Need Help Optimizing the Following Query - Running Queries in Access and Export various table outputs to Excel

[santa12345]

I am using office 365..current access db is saved as 2007-2016 format...and excel file is 97-2003 format.
I have the below code working, but it gets very slow as the process reaches 300, 400, 500 generations of separate files. I have approx 3K + files a month to generate unfortunately.
Any questions on the code, please let me know. I added some notes to hopefully help.

Basically, I have a customer id table with 3000+ ids in access. I need to run a series of queries against one customer id at a time, query each local table individually and export each table output into a tab in an excel template. The logic is working, but bogs down after a few hundred lines. Any way to optimize this. I've been trying everything that I can think of.

I use a form to enter the month I'm running. The queried field within each table is the customer id. I have indexed the customer ID in each table, which seems to help. But again, overall, after a few hundred files are generated, slows down.. When i start the process, it generates like one file per minute...after a few hundred, its like 3-5 minutes to generate a file. At that pace, it would take over a week to run the entire process from start to finish.

Can I optimize the vb as is and get better results.
Can I have VB written in Excel that would pull the data and create individual excel files.
Not sure if its the code that slowing it down, or since everything is happening from Access or possibly the workstation resources themselves.
Please help! I've been struggling with this for quite some time.

Thank you!

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

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]

I did ask you to use code tags I believe in your other crossposted thread?

 

[santa12345]

I did ask you to use code tags I believe in your other crossposted thread?

I'm sorry I didn't know what that meant. I thought you said to re-post and tags meaning at the bottom of the screen when posting... (excel, vb, export,..etc.. that screen)

 

[santa12345]

I'm sorry I didn't know what that meant. I thought you said to re-post and tags meaning at the bottom of the screen when posting... (excel, vb, export,..etc.. that screen)

Did you mean to reference the other post? and if so, how do you do that?