Inner Join Ms.Access in vb6

[Octo Siburian]

Hi there,
i have a project vb6. i want using inner join to join 4 tables with query select. but it's error "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect..

This is my script

QuerySelect = "SELECT a.DN, b.DIN, c.PIN, c.UserName, " _
             & "b.Clock, d.ItemName, " _
             & "FROM ras_Device a INNER JOIN ras_AttRecord b ON a.DN = b.DN " _
             & "LEFT JOIN ras_Users c ON b.DIN = c.DIN " _
             & "LEFT JOIN ras_AttTypeItem d ON d.ItemId = b.AttTypeId " _
             & "WHERE b.DIN = c.DIN"
rsSQL.Open QuerySelect, adoRAS

Please help me.
Thanx before.


Regards,

Octo

database use ms.access 2003

[MikeTheBike]

Hi

First thing to do is remove the comma between d.ItemName and the FROM. I think it is expecting FROM to be a field name, which is, of course, a reserved word!?


MTB

[Octo Siburian]

Thanx Mr.Mike,

I have fixed the script, but is still error,
"Syntax error(missing operator) in query expression 'a.DN=b.DN LEFT JOIN ras_Users c ON b.DIN=c.DIN LEFT JOIN ras_AttTypeItem d ON b.AttTypeId=d.ItemId' :(

[MikeTheBike]

Hi

It is a bit difficult to have any degree of confidence in diagnosing the problem without any knoledge of the database structure or what you are trying to achieve, but I suggest some bracketing might be usfeul !

QuerySelect = "SELECT a.DN, b.DIN, c.PIN, c.UserName, " _
        & "b.Clock, d.ItemName " _
        & "FROM ((ras_Device a INNER JOIN ras_AttRecord b ON a.DN = b.DN) " _
        & "LEFT JOIN ras_Users c ON b.DIN = c.DIN) " _
        & "LEFT JOIN ras_AttTypeItem d ON d.ItemId = b.AttTypeId " _
        & "WHERE b.DIN = c.DIN"

??

MTB

[Octo Siburian]

Thanx so much Mr.Mike, it work, the desired data has emerged,
now, i have a new question , from the previous SELECT query I want to enter back into the database with INSERT query, but every time I run the application, the old data is still in, I just want the new data are entered

QuerySelect = "SELECT a.DN, b.DIN, c.PIN, c.UserName, " & _
"b.Clock, d.ItemName " & _
"FROM ((ras_Device a INNER JOIN ras_AttRecord b ON a.DN=b.DN) " & _
"LEFT JOIN ras_Users c ON b.DIN=c.DIN) " & _
"LEFT JOIN ras_AttTypeItem d ON b.AttTypeId=d.ItemId " & _
"WHERE b.DIN=c.DIN"
rsSQL.Open QuerySelect, adoRAS

'Input pada dbSIKawan
Do While Not rsSQL.EOF
QueryInsertSIKawan = "INSERT INTO fingerprint_" _
& "(device, finger_ID, nik_kary, nama_kary, time, type)"
QueryInsertSIKawan = QueryInsertSIKawan & " VALUES('" & rsSQL(0) & "', '" & rsSQL(1) & "', " _
& "'" & rsSQL(2) & "', '" & rsSQL(3) & "', '" & rsSQL(4) & "', '" & rsSQL(5) & "')"
adoSIKawan.Execute QueryInsertSIKawan
Loop


any suggestion sir?

Regards,

octo

[MikeTheBike]

Hi

Do you get any error messages when the code executes?

What data types are the device, finger_ID, nik_kary, nama_kary, time and type fields?

If any of them are not Text data types then the sytax is incorrect. Number datatypes do not require apostrophy delimiters and date/time fields need # delimiters .

MTB

[Octo Siburian]

No sir, but when running the data is always repeating itself, but a similar data entry of repetitive,

ms.access
device = number
finger_ID = number
nik_kary = text
nama_kary = text
time = date / time
type = text

sql server
device = int
finger_ID = int
nik_kary = varchar
nama_kary = varchar
time = datetime
type = varchar


Regards,

Octo