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]

Hello. i was getting that runtime error before as well....once in a while. very hit and miss thou.

I've updated the code with all of the 10 DoCmd.OpenQuery's to run together before any of the 10 DoCmd.TransferSpreadsheet acExport's execute, which are at the bottom of the code, all together as well..... before looping back to the top of the code for the next customer id. This seems to have fixed the Runtime 5031 error.

So does the range.copyfromrecordset method replace the DoCmd.Transfer Spreadsheet acExport command?

 

[santa12345]

Were you getting that before?, as you are meant to be writing to a new file for each customer that should not occur. If you are using a template how did you manage before?

Another way might be CopyFromRecordset ?

Range.CopyFromRecordset method (Excel)

Office VBA reference topic

learn.microsoft.com

Thank you for the reply.
The runtime has always been a hit and miss error message i've received now and in the past.

I've updated the code to have all 10 DoCmd.OpenQuery's run first and then at the bottom of the code before looping, I have all 10 DoCmd.TransferSpreadsheet acExport's together.
This seems to have fixed the runtime error issue.

Does the CopyFromRecordset command replace the DoCmd.TransferSpreadsheet ? Can I use Select queries vs. Make Table queries to make it faster, or doesn't that matter?

 

[welshgasman]

You do not need to open the queries, the TransferSpreadSheet will do that for you.
Try one and see from the immediate window.

 

[santa12345]

You do not need to open the queries, the TransferSpreadSheet will do that for you.
Try one and see from the immediate window.

hello again. sorry to keep asking questions as I've struggled with this for quite some time.
If i don't open the queries, i still have to run the queries for the next customer id in line...etc...


Maybe I haven't been the clear 'est on what I'm trying to accomplish.... so I've included the entire code I am using.....the code is working, just very slow compiling 3000+ excel files every month. I've indexed the customer id field in the necessary tables... which helped. THANK YOU FOR THAT!!

**************************************

Private Sub command25_Click()

DoCmd.SetWarnings 0

Dim strWI, strDistrict, strxcel, objFolder, strBO
Dim Temp As Long
Dim ddate As Date
Dim dddate As Integer
Dim datename As String

Set objFSO = CreateObject("Scripting.FileSystemObject")

' code will save each customer ID file in the c:\customers\monthly folder..based on the month I entered in a dialog box in the FORM and name the file with the customer ID in the appropriate region ie... c:\customers\monthly\June\Europe\Customer ID-0000012345

ddate = CDate(TxtExp)
ddate = ddate + 1
dddate = Month(ddate)
datename = MonthName(dddate)

Dim RetVal

Set objFolder = objFSO.CreateFolder("c:\customers\monthly\" & datename)
Set objFolder = objFSO.CreateFolder("c:\customers\monthly\" & datename & "\" & "Europe")
Set objFolder = objFSO.CreateFolder("c:\customers\monthly\" & datename & "\" & "Latin America")
Set objFolder = objFSO.CreateFolder("c:\customers\monthly\" & datename & "\" & "Canada")

strxcel = ".xls"
strWI = "Customer ID-"

'code will take the first customer ID from the customer id table, run the 10 queries stated below for that particular ID, eventually having 10 tables ready to export to excel for that particular customer ID. before looping to the next customer ID.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("customer id table", dbOpenTable)

rs.MoveFirst

Do Until rs.EOF

cust_id = rs![customer id]
strDistrict = rs![country]

'this code will copy a customer id template (smaller file size) to a customers.xls file before starting the loop for exports, etc.. I did this because as the loop would generate files, the file size on a bigger customer ID was larger in file size, and that file size would then "stick" for the remaining customer id files that were generated after....

Sleep (5000)
RetVal = Shell("cmd /c copy /y c:\customers\monthly\customer-template.xls c:\customers\monthly\customers.xls", vbHide)
Sleep (5000)

'here are the 10 queries that need to be ran for each individual customer id listed in the customer id table - these are currently make table queries

DoCmd.OpenQuery "1 - customer id information", , acReadOnly
DoCmd.OpenQuery "2 - customer id purchases", , acReadOnly
DoCmd.OpenQuery "3 - customer id additional info 1", , acReadOnly
DoCmd.OpenQuery "4 - customer id additional info 2", , acReadOnly
DoCmd.OpenQuery "5 - customer id additional info 3", , acReadOnly
DoCmd.OpenQuery "6 - customer id additional info 4", , acReadOnly
DoCmd.OpenQuery "7 - customer id additional info 5", , acReadOnly
DoCmd.OpenQuery "8 - customer id additional info 6", , acReadOnly
DoCmd.OpenQuery "9 - customer id additional info 7", , acReadOnly
DoCmd.OpenQuery "10 - customer id additional info 8", , acReadOnly

'here are the 10 export statements to export each of the 10 query datasets to excel

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id information", "c:\customers\monthly\customers.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id purchases", "c:\customers\monthly\customers.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id additional info 1", "c:\customers\monthly\customers.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id additional info 2", "c:\customers\monthly\customers.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id additional info 3", "c:\customers\monthly\customers.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id additional info 4", "c:\customers\monthly\customers.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id additional info 5", "c:\customers\monthly\customers.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id additional info 6", "c:\customers\monthly\customers.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id additional info 7", "c:\customers\monthly\customers.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "customer id additional info 8", "c:\customers\monthly\customers.xls", True

'now that all 10 queries have been ran and the 10 outputs were exported to the customers.xls file, save the file to the appropriate folder and label it as such...ie ....c:\customers\monthly\June\Europe\Customer ID-0000012345....this is working great

Set objFolder = objFSO.CreateFolder("c:\customers\monthly\" & datename & "\" & strDistrict & "\" & strWI & cust_id)
RetVal = Shell("cmd /c copy /y c:\customers\monthly\customers.xls c:\customers\monthly\" & datename & "\" & strDistrict & "\" & strWI & cust_id & "\" & strWI & cust_id & strxcel, vbHide)

Sleep (2000)

'move to next customer id in the customer id table and loop until the last customer id has been reached

rs.MoveNext

Loop

DoCmd.SetWarnings -1

End Sub

 

[welshgasman]

Please repost and use the code tags,.
Use the </> icon.

You need to parametise the queries for the customer ID. Then set that before Transfer.
You could use a TempVar for that.
How are they parameteised now?, I cannot see how?

I honestly do not know if the Transfer would take the open query data, or open another copy?. That could be an issue as you are opening everything twice.
Try commenting out the Openquery and run for just a few customers by limiting the recordset.

BTW, when you open a recordset, if any records exist, you are the first record, so MoveFirst is not required.

As you are running for each month, I expect indexing whatever date you use would help as well? ANYTHING that is in the where clause.

As you are opening the query ATM, should you not be closing them all before the next customer?