mixing ADO and ODBC

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

myQueryTable.Connection = "ODBC;DRIVER=SQL Server;SERVER=MyDatabase;"

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:

myQueryTable.CommandText = ThisWorkbook.Names("SQL").RefersToRange.Value

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.