I am banging my head against a brick wall here with a simple query I am creating in VBA that will not work if I include the LIKE clause.
Even the Dumbed down example below won't work.
Dim rst_MaxVer As New ADODB.Recordset
strSQL = "SELECT tblHSSEIcdAttachs.* FROM tblHSSEIcdAttachs" & Chr(10) & _
" WHERE (tblHSSEIcdAttachs.File LIKE " & Chr(34) & "B*" & Chr(34) & ")" & Chr(10) & _
" ORDER BY File DESC;"
rst_MaxVer.ActiveConnection = CurrentProject.Connection
rst_MaxVer.Open strSQL, , adOpenKeyset, adLockOptimistic
bolNewVersion = False
If Not rst_MaxVer.EOF Then ' this filename is already on record, record old file, extract MaxVer and set new one
bolNewVersion = True
End If
rst_MaxVer.Close
Set rst_MaxVer = Nothing
The target table is a linked access database table.
The final query is more complex using variables from the code in this example. It's just that I have tied it down to the Like statement being the problem.
I have compacted the database, the table source database and also made the File column indexed.
Any suggestions would be much appreciated.