0

I have a problem with VBA Access because I don't know the syntax. I have function which shall read fields from a table "IC4_MMI_405" correctly imported on ACCESS from an excel file:

Dim conn As New ADODB.Connection
Dim record As New ADODB.Recordset

Set conn = CurrentProject.Connection
Set record = New ADODB.Recordset

record.Open "SELECT * WHERE file = '" & sFile & "' And ccu_code = " & iCode, conn, adOpenKeyset, adLockOptimistic

with this code everything works fine. If I change the code in this way:

Dim conn As New ADODB.Connection
Dim record As New ADODB.Recordset
Dim sSQL As String

Set conn = CurrentProject.Connection
Set record = New ADODB.Recordset
sSQL = "SELECT * FROM IC4_MMI_410 WHERE file = "
sSQL = sSQL & "" & sFile & ""
sSQL = sSQL & " And ccu_code = " & iCode

record.Open sSQL, conn, adOpenKeyset, adLockOptimistic

I will receive the run time error. sFile is as String and iCode is an Integer. When I run in debug mode I can see the values of sFile and iCode are the expected ones. I think the problem lays on the way I wrote sSQL. Can you help me please?

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Francesco
  • 3
  • 1
  • 5
  • 1
    In your initial code, `sFile` is between single quotes `'`, but not in your later code. Maybe try `sSQL = sSQL & "'" & sFile & "'"`. – Vincent G Oct 02 '17 at 07:41
  • Thank you! You solved the problem :) – Francesco Oct 02 '17 at 07:53
  • Using `""` (escaped) double, double quote is not a bad idea but has to be made the right way (`Debug.Print`the result). It's easier to use Chr(34) as replacement. This allows string criterias with single quote including. If you use single qutoes as string literal and want single quotes in criteria you have to escape them by doubling `Replace(sFile,"'","''")` But best practice is using [parameters.](https://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters). – BitAccesser Oct 02 '17 at 08:18
  • Another consideration, if you are googling like me about this error - what it means I think is that either the SQL is invalid (bad syntax) or the columns do not exist in the database. For me I was using the wrong version of the database, without that column, that generated the error. – Rob Oct 26 '18 at 00:16

1 Answers1

0

This should work:

Dim conn As New ADODB.Connection
Dim record As New ADODB.Recordset
Dim sSQL As String

Set conn = CurrentProject.Connection
Set record = New ADODB.Recordset
sSQL = "SELECT * FROM IC4_MMI_410 WHERE file = "
sSQL = sSQL & "'" & sFile & "'"
sSQL = sSQL & " And ccu_code = " & iCode

record.Open sSQL, conn, adOpenKeyset, adLockOptimistic
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117