Open DAO recordset without using a recordset object

[Schroeder]

In order to use a certain password-protected ODBC connection, I first run a Pass-Through query against one of the tables. Once the Pass-Through query is run I can use linked tables in my Access db without concerning myself with the password protection.

I've set up the Pass-Through query (we'll call it ConnectionQ) in my Access db and call it like this:

dbDBase.OpenRecordset "ConnectionQ"

Where does that recordset go? Do I need to worry about a recordset object that might be floating around in my stack space? or does it get created and destroyed all in one operation? It is a SELECT query as I'm unable to run any action queries against the ODBC db.

 

[Golom]

This seems a bit strange because password information is usually at the database connection level ... not at the recordset level ... however

The "OpenRecordset" method returns a recordset object and I'm not sure what happens when you don't give it a target. You could be explicit about things with
[blue][tt]
Dim rs As DAO.Recordset
Set rs = dbDBase.OpenRecordset ("ConnectionQ")
Set rs = Nothing
[/tt][/blue]
which explicitly creates and destroys the recordset.

 

[Schroeder]

Data connection techniques have always been very enigmatic to me. Given that, my perception of what's happening is: the protection is at the db connection level. Once I make one good connection, I'm not hassled again until I close Access (or close my db object that's connected to Access).

I don't connect directly to the data source - couldn't with DAO even if I wanted to. I use tables linked into Access. If I don't run my pass-through query, the first time my program uses one of those linked tables, a password prompt pops up. The pass-through query allows me to send the password info "automatically" avoiding the non-user-friendly prompt.

I could declare a Recordset object, instantiate it and destroy it like you said.. I just thought it was pretty cool to get the whole thing done in one line.