Print Page - Access query question

Title: Access query question
Post by: MW4 on July 21, 2016, 04:55:02 PM

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

Title: Re: Access query question
Post by: td on July 21, 2016, 07:41:15 PM

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.

Title: Re: Access query question
Post by: stanl on July 22, 2016, 05:17:44 AM

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,

Title: Re: Access query question
Post by: MW4 on July 25, 2016, 01:06:36 PM

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 :']"

Title: Re: Access query question
Post by: MW4 on July 25, 2016, 01:09:35 PM

got it:

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

Title: Re: Access query question
Post by: td on July 26, 2016, 08:30:00 AM

Or you can use the the concatenation operator:

Code (winbatch) Select

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

WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: MW4 on July 21, 2016, 04:55:02 PM