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?

[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.

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

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
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:
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
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
Microsoft Access

From novice to tech pro — start learning today.