We help IT Professionals succeed at work.

manage multiple data connections in a workbook

ktjamms2
ktjamms2 used Ask the Experts™
on
I created a workbook with several tabs that connect to an Access database with several different queries. Each tab is connected to a different query within the same database. I would like to be able to use this workbook as a template, but every 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 (every six months the old database is put to archive and a copy is made in a new folder for going forward). I want to reuse my workbook but would like it if I didn't have to go through 30+ tabs and change the datasources. Does anyone have any suggestions how to easily change/manage the data source connections?
Thanks!
KT
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Since you are only changing one thing (datasource), I'm sure you can setup a loop to go through all the tabs and refresh the queries for you.

I've done something similar with my current project. I have 6 different queries all going to the same database (through MySQL), but the table I grab the data from can change. I've made a macro where I store the tablename in a variable, which I change whenever I want to switch tables, then rerun the macro.

You should be able to accomplish a similar result. In order to get the original macro to modify, you need to first record a macro, then go through the steps of setting up a connection manually. The macro will record all the necessary things. Once you've finished setting up the connection, stop the macro, then go into the code and make proper adjustments.

I'll attach my example just so you can get an idea. I'm sure my code is not as pretty as it could be, but it gets the job done and I'm only showing you to give you a starting point on what you might need to do.
Sub LoadDataFromMySQL()
    
    Application.ScreenUpdating = False
    ActiveSheet.Range("A:XFD").ClearContents

    Dim columnLetter As String
    Dim tblName As String
    
    For i = 1 To 6
    
    If i = 1 Then
        columnLetter = "A1"
    ElseIf i = 2 Then
        columnLetter = "E1"
    ElseIf i = 3 Then
        columnLetter = "I1"
    ElseIf i = 4 Then
        columnLetter = "M1"
    ElseIf i = 5 Then
        columnLetter = "Q1"
    ElseIf i = 6 Then
        columnLetter = "U1"
        
    End If

    tblName = "tblTestData"
    ' tblMain
    ' tblStressTest
    ' tblTestData
        
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=Diabetes Data;", Destination:=Range(columnLetter)).QueryTable
        .CommandText = Array( _
        "SELECT " & tblName & "_0.datetime_as_string, " & tblName & "_0.value, " & tblName & "_0.eventID" & Chr(13) & "" & Chr(10) & "FROM bloodsugarnumbers." & tblName & " " & tblName & "_0" & Chr(13) & "" & Chr(10) & "WHERE (" & tblName & "_0.eventID=" & i & ")" & Chr(13) & "" & Chr(10) & "ORDER BY " & tblName & "_0.datetime_as_string" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Diabetes_Data_" & i
        .Refresh BackgroundQuery:=False
    End With
    
    Next i
    Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
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!

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Open 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)
Most Valuable Expert 2012
Top Expert 2012
Commented:
Some good tips going on here...  However, I believe there's a more standard approach you can utilize.

I recently had to do this with links and found a helper tip from MSDN that worked quite well that I have also used in the past.  Here's one I reviewed and tested which is focused on query and pivottable connections: http://support.microsoft.com/kb/816562 which helped quite a bit - note the help extends to changing connections.

The code you're looking for is:

 
Sub QueryChange()
     Dim sh As Worksheet, qy As QueryTable
     Dim pt As PivotTable, pc As PivotCache
     Dim OldPath As String, NewPath As String	
     Dim rng As Range
     
     'Replace the following paths with the original path or server name
     'where your database resided, and the new path or server name where
     'your database now resides.
     OldPath =  "C:\OldPath\Folder"
     NewPath = "C:\NewPath\Folder"

     For Each ws In ActiveWorkbook.Sheets
       For Each qy In ws.QueryTables
            qy.Connection = _
                  Application.Substitute(qy.Connection, _
                  OldPath, NewPath)
            qy.CommandText = _
                  StringToArray(Application.Substitute(qy.CommandText, _
                  OldPath, NewPath))
            qy.Refresh
       Next qy

       For Each pt In ws.PivotTables
            pt.PivotCache.Connection = _
                  Application.Substitute(pt.PivotCache.Connection, _
                  OldPath, NewPath)
            On Error Resume Next
            pt.PivotCache.CommandText = _
                  StringToArray(Application.Substitute(pt.PivotCache.CommandText, _
                  OldPath, NewPath))
            If Err.Number <> 0 Then
                Err.Clear
                On Error GoTo 0
                Application.ScreenUpdating = False
                Set rng = pt.TableRange2
                pt.TableRange2.Copy Workbooks.Add(xlWorksheet).Worksheets(1) _
                           .Range("A1")
                ActiveCell.PivotTable.PivotCache.CommandText = _
                           StringToArray(Application.Substitute(pt.PivotCache.CommandText, _
                           OldPath, NewPath))
                ActiveCell.PivotTable.TableRange2.Copy pt.TableRange2
                ActiveWorkbook.Close False
                Set pt = rng.PivotTable
                Application.ScreenUpdating = True
            End If
            pt.PivotCache.Refresh
        Next pt
Next ws
End Sub

Function StringToArray(Query As String) As Variant

   Const StrLen = 127
   Dim NumElems As Integer
   Dim Temp() As String

   NumElems = (Len(Query) / StrLen) + 1
   ReDim Temp(1 To NumElems) As String

   For i = 1 To NumElems
      Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
   Next i

   StringToArray = Temp

End Function

Open in new window


The code could be easily modified to be called and passed the old and new path's for your access database

Author

Commented:
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?
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

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

Author

Commented:
Hi Ken,
Can you clarify exactly what the loop should look like with a sample?
Thanks!
Most Valuable Expert 2012
Top Expert 2012
Commented:
I played with the tip I suggested for a bit and see the quandry.  The querytable is not quite what we need.  However, I was able to use the same principle, to search through all connections and modify the OLEDB Connections from old to new.

Please try this:
 
Sub ConnectionChange()
     Dim sh As Worksheet
     Dim OldPath As String, NewPath As String
    Dim myConn As Variant
     
     'Replace the following paths with the original path or server name
     'where your database resided, and the new path or server name where
     'your database now resides.
     OldPath = ActiveWorkbook.Path
     NewPath = ActiveWorkbook.Path & "\Folder1"
    
    For Each myConn In ActiveWorkbook.Connections
        myConn.OLEDBConnection.Connection = Application.Substitute(myConn.OLEDBConnection.Connection, OldPath, NewPath)
    Next myConn
End Sub

Open in new window


Dave
Most Valuable Expert 2012
Top Expert 2012

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

Please let me know if that helped!

Dave

Author

Commented:
Confussed about this:

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

what is & "\Folder1" ?
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
How will it know which query? Each sheet should access a different query in the database
Most Valuable Expert 2012
Top Expert 2012

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

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
Thank-you very much! These are great solutions.