CODEWITHSUNDEEP
sqlms-access
Mario Cordeiro
Mario Cordeiro

Reputation: 125

MS Access SELECT query, cannot retrieve values

I'm using VBA for applications and trying to get some rows from a MS Access table.

I have this code:

KKS = "11LAB10"
id& = SQLOpen("DSN=MJC_Experiencia;DBQ=c:\equipamentos.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;",,3)

query_SQL = "SELECT * FROM my_table WHERE kks=" & Chr(39) & KKS & Chr(39)
msgbox query_SQL
qry& = SQLExecQuery(id&,querySQL)
i% = SQLBind(id&, b, 3)
i% = SQLBind(id&, b, 1)
i% = SQLBind(id&, b, 2)
i% = SQLBind(id&, b, 4)
l& = SQLRetrieve(id&, c)

msgbox "KKS = " & c(0,0)

The first msgbox is displaying this:

SELECT * FROM my_table WHERE kks="'11LAB10'"

But I get an error

9 - Subscript out of range

(referring to c(0,0))

If I substitute the query_SQL with this:

query_SQL = "SELECT * FROM my_table WHERE kks='11LAB10'"

Then the code works, and I get the correct value, in the second msgbox.

What am I doing wrong with the variable KKS in the first query?

Upvotes: -2

Views: 63

Answers (2)

Mario Cordeiro
Mario Cordeiro

Reputation: 125

Today I finally realize that the KKS variable include the quotation marks itself, that´s why the query_sql don't work. What I do is to get rid of the quotation marks with this:

KKS = Mid(KKS,2, Len(KKS) -2)

Now it's working, thanks all for your help.

Upvotes: 1

Hogan
Hogan

Reputation: 70538

to change this:

 SELECT * FROM my_table WHERE kks="'11LAB10'"

to this

 SELECT * FROM my_table WHERE kks='11LAB10'

use the following visualbasic

 query_SQL = "SELECT * FROM my_table WHERE kks='" & KKS & "'"

Upvotes: 1

Related Questions