VBA to manipulate the Source file name in an OLEDB QueryTable connection string

Jerry Paladino
Jerry Paladino used Ask the Experts™
on
I have a number of QueryTables in an Excel file.  Most sheets contain one QueryTable and a few have several.  I would like to be able to change where the Source=C:\test\myDb.accdb points to in a VBA module for each QueryTable before it is updated with
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
So I can easily switch between a Test database and a Production database.

An Example on worksheet   DETAIL   is:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\test\myDb.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False

where
Command Type = Table
Command Text = ConsolidatedView

If there is more than one QueryTable on a worksheet how do you distinguish one from the other in the VBA.

Thanks,
Jerry
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Query table objects are referenced by referencing the containing worksheet's QueryTables collection as illustrated below.

   Dim TargetQueryTable As QueryTable
   Set TargetQueryTable = Sheets("Sheet1").QueryTables("Query 1")

To add a new query table:

   Set TargetQueryTable = Sheets("Sheet1").QueryTables.Add...

Note that the Add query table method is very complex. The best way to write VBA code to add a query table is to turn the macro recorder on, create the query table manually, and review the generated code.

To rename a query table:

   TargetQueryTable.Name = "Query 1"

To delete a query table:

   Sheets("Sheet1").QueryTables("Query 1").Delete

Note that the data pulled is not cleared when deleting the query table, nor is the range name deleted. To completely purge a query table:

   Sheet1.QueryTables("Query 1").Delete
   [Query_1].Clear
   Sheet1.Names("Query_1").Delete

Note that the query table range name contains underscores in place of spaces in the query table name.

Kevin
Most Valuable Expert 2011
Top Expert 2011
Commented:
In 2007 you iterate throught the worksheets, then through the listobjects on each sheet and check the connection of its querytable. You should also be able to loop the workbooks WorkbookConnections collection I think.
Trying to iterate the querytables of the worksheet does not usually work in 2007 unlike prior versions

Commented:
try range("A1").listobject.querytable.connection = "your connection string"

  Assuming A1 is part of your querytable
Kevin / Rory,

Thanks for your help.  Allways appreciate it!

Jerry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial