MS Access Export To MS Excel Script

[santa12345]

Hello.
Currently I have a process in place where in Access, I have a customer database. The customer database is a single locally stored database on my computer with no links to any other external databases on a server, all information resides within this one database. Within the customer database, I then use a form I created. The form simply has a button to run some vb script. The script then runs a series of queries pulling data from various other tables (customer address, customer purchases, etc... using [Forms]![Form1]![customer_id] and after each table query, exports to an excel customer id template file and renames the excel template file to the actual customer id.xls, etc.. and loops until I get thru the entire customer id table list. Usually around 2500-3000 customers.

Initially, it currently takes approx 1 minute per file to generate. Very Acceptable!!

Unfortunately, after around 500'ish files are generated, the process tends to start lagging. Taking 2,3,4 sometimes 5 minutes a file to generate. Before I just replace the workstation with a possibly faster processor, and try that, I am wondering if the current commands I am using are still "up to date" or if there is a better / more efficient way to do this.

I am basically using DoCmd.OpenQuery and DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8 commands to accomplish this.

I do have sleep commands between each OpenQuery command. So after running the Customer Address query, capturing that data and exporting to the template, Sleep (2000), and then start running the Customer Purchases query for example. I believe I added this due to getting a customer id template.xls file already in use type message in the past.

The code was written using the following:

Microsoft Access Database (.accdb) Access 2016 File Format and Excel 97-2003 Worksheet

Any suggestions / ideas would be greatly appreciated. Thank you.

 

[santa12345]

I would still be going with a simple select query for all those queries. Then just use the simple TransferSpreadsheet as I have already said a good few times?

Hi..it is possible now.. i changed the queries to selects.. and doing a transfer from there... but each file being generated now has the same data in it... Not sure how that would mess up the next record (cust id) being passed to the next file, etc.. I'll stop the vba step by step and see if i can figure out why.

 

[santa12345]

Hi..it is possible now.. i changed the queries to selects.. and doing a transfer from there... but each file being generated now has the same data in it... Not sure how that would mess up the next record (cust id) being passed to the next file, etc.. I'll stop the vba step by step and see if i can figure out why.

After the first file is generated, the next customer ID being passed via the recordset is not changing. How do i close the select queries before the next loop is performed. I assume that is the issue.

 

[welshgasman]

You DO NOT OPEN the queries. The transfer does that for you.
You must have paramterised the original make table queries in the first place, to get different data for each customer, so you do the same with the select queries.

I would use tempvars() for any criteria and set it from each record in the recordset.
Post the query sql within code tags please.

 

[santa12345]

You DO NOT OPEN the queries. The transfer does that for you.
You must have paramterised the original make table queries in the first place, to get different data for each customer, so you do the same with the select queries.

I would use tempvars() for any criteria and set it from each record in the recordset.
Post the query sql within code tags please.

can you do a select query on top of another select query before transferring.. it seems to not like that
also with the suggested changes i get the runtime error message stating the DB is already open, click the play button and the macro / loop continues just fine

 

[welshgasman]

Yes, you can select from another select query, without any issues.
Without seeing the code, I am working in the dark here?

I really have no idea as to what you are doing now? If you could run make table queries without any issues, select queries would be just fine. Why the DB is in use message, I have no idea.

I am off back home from Mexico in an hour or so, so no reply from me until Thursday probably.

 

[santa12345]

Yes, you can select from another select query, without any issues.
Without seeing the code, I am working in the dark here?

I really have no idea as to what you are doing now? If you could run make table queries without any issues, select queries would be just fine. Why the DB is in use message, I have no idea.

I am off back home from Mexico in an hour or so, so no reply from me until Thursday probably.

i am exporting select queries using transfer now. i guess one of my questions is..should i write the data (first pass for example) to a template file and then rename it, and loop...or should i be transferring the data to a temp file and then rename it, and loop... as i received a "db is already in use" and a runtime 3051 error "template.xls" already in use messages..

safe travels! i will report back with progress

 

[welshgasman]

Back home now.

I am extremely puzzled as to why you are having these issues?
We have not changed how you were doing things, just how you do them, more efficiently?
The process remains the same.