Programmatically exporting an access table to excel

[emozley]

I have a database that has 8 fields and about 6,300 records. I have written a script that opens a filesystem object, writes to the contents of the recordset to this then closes that - thereby prodcing an Excel file.

Unfortunately it is painfully slow - takes about 2 or 3 minutes and then about the same again to open the file.

In Access you can right click on a table and export it and it generates the Excel table in about 5 seconds - is there any way of calling this function from a server side script?

Thanks very much

Ed

 

[Tarwn]

I don't know how your writing out your data (CSV, table and saved as XLS, Excel object?) but you should be able to dump the data much, much faster.

The fastest option would be to get your recordset and use the GetString function to very quickly dump the data as either CSV or a table that can be saved as XSL.

Here's a quick example of dumping to CSV:

Dim fso, fil, rsData, connData

'get a handle on the file
Set fso = Server.CreateObject("Scripting.FileSystemObject")
Set fil = fso.CreateTextFile(Server.MapPath("myOutput.csv"))

'database query
Set connData = Server.CreateObject("ADODB.Connection")
connData.Open "connection string here"
Set rsData = connData.Execute("SELECT * FROM MyTable")

'output column headers
Dim fld
For fld = 0 to rsData.Fields.Count
   If fil > 0 Then fil.Write ","
   fil.Write rsData.Fields.Count
Next
fil.Write vbCrLf

'output data
fil.Write """"
fil.Write rsData.getString(,,""",""","""" & vbCrLf & """","")
fil.Write """"

'Cleanup
fil.Close
Set fil = Nothing
set fso = Nothing

Set rsData = Nothing
connData.Close
Set connData = Nothing

The other option, if you need to do any calculation as your processing rows or need to use something like isNumeric, would be to use GetRows to convert the data to a two dimensional array. Once you do that you can get rid of the recordset and database connection. Using the array instead of a recordset usually speeds things up dramatically.

One last piece of advice: try not to do to much inside your loop. Remember that everything you do in a loop through your recordset is going to cost 6,300 times.

-T