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?

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?

• sql
• ms-access

• Try this query_SQL = "SELECT * FROM my_table WHERE kks = '" & Replace(KKS, "'", "''") & "'" and see if it works, I can add an answer if so. – Je Campos Commented Feb 15 at 23:10

2 Answers 2

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 & "'"

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.