Avatar of ktjamms2
ktjamms2
Flag for United States of America asked on

manage multiple data connections in a workbook

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
Microsoft ApplicationsMicrosoft Excel

Avatar of undefined
Last Comment
ktjamms2

8/22/2022 - Mon
SOLUTION
KenHadley

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ktjamms2

ASKER
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).
KenHadley

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!
ktjamms2

ASKER
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ktjamms2

ASKER
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
ktjamms2

ASKER
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!
KenHadley

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
KenHadley

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)
SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ktjamms2

ASKER
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?
dlmille

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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ktjamms2

ASKER
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.
dlmille

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
ktjamms2

ASKER
Yes, both paths are fully qualified and exist, and databases have exact same names
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ktjamms2

ASKER
Hi Ken,
Can you clarify exactly what the loop should look like with a sample?
Thanks!
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dlmille

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

Please let me know if that helped!

Dave
ktjamms2

ASKER
Confussed about this:

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

what is & "\Folder1" ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
dlmille

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
ktjamms2

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

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

Dave
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dlmille

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
ktjamms2

ASKER
Thank-you very much! These are great solutions.