mixing ADO and ODBC

**dlh** · 12-30-2009, 06:33 PM

I've got an Excel document I've built which uses multiple ODBC QueryTables

Please Login or Register  to view this content.

which were built using Excel's Connection Wizard. I periodically tweak the QueryTables by editing an SQL-string and then replacing the QueryTable's CommandText:

Please Login or Register  to view this content.

In the same Excel document I'm also using several ad hoc ADO queries against the same database, the results of which don't get embedded in a sheet but are used on-the-fly by my code.

My problem is I'd really like the user to only have to log into the database *once* the first time the database is needed (when one of the QueryTables is refreshed or when one of the ad hoc queries is encountered). For single login access to all the ADO stuff I'm just storing the Connection object in a global variable, but I don't know how to integrate that with the QueryTables.

I see that QueryTables can be created from ADO Recordsets, but then, from what I can tell, I won't be able to tweak the SQL and refresh.

I'm going to try to learn about Windows Authentication, but I don't have control of the database permissions, so that may not work.

I'd really appreciate any advice.

**romperstomper** · 12-31-2009, 04:35 AM

You can overwrite the recordset used for a querytable, so you would need to change the SQL, retrieve a new recordset using your stored connection, then assign it to the querytable and refresh.
If you need your users to be able to update the querytables manually too, then I think you will need to look at Integrated Security.

**dlh** · 12-31-2009, 06:08 PM

Thank you again, romperstomper. I hadn't realized I could assign a Recordset to an existing QueryTable. This seems promising, but can you please be more specific about the inability to update the table manually? Exactly what functionality goes away?

If I use a dynamic cursor in the Recordset, will the Refresh method allow the QueryTable to respond to changes in the database? And does the CursorLocation matter?

For tweaking the QueryTable's SQL (which won't happen very often), I think I can tell the user to update the SQL command and then have them run a special refresh macro (which would create a new Recordset, assign it to the QueryTable, and then refresh the QueryTable).

(I'm going to test all of this as soon as I get access to the database again.)

**dlh** · 01-05-2010, 03:48 AM

Marking the thread solved.

If anyone else has a similar question, after you assign an existing Recordset to a QueryTable's Recordset property, the Excel UI's Refresh buttons (both right-click menu from within the QueryTable's range and the menu/ribbon item) become greyed out. This was an acceptable compromise for me.

I don't know if subsequent calls to the VBA QueryTable.Refresh method will respond to updates in the underlying database, but I suspect it will not.