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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The database names are
db_client_cust1
db_client_cust2
db_client_cust5
The table name is UsageReport for all 3 tables.
db_client_cust1
db_client_cust2
db_client_cust5
The table name is UsageReport for all 3 tables.
Great. Then all you have to do is point the connection string to a different database.
ASKER
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.
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 FunctionASKER
I had to update the code.
This produces my pdf of the first customer in my list but fails to loop through the rest.
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 FunctionThis produces my pdf of the first customer in my list but fails to loop through the rest.
ASKER
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
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.