While exporting to Excel, export is slow and/or crashes Access 1

[tsosiel]

I have a database that uses filters to display data on forms and reports. Now I need to send the filtered data to an Excel Spreadsheet. I managed to do this two ways:

1. Using a recordset and sending the data to an Excel object; but this method is SLOW and I've tried to optimize the method as best I could. Below is the function that performs the operation.

Public Function procExpQry(strQry As String, strCriteria As String) As Boolean

Dim db As Database
Dim qryPass As QueryDef
Dim qryLocal As QueryDef
Dim rs As Recordset
Dim strSaveAs As String
Dim strSQL As String
Dim introwas Integer
Dim intcol As Integer
Dim objXL As New Excel.Application

Set objXL = CreateObject("Excel.Application&quot
objXL.Workbooks.Add

Set db = CurrentDb()
Set qryPass = db.QueryDefs(strQry)
strSQL = qryPass.SQL
strSQL = fucnRefineSQL(strSQL) 'will change to proper SQL
qryPass.Close
Set qryPass = Nothing
Set qryLocal = db.CreateQueryDef("&quot
qryLocal.SQL = strSQL
Set rs = qryLocal.OpenRecordset()

introw = 1
For intcol = 1 To rs.Fields.count
objXL.ActiveSheet.Cells(introw, intcol).Value = rs.Fields.Item(intcol - 1).Name
Next intcol

introw = introw + 1
Do While Not rs.EOF
For intcol = 1 To rs.Fields.count
objXL.ActiveSheet.Cells(introw, intcol).Value = rs.Fields.Item(intcol - 1).Value
Next intcol
rs.MoveNext
Loop

Set objXL = Nothing
Set rs = Nothing
Set qryPass = Nothing
Set db = Nothing

End Function


2. Combining the SQL portion of my saved query with the Criteria set by the user to Make a table (make-table query) and then using the TransferSpreadsheet command. This works...! The first time, but bombs after 3-4 iterations of the same process! The Make-Table query runs fine; its the TransferSpreadsheet that crashes almost all the time!

Public Function procMSExpQry(strQry As String, strCriteria As String) As Boolean

Dim db As Database
Dim qryPass As QueryDef
Dim rs As Recordset
Dim strSaveAs As String
Dim strSQL As String
Dim objXL As New Excel.Application

DoCmd.SetWarnings False

strSaveAs = "c:\my documents\" & strQry & "_" & Format(Date, "yyyy_mm_dd&quot & ".xls"
Set db = CurrentDb()
Set qryPass = db.QueryDefs(strQry)
strSQL = qryPass.SQL
qryPass.Close
Set qryPass = Nothing
strSQL = funcFixSQL(strSQL, strCriteria)

strSQL = Left(strSQL, InStr(1, strSQL, "FROM&quot - 1) & " INTO tblDummy " & Right(strSQL, Len(strSQL) - InStr(1, strSQL, "FROM&quot + 1)
DoCmd.RunSQL strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tblDummy", strSaveAs

db.Close
Set db = Nothing
DoCmd.SetWarnings True

End Function


In both instances, I've notice that Method#2 is faster and cleaner, but it crashes Access more than method 1! Of the two main tables I query/filter, the maximum record count is about 1000!!! I can't understand why this is happening. If there is someone that can help me, I'd appreicate it. I'd eventually like to open the Excel spreadsheet and perform formatting tasks to the spreadsheet!

Thank you in advance for any assistance/suggestions!

Lawrence

 

[seaport]

When publishing data in excel, I always use copyfromrecordset method of the excel range. It rans fast. However, you need to use ADO rather then DAO in this method. Check the reference of excel.

Seaport

 

[jacksonmacd]

Just discovered the copyFromRecordset method myself. It was 12x faster on my export than the equivalent record-by-record loop.

BTW - was done in Acc97 using DAO.

 

[tsosiel]

Okay, I tried the CopyFromRecordset and it worked fine with out crashing Access. However, there was is a new error - "Run-time error ... Method 'CopyFromRecordSet' of object 'Range' failed. This occured in one of the two tables I have in my database. This particular table has 23 fields (2 number, 3 date/time, 13 text, 1 yes/no, and 4 Memo) with 1000 records.

I then imported another table w/17 fields and 18000 records - this worked fine and no errors! I then analyzed the table and noticed that one of the memo fields (ProblemDescription) in my table has a record that reached the limit for a field of type Memo!! I then removed each memo field and ran the method and removed another and another. It came down to 1 memo field and the method worked!

Therefore, I think that the size of the recordset is taken into consideration when exporting using the CopyFromRecordset method; this could explain Access crashing using the TransferSpreadsheet. All this was done using DAO.

I need these queries/tables to be exported! If anyone has suggestions on how this can be done, please let me know. Thank you in advance for your help!

Lawrence

 

[Klopper]

Using Access 97 and DAO, I tested CopyFromRecordset and found it was actually about 50 per cent slower than Docmd.Transferspreadsheet.

Its taking around 10 minutes to tranfser 4000 records (40 odd fields), yet only 10 seconds to open the underlying query. I have not experienced stability issues with Docmd.Transferspreadsheet.