How do I run the same query/report against multiple db's on the same server?

MS Access is connecting to an MS SQL server via ODBC. The same pass through query is created multiple times to connect to different dbs in the same server. The only difference is the db that is being connected to. The result set changes of course but all the same fields. There is also 1 report per query. I would like to simply have 1 query and 1 report that reads through the dbs that need to be reported on. Is it possible to have a list of the db names that need reported on and loop through the names using vba and create a pdf of the report?

The db names are in the format of:
db_client_cust1
db_client_cust2
db_client_cust5

There is some skipping between the dbs to only report on those that require reporting.
LVL 1
JeffDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Yes, you would just need to save those "db names" either in an array or a table, then loop through them and change the Connect string of the querydef you are using to run the query.

Once you change the connection string, it will point to the database defined in that connection string
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
If the tables are in different databases, it would have been better to give them all the same name and allow the connection string to differentiate.  However, Access can save you here also.  You can a different name for the link than that of the actual table.  So when you link the table originally, give it a common name.

db_client_cust1
db_client_cust2
db_client_cust5

can each be named tblCustomer in the Access FE.  Of course that means they cannot all be linked at the same time.  So, you will have to relink them as Dale suggested BUT, you will also have to change the table name since you did not standardize the BE databases.
0
JeffDeveloperAuthor Commented:
The database names are

db_client_cust1
db_client_cust2
db_client_cust5

The table name is UsageReport for all 3 tables.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PatHartmanCommented:
Great.  Then all you have to do is point the connection string to a different database.
0
JeffDeveloperAuthor Commented:
I apologize, I'm failing to appropriately describe what I'm trying to do.

I have 1 odbc connection and through that 1 connection I connect to 30 schemas by changing my FROM statement. For example:

My first query would be

SELECT * FROM my_client_101

My next query would be

SELECT * FROM my_client_102

and I run 30 individual Pass Through queries to produce 30 separate reports. I would like to run 1 query that loops through a list that contains the customer so that the FROM is updated to connect to the corresponding schema. I'm not sure how to do that. This is the code I have so far.

Function UsageReport()
Dim ssql As String, lookup As String
Dim qd As DAO.QueryDef, db As DAO.Database
Dim Cus As String

Open "D:\Access\UsageList.txt" For Input As #1
Do While Not EOF(1)
Input #1, Cus

Set db = CurrentDb()
Set qd = db.QueryDefs("UsageReport") 

ssql = "Set nocount ON; SELECT * FROM my_client_ & CUS & .usagereport

On Error GoTo UsageReport_Err

    DoCmd.OutputTo acOutputReport, "UsageReport", "PDFFormat(*.pdf)", "D:\Reports\UsageReports\UsageReport_" & Cus & ".pdf", False, "", , acExportQualityPrint

UsageReport_Exit:
    Exit Function

UsageReport_Err:
    MsgBox Error$
    Resume UsageReport_Exit
Loop
Close 1
End Function

Open in new window

0
JeffDeveloperAuthor Commented:
I had to update the code.

Function UsageReport()
Dim ssql As String, lookup As String
Dim qd As DAO.QueryDef, db As DAO.Database
Dim Cus As String



Open "D:\Access\UsageList.txt" For Input As #1
Do While Not EOF(1)
Input #1, Cus

Set db = CurrentDb()
Set qd = db.QueryDefs("TESTUsageReport")

ssql = "Set nocount ON; select * from air_client_" & Cus & ".dbo.usagereport "
qd.SQL = ssql

On Error GoTo UsageReport_Err

    
    DoCmd.OutputTo acOutputReport, "TESTUsageReport", "PDFFormat(*.pdf)", "D:\Reports\UsageReports\UsageReport_" & Cus & ".pdf", False, "", , acExportQualityPrint


UsageReport_Exit:
    Exit Function

UsageReport_Err:
    MsgBox Error$
    Resume UsageReport_Exit
Loop
Close 1
End Function

Open in new window


This produces my pdf of the first customer in my list but fails to loop through the rest.
0
JeffDeveloperAuthor Commented:
Had LOOP in the wrong place. It now functions as I had hoped.

Function UsageReport()
Dim ssql As String, lookup As String
Dim qd As DAO.QueryDef, db As DAO.Database
Dim Cus As String



Open "D:\Access\UsageList.txt" For Input As #1
Do While Not EOF(1)
Input #1, Cus

Set db = CurrentDb()
Set qd = db.QueryDefs("TESTUsageReport")

ssql = "Set nocount ON; select * from air_client_" & Cus & ".dbo.usagereport "
qd.SQL = ssql

On Error GoTo UsageReport_Err

    
    DoCmd.OutputTo acOutputReport, "TESTUsageReport", "PDFFormat(*.pdf)", "D:\Reports\UsageReports\UsageReport_" & Cus & ".pdf", False, "", , acExportQualityPrint

Loop
Close 1

UsageReport_Exit:
    Exit Function

UsageReport_Err:
    MsgBox Error$
    Resume UsageReport_Exit

End Function

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.