manage multiple data connections in a workbook

I recorded a macro and got this:
Sub Datasource()
'
' Datasource Macro
'
'
    Workbooks("filename.xlsm").Connections.AddFromFile _
        "C:\Users\username\Documents\folder1\folder2\databaseName.accdb"
End Sub

I selected a query within the database but the macro didn't appear to list it?

I looked at your code, and it seems that you are using the same query. I need to loop through a different query everytime (around 37 different queries), and I would like it to be on a different worksheet (if not too involved).

Using a different query and worksheet for each instance of the loop shouldn't be TOO much of a problem. It helps, of course, if there's some rhyme or reason to the sequence (i.e. a pattern).

I confess I don't know too much about how Excel uses Access and its queries, but I'm sure one of the REAL experts (I'm obviously a novice just trying to help) could help you out further.

Good luck and sorry I couldn't help more!

Thank-you for the help!...the naming convention for the queries in the database are in a sequence:
QRY_1
QRY_2
QRY_3
........etc

OK, better luck with recording the macro this time:

Sub datasource()
'
' datasource Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\username\Documents\folder1\folder2\databaseName.accd" _
        , _
        "b;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Passwo" _
        , _
        "rd="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transa" _
        , _
        "ctions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Do" _
        , _
        "n't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Da" _
        , "ta=False"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("QRY_1")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "C:\Users\username\Documents\folder1\folder2\databaseName.accdb"
        .ListObject.DisplayName = "Table_databaseName.accdb_1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Hi Ken, using your code and modifying it to do what I need works great if I put each query on the same worksheet. I'm not sure how to go about puting each query on it's own worksheet. Not really married to doing it that way, but it would make it easier to view the data.  If anyone has any suggestions on how to do it, that would be great!

In order to put each query on a different worksheet, you simply edit the Destination parameter.

You'll probably want to have your main loop go through all the worksheets. It would look something like this:
 

Dim ws As Worksheet
Dim destination As Range
For Each ws In ActiveWorkbook.Worksheets.Count
    destination = ws.Range("A1")
Next ws

Select allOpen in new window

Where ws is the current worksheet being handled.

Following this line of thought, replacing Destination:=Range("$A$1") in your recorded macro (I'm assuming you have a more advanced working version in your hands, so I'm just referring to the recorded one pasted above) with Destination:=ws.Range("A1") should do the trick.

My syntax may be off, but that's the basic idea :)

Hope I can be of some more help this time! Post back if you have any problems, someone besides me will eventually get to this I'm sure.

On second look, I must correct myself.

Line 3 on the code should be "For Each ws in Activeworkbook.Worksheets" without the .Count at the end.

Since you are looping through a collection with For Each you don't use .Count (if you were to use something like For i = 1 To Activeworkbook.Worksheets.Count, that would work, but you'd have to reference the worksheets inside the loop with Activeworkbook.Sheets(i) instead of just as ws)

Hi DL,
Thanks for the help...I'm not sure how to make this work though.  Because for the command  text, the query would be different for each worksheet. Each worksheet connects to a different query in my database. The command text would not need to change because the same table names and query names are in each database. The location for the database is the only change. I tried commenting out the
    '               qy.CommandText = _
    '              StringToArray(Application.Substitute(qy.CommandText, _
    '              OldPath, NewPath))
But, when I run it nothing happens?

Do not comment those out.  the loop is going through EACH query that exists, and changing ONLY the path of the old database to the new database.

     For Each ws In ActiveWorkbook.Sheets
       For Each qy In ws.QueryTables
            qy.Connection = _
                  Application.Substitute(qy.Connection, _
                  OldPath, NewPath) 'only changing the path of the database
            qy.CommandText = _
                  StringToArray(Application.Substitute(qy.CommandText, _
                  OldPath, NewPath)) 'aligning commandtext with the path change
            qy.Refresh
       Next qy



Dave

I can't figure out what kind of "hair brain" thing I'm doing wrong then. I copied your code as is, and put in a value for  OldPath = "some path"  &     NewPath = "some path"
and I run it, and absolutely nothing happens....no compile error or no indication that it is computing anything.

Make sure your old path and new paths are fully qualified...

C:\Users\username\Documents\folder1\folder2\ <- old path

C:\Users\Username\Documents\Folder5 <- theroetical new path

Also ensure both paths exists, that the access DB having the exact same name is in both folders.

If you continue to have problems, I'll try to setup an example with a DB and zip it up for you to see.

Dave

Yes, both paths are fully qualified and exist, and databases have exact same names

Hi Ken,
Can you clarify exactly what the loop should look like with a sample?
Thanks!

Be sure to update OldPath and NewPath before running the code.

Please let me know if that helped!

Dave

Confussed about this:

-----NewPath = ActiveWorkbook.Path & "\Folder1"

what is & "\Folder1" ?

Just change OldPath and NewPath to the proper paths

OldPath = your old path
NewPath = your new path

my code - Activeworkbook.Path & "\Folder1" was for my testing.

Dave

How will it know which query? Each sheet should access a different query in the database

Its the database location, not the query that we're changing, correct?  Give it a shot.

Dave

I just tested this with access database.  I started with the DB in one place and tested my query, then I moved the database to another folder, ran the routine I posted, and then refreshed my query.  It worked.

>>six months, the database will change it's location. It will be the same querries on the same tables with the same names, just a different location for the database
This is what you want to do, right?  Respecify the folder where the database resides?

Dave

Thank-you very much! These are great solutions.