Access query question

[MW4]

I'm using the following code and it's erroring out on the rs = db.OpenRecordSet(sqlstr).

ERROR: 1261: COM/CLR Exception

Any ideas??


CODE----------------

dbname = "\\fleetdc01\Fleet\Data\BED\Registrations.mdb"
Access = ObjectOpen("Access.Application")
Access.OpenCurrentDatabase(dbname)
Access.Visible = @TRUE
db = Access.CurrentDb

sqlstr = "SELECT tblVins.Vin, tblVins.Account, tblVins.Make, tblVins.Location FROM tblVins WHERE tblVin ='12345678901234567'"
rs = db.OpenRecordSet(sqlstr)

While !rs.eof
   Message("Debug", StrCat("Vin: ", rs.fields("Vin").value, @CRLF, "Account: ", rs.fields("Account").value, @CRLF, "Make: ", rs.fields("Make").value))
   rs.movenext
EndWhile
   
rs.close

[td]

If you have a newer version of WinBatch and the 'More Error Info' button is enabled on the error message, click it to get more information.  Other than that, a general guess would be that your SQL statement is a bit off in some way.

[stanl]

try changing

sqlstr = "SELECT tblVins.Vin, tblVins.Account, tblVins.Make, tblVins.Location FROM tblVins WHERE tblVin ='12345678901234567'"

to

sqlstr = 'SELECT tblVins.Vin, tblVins.Account, tblVins.Make, tblVins.Location FROM tblVins WHERE tblVin ="12345678901234567"'


Access likes char vars surrounded in "  whereas SQL Server likes '

may not be the answer but I remember it causing me issues in the pasr, 

[MW4]

I got that ironed out...what about using a variable?

tried this and it doesn't work

strVinNum=    '2C4RC1BG1GR307997'
sqlstr = "SELECT [Vin], [Account], [AcctInfo], [make] FROM [tblRegistrations] WHERE [Vin] = [': strVinNum :']"

[MW4]

got it:

strVinNum=    '2C4RC1BG1GR307997'
sqlstr = "SELECT [Vin], [Account], [AcctInfo], [make] FROM [tblRegistrations] WHERE [Vin] = '%strVinNum%'"

[td]

Or you can use the the concatenation operator:

Code (winbatch) Select Expand

strVinNum=    '2C4RC1BG1GR307997'
sqlstr = "SELECT [Vin], [Account], [AcctInfo], [make] FROM [tblRegistrations] WHERE [Vin] = '":strVinNum:"'"