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?
 
Dale FyeConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.