Hi,
I have a bit of vba which assigns a tempvar and then opens a recordset based on a SELECT query "qry_InsertLetter_C1" which is using that tempvar in the WHERE clause. The query uses linked tables to a SQL Azure database.
If I run this, I get error 3146 ODBC Call Failed. Very odd as if I manually open the query after setting the tempvar, it opens fine and displays the correct row of data.
However, if I replace the tempvar in the query with a parameter, and then assign the value of the parameter to the tempvar in vba, it works:
Can anyone give me any pointers as to what is wrong with the first solution? I use variants of this code in lots of places in my application, so it would save me making lots of changes,
Thanks for reading and for any help!
Jim
I have a bit of vba which assigns a tempvar and then opens a recordset based on a SELECT query "qry_InsertLetter_C1" which is using that tempvar in the WHERE clause. The query uses linked tables to a SQL Azure database.
Code:
TempVars.Add "myRPID", 35
Set rsIn = db.OpenRecordset("qry_InsertLetter_C1", dbOpenSnapshot, dbSeeChanges)
If I run this, I get error 3146 ODBC Call Failed. Very odd as if I manually open the query after setting the tempvar, it opens fine and displays the correct row of data.
However, if I replace the tempvar in the query with a parameter, and then assign the value of the parameter to the tempvar in vba, it works:
Code:
Set qdf = db.QueryDefs("qry_InsertLetter_C1")
qdf.Parameters("MyRPID").Value = [TempVars]![myRPID]
Set rsIn = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
Can anyone give me any pointers as to what is wrong with the first solution? I use variants of this code in lots of places in my application, so it would save me making lots of changes,
Thanks for reading and for any help!
Jim