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.

 

[welshgasman]

Make sure your pertinent fields for your query have indices.
I would be reading the customer table as a recordset and generate the query data from that each time, so I would only have one OpenQuery?
In fact I probably would not even have that, as that query could be used in the DoCmd.TransferSpreadsheet command?

 

[santa12345]

Make sure your pertinent fields for your query have indices.
I would be reading the customer table as a recordset and generate the query data from that each time, so I would only have one OpenQuery?
In fact I probably would not even have that, as that query could be used in the DoCmd.TransferSpreadsheet command?

Thank you for the reply. Shoot you lost me, can you give me an example please ?

 

[santa12345]

Are you saying index my customer ID fields in all the queried tables?

 

[welshgasman]

Index whatever you are using in the where.

As for examples not really.
This is the best I can do, but I am generating a report here.

Sub Print_All_Ships()
 Dim rs As DAO.Recordset
 Dim db As Database
 Dim stSQL As String, stDate As String, stDBpath As String, stFTPpath As String
 Dim stRptName As String, stParam As String, stLinkCriteria As String, stAlphabet As String, astAlpa(1, 26) As String
 Dim stStart As String, stEnd As String, iloop As Integer
 Dim iOKCancel As Integer
  
 ' GoTo rptalpha
  
 stRptName = "Main_by_Ship"
 Set db = CurrentDb
' Generate all the Ship reports
' GoTo rptleave:
stDBpath = CurrentProject.Path & "\"
stFTPpath = stDBpath & "Gazette\"

 stSQL = "SELECT Ship.Ship FROM Ship WHERE (((Ship.ID)<> 26 and (Ship.ID)<> 27 and (Ship.ID)<> 60))ORDER BY Ship.Ship"

 Set rs = db.OpenRecordset(stSQL)

 Do While Not rs.EOF
 ' Need to convert any spaces in ship name to _ for website
    stParam = LCase(Replace(rs!Ship, " ", "_"))
    stLinkCriteria = "[Ship] = '" & rs!Ship & "'"
    
    'DoCmd.CopyObject , stParam, acReport, stRptName
    DoCmd.OpenReport stRptName, acViewPreview, , stLinkCriteria, acHidden
 ' Pause for 5 seconds to save report
    'Pause (5)
    DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
    DoCmd.Close acReport, stRptName
    'DoCmd.DeleteObject acReport, stParam

    rs.MoveNext
    
'    iOKCancel = MsgBox("OK to proceed?", vbOKCancel)
'    If iOKCancel = vbCancel Then
'        Exit Sub
'    End If
    
 Loop
 rs.Close
 Set rs = Nothing
End Sub

Now you could either build the query inline and save it, or again I would probably use parameters or tempvars.
Set them in your code then the query uses those to select a particular customer.
In my case it is a ship, but the logic is the same.

So one query and use a parameter(s) to run it every time.

 

[santa12345]

thank you. before updating the code i indexed the customer id field in the tables i am querying and that seems to speed it up a bit. I do get a 5031 run time error sometimes stating the cust.xls is already open when trying to perform a DoCmd.TransferSpreadsheet acExport on a various line.

here is the bulk of my current code....the first line is copying a template to a cust.xls file besides as the process compiles customer files, the starting point seems to get bigger in size, so this step has helped with starting with the same size

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

Sleep (5000)
RetVal = Shell("cmd /c copy /y C:\test\cust_template.xls C:\test\cust.xls", vbHide)
Sleep (5000)


DoCmd.OpenQuery "cust address", , acReadOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "cust address", "C:\test\cust.xls", True
Sleep (2000)
DoCmd.OpenQuery "cust purchases", , acReadOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "cust purchases", "C:\test\cust.xls", True
Sleep (2000)
DoCmd.OpenQuery "cust other data", , acReadOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "cust other data", "C:\test\cust.xls", True
Sleep (2000)
DoCmd.OpenQuery "cust other data2", , acReadOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "cust other data2", "C:\test\cust.xls", True
Sleep (2000)

Set objFolder = i have logic to name the file to a particular folder based on the month and the filename is labeled with the customer id ...which is all working



Sleep (2000)

rs.MoveNext

Loop

DoCmd.SetWarnings -1


End Sub

 

[welshgasman]

I thought you were running a query, the same query for many customers?
Are all those queries each for one customer.

 

[santa12345]

I thought you were running a query, the same query for many customers?
Are all those queries each for one customer.

sorry for the confusion... yes... i run a series of queries for one customer...about 10 queries... exports 10 tabs to excel.... names the file..and then loops again.. until all customer ids have been processed. query, export, next query, export, etc... then loops... the index on the table for the customer id is good... sped up the process a little..but now i'm getting the 5031 run time error... template xls file is already in use sort of thing

 

[Micron]

If that is involved in the loop then you could trap the error, advance a counter variable by 1 and have the code resume n times as per your counter. I don't see the need for the sleep between the transfer and Set objFolder lines. Maybe have the error handler add a one second pause there before trying again.

 

[welshgasman]

sorry for the confusion... yes... i run a series of queries for one customer...about 10 queries... exports 10 tabs to excel.... names the file..and then loops again.. until all customer ids have been processed. query, export, next query, export, etc... then loops... the index on the table for the customer id is good... sped up the process a little..but now i'm getting the 5031 run time error... template xls file is already in use sort of thing

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