How to append Access query results to an Excel spreadsheet?

I have a query that loops through a list of db schemas and outputs the results to individual pdfs. I would like to append the results to a single excel spreadsheet instead of individual files. How do I do this?

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 air_client_" & Cus & ".dbo.usagereport "
qd.SQL = ssql

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

LVL 1
JeffDeveloperAsked:
Who is Participating?
 
JeffDeveloperAuthor Commented:
So to resolve this I created a standard Access Append query named "Append". I updated my VBA to call the query by adding:

qd2 As DAO.QueryDef
Set qd2 = db.QueryDefs("Append")
qd2.Execute

Open in new window


Function UsageReport()
Dim ssql As String, lookup As String
Dim qd As DAO.QueryDef, db As DAO.Database, qd As DAO.QueryDef
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")
Set qd = db.QueryDefs("Append")

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

qd2.Execute

On Error GoTo UsageReport_Err

UsageReport_Exit:
    Exit Function

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

Open in new window


Likely not the best answer but it is simple and functional.
0
 
PatHartmanCommented:
Making a report and exporting it as a pdf is different from exporting to excel.  Were you planning on having the Excel output look like a report or simply be a sheet with all the basic data?

If the query I see in the code is the recordsource for the report, you will need to create a union query that unions all the cus tables and then export the union query.

Either hardcode the table names or build the union using a loop.  A warning though.  The number of tables/queries in a union is limited.  I'm not sure what the limit is but it is probably too small.  If you run into trouble with this method, you will need to use OLE automation.  You would use code similar to what you have above except that instead of opening a .pdf for each set of data, you would append a set of data.  I don't have any sample code but someone else may have.  However, if the data will go on to separate tabs in the workbook, you can just export different queries to the same spreadsheet using different query names and that will create different tabs.
0
 
JeffDeveloperAuthor Commented:
There are 30 different schemas being queried for the same information.

Each iteration of the loop produces 1 record with 7 fields: Customer, Month, Orders, Type1, Type2, Type3, Type4

Could I append the data to a local table? I've tried INSERT INTO localTable but it fails because it cannot find the localTable in the ODBC connection which it's not it exists in the Access db.

If I could get the data into a local table then exporting to excel from there is a piece of cake.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PatHartmanCommented:
You might want to consider normalizing your schema.  Rather than having 30 separate tables that include a piece of "data" in their names, you should have 1 table with an additional column that holds that "data".  That way you only need one form, one report, 1 query.  To work with one set of data at a time, use an argument that selects only the rows with the specific value.  Or using no argument, a simple select query gets a recordset that contains data from all the sets.  What you have is more like a spreadsheet than a proper relational database.
0
 
JeffDeveloperAuthor Commented:
Unfortunately, it's not my DB I'm just trying to accomplish some reporting from it and maybe I have my terminology off a bit causing confusion.

There is the main database connection that I connect to via ODBC. Within that main connection there are 35 to 40 separate databases of which most are customer related databases. Within each one of those is a batch of tables that pertain to each customer. Each customer is very custom. One table that most of these databases have in common is the UsageReport table. Within that table is high level order data such as device used, app used, total orders, etc...

Other than that each has it's own order table, item table, pricing table, etc... One may have 15 tables and another may have 25.
0
 
JeffDeveloperAuthor Commented:
Lack of response to question. Found my own solution.
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.