VBA Recordset based on query with Tempvars

[BiigJiim]

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.

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:

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

 

[Gasman]

I *think* you have fallen into the issue with parameter queries and recordsets?
I get parameters expected 1 on this code

Sub TestQueryTempVar()
Dim rst As DAO.Recordset, db As Database
TempVars("MonthNum") = 6
Set db = CurrentDb()
Set rst = db.OpenRecordset("Query1")
Do While Not rst.EOF
    Debug.Print rst.Fields(0)
Loop

Set rst = Nothing
Set db = Nothing

End Sub

theDBGuy has a link on here to Eval the parameters.
Have a search

Edit: This is the link, which pretty much does the same thing you tried later, but more generic.

Code Snippets

 

[cheekybuddha]

When using DAO recordsets the query is not passed through Access' 'expression service', and all parameters must be resolved by you in VBA.

Do a search for 'Leigh's generic recordset' for a wrapper function to CurrentDb.OpenRecordset that will handle all this for you.

Or, don't use parameters in your stored queries; just build the literal SQL in VBA and pass to the OpenRecordset method.

 

[BiigJiim]

Thanks for your replies. Does that mean that basically you cannot open a recordset in vba with a query that references a tempvar? (I am sure I have done this lots of times in the past, but maybe I am mistaken.)

 

[theDBguy]

Thanks for your replies. Does that mean that basically you cannot open a recordset in vba with a query that references a tempvar? (I am sure I have done this lots of times in the past, but maybe I am mistaken.)

Hi. Pardon me for jumping in, but you absolutely can open a recordset in vba with a query that references a TempVar. Have you tried using the link @Gasman provided in Post #2?

All you have to do is replace this line:

Set rsIn = db.OpenRecordset("qry_InsertLetter_C1", dbOpenSnapshot, dbSeeChanges)

with this one:

Set rsIn = fDAOGenericRst("qry_InsertLetter_C1", dbOpenSnapshot, dbSeeChanges)

Hope that helps...

 

[cheekybuddha]

@dbg, who hosts your site on accessmvp.com?

Try and get them to get a SSL cert for the site - I always get blocked initially by HTTPSEverywhere, and perhaps others are not risking going to the site if they receive such a warning.

 

[theDBguy]

@dbg, who hosts your site on accessmvp.com?

Try and get them to get a SSL cert for the site - I always get blocked initially by HTTPSEverywhere, and perhaps others are not risking going to the site if they receive such a warning.

Hi David. That's a good idea. I'll ask.