Error while opening recordset (1 Viewer)

[frispee]

Hi,

I get the following error when I open a record set:
Run-time error 3075 Syntax error in query expression 'Equiptment = 40' Standard Dry"

I am guessing that the error is because of the symbol ' in the string. But here 40' stands for 40 feet and has nothing to do with SQL. How do I make Access understand that? Any help would be greatly appreciated. The code is:

If Me.Trailer_opt.Value = 2 Then
ContSize = "40' Standard Dry"
Set Route = CurrentDb.OpenRecordset("SELECT * FROM RouteGuide_tbl WHERE [Origin Country] = '" & ContCode & "' AND [Destination City] = '" & Dest & "' AND [Equiptment] = '" & ContSize & "'")

 

[pbaldy]

This should, as it deals with that very issue:

http://www.baldyweb.com/BuildSQL.htm

 

[mdlueck]

I am guessing that the error is because of the symbol ' in the string. But here 40' stands for 40 feet and has nothing to do with SQL.

Likely guess. I had to do with the quote character being a paired character. I suspect you will need to find out how to escape that character in the search string. Perhaps you need to add a leading \ character. (Ahh yes pbaldy, or use the VBA Chr() function to inject the correct character. That would be another way to solve the problem.)

Alternately, I suspect you could switch the query to use an ADO.Command object with ADO.Parameters objects which then you would totally avoid the fight with quote characters as the value would be inside an ADO.Paramters object.

I do not spot right off a bookmarked forum post of a sample ADO.Command doing a SELECT type query, so I will paste sample code in here, and save this post for future reference:

'This API searches for the a matching record based on the PartNumber
Public Function LocateByPartNumber() As Boolean
On Error GoTo Err_LocateByPartNumber

  Dim adoCMD As Object
  Dim adoRS As Object
  Dim strSQL As String

  'Define a query to look for the KeyValue based on the KeyName and Run-Time Environment
  strSQL = "SELECT [piw].[aid],[piw].[title],[piw].[qtyper],[oldqtyper],[piw].[addpartrecordflg],[piw].[doneflg]" & vbCrLf & _
           "FROM [" & Me.FETempTableName & "] AS [piw]" & vbCrLf & _
           "WHERE [B][COLOR=Red][piw].[partnumber] = p1[/COLOR][/B];"

  'Define attachment to database table specifics
  Set adoCMD = CreateObject("ADODB.Command")
  With adoCMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    [B][COLOR=Red].Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)[/COLOR][/B]
    .CommandText = strSQL
    Set adoRS = .Execute()
  End With

  With adoRS
    'Was no record found?
    If .BOF Or .EOF Then
      Me.Clear
      LocateByPartNumber = False
    Else
      'Fetch the value found
      Me.aid = Nz(adoRS.Fields.Item("aid"), 0)
      Me.title = Nz(adoRS.Fields.Item("title"), vbNullString)
      Me.qtyper = Nz(adoRS.Fields.Item("qtyper"), 0)
      Me.oldqtyper = Nz(adoRS.Fields.Item("oldqtyper"), 0)
      Me.addpartrecordflg = Nz(adoRS.Fields.Item("addpartrecordflg"), False)
      Me.doneflg = Nz(adoRS.Fields.Item("doneflg"), False)
      LocateByPartNumber = True
    End If

    'Close the database table
    .Close
  End With

Exit_LocateByPartNumber:
  'Clean up the connection to the database
  Set adoRS = Nothing
   Set adoCMD = Nothing

  Exit Function

Err_LocateByPartNumber:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: LocateByPartNumber()")
  LocateByPartNumber = False
  Resume Exit_LocateByPartNumber

End Function

You should define the datatype of the Paraemters object to match the type of column. You may find a helpful reference bridging ADO datatypes to a variety of different databases (including Access) here. This page has been extremely helpful as I work with a combination of SQL Server and Access, so this page helps keep my head unscrambled.

"Data Type Mapping"
http://www.carlprothman.net/Default.aspx?tabid=97

Update Note: I have updated this post to use Late-Binding syntax to create the ADO object(s). You will also need to use this post to obtain all of the constants ADO utilizes in order to have complete success with Late-Binding.

ADO Constants for use with Late Binding ActiveX Data Objects 2.8 Library
http://www.access-programmers.co.uk/forums/showthread.php?t=243088

 

[frispee]

@ Paul and Michael: Thank you for replying.
@ Paul: I have used the solution you have described in the site and it works perfectly
@ Michael: I found your reply very insightful and I am sure the table on data type mapping is going to prove useful in my current project, especially since I am just at an amateur level now

 

[pbaldy]

Happy to help!