Export table to Excel from Access using ASP

  • Thread starter Thread starter News
  • Start date Start date
N

News

Hi,

I have an Access database recently converted to 2003 from 2002. I used the
following command to export a table to Excel from an ASP page:

Set objAccess = Server.CreateObject("Access.Application")
With objAccess
..Visible = False
..OpenCurrentDatabase DatabasePath, False
..DoCmd.SelectObject acTable, Table, True
End With
objAccess.DoCmd.OutputTo acOutputTable, Table, acFormatXLS, szOutputFile,
False
objAccess.Quit

However, with 2003 it does not seem to work. Am I doing something wrong?
Is there a better way?

Chris Dyck
 
I use this code to output Access data to an Excel format from ASP pages:

Response.ContentType="application/csv"
Response.AddHeader "Content-Disposition", "filename=RepDirectory.csv;"

q = """"
sDelim = q & "," & q

sline = q & "Company" & sDelim & "Contact" & sDelim & "Cell" & sDelim _
& "Phone" & sDelim & "Fax" & sDelim & "Email" & q
Response.Write(sLine)
Response.Write vbNewLine

' Create an ADO database connection
Set cnnOD = server.createobject("adodb.connection")
cnnOD.open(Session("strConnect"))

' Select records
sSQL = "SELECT * FROM tblPersons WHERE Active=True ORDER BY Company, Contact"
Set rsOut = server.CreateObject("adodb.recordset")
Set rsOut = cnnOD.Execute(sSQL)

Do Until rsOut.EOF
sLine = ""
sLine = sLine & q & rsOut.Fields("Company") & q & ","
sLine = sLine & q & rsOut.Fields("Contact") & q & ","
sLine = sLine & q & rsOut.Fields("Cell") & q & ","
sLine = sLine & q & rsOut.Fields("Phone") & q & ","
sLine = sLine & q & rsOut.Fields("Fax") & q & ","
sLine = sLine & q & rsOut.Fields("Email") & q & ","

Response.Write(sLine)
Response.Write vbNewLine

rsOut.MoveNext
Loop

Set rsOut = Nothing
Set cnnOD = Nothing
Response.End
 
Hi Dan,

That'll do.

Many thanks,
Chris Dyck

Danny J. Lesandrini said:
I use this code to output Access data to an Excel format from ASP pages:

Response.ContentType="application/csv"
Response.AddHeader "Content-Disposition", "filename=RepDirectory.csv;"

q = """"
sDelim = q & "," & q

sline = q & "Company" & sDelim & "Contact" & sDelim & "Cell" & sDelim _
& "Phone" & sDelim & "Fax" & sDelim & "Email" & q
Response.Write(sLine)
Response.Write vbNewLine

' Create an ADO database connection
Set cnnOD = server.createobject("adodb.connection")
cnnOD.open(Session("strConnect"))

' Select records
sSQL = "SELECT * FROM tblPersons WHERE Active=True ORDER BY Company, Contact"
Set rsOut = server.CreateObject("adodb.recordset")
Set rsOut = cnnOD.Execute(sSQL)

Do Until rsOut.EOF
sLine = ""
sLine = sLine & q & rsOut.Fields("Company") & q & ","
sLine = sLine & q & rsOut.Fields("Contact") & q & ","
sLine = sLine & q & rsOut.Fields("Cell") & q & ","
sLine = sLine & q & rsOut.Fields("Phone") & q & ","
sLine = sLine & q & rsOut.Fields("Fax") & q & ","
sLine = sLine & q & rsOut.Fields("Email") & q & ","

Response.Write(sLine)
Response.Write vbNewLine

rsOut.MoveNext
Loop

Set rsOut = Nothing
Set cnnOD = Nothing
Response.End

--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast
Click to expand...
 
Back
Top