For this example we will create a Comma Separated Values (CSV) file with our information in, and in the first case simply provide a link to this file and in the second we will automatically trigger a download of this file to the local computer.
So, we need to get all of our data out of the datasource. for this example I am going to use the getString function which returns a recordset as a single string, although for more complex processes you may need to extract each element of the recordset in turn to perform an action on it, such as adding to a total, formatting etc.
' Create a connection object
set dbConn = server.createobject("ADODB.connection")
' Open a connection using the following criteria - DSN Name, UserName, Password
dbConn.open "myDSN", "username", "password"
SQLStatement="SELECT * FROM myTable"
' if no results found
if results.EOF then
' no results
Response.Write("No Data Found")
' grab the string, using a comma as a record separator and a tab as a record separator
ResultsString = results.GetString(2, , ",", vbTab)
' create a link to the local file system on the server
Set objFileSystemObject = Server.CreateObject("Scripting.FileSystemObject")
' Create a text file and dump the contents of the string to the file,
' using the file name export with the current date and time as a way to seperate out differing users
filename="export" & CLng(Now)
Set objTextStream = objFileSystemObject.CreateTextFile(filename & ".csv", 2, True)
' Clean up
Set objTextStream = Nothing
Set objFileSystemObject = Nothing
' return a link to the file to the browser
Response.Write("The file <a href='" & filename &".csv'>" & filename & ".csv</a> is now available to download")
If you are using MS Access as your database, from which you are creating the files, you have several different options for placing the data into the server side Excel / CSV files. A review of some of these, including their pro's, con's and some metrics can be found on this website.
In the final section of this article, we summarise the different approaches and provide links for futher reading on the subject.