Discussion Locked

Running Access Macros via a Web Site

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have an Access 97 database running on an IIS Intranet server. I am using ASP to run SQL queries via HTML web pages. I have an Access Macro which I use to export the database to an Excel spreadsheet but I have to run this on a local saved copy of the database. Can this extract facility be offered to users with a browser so they can save an Excel version of the database while on line. I have read about the VB command DoCmd.TransferSpreadsheet but not sure if this is the best route. Anyone have any code ?

1 reply

by paul.graf

You could have the user submit a query and present them with choice of outputting to HTML table or to Excel. THe following include file outputs a resultset to an Excel sheet:

<%
'-----------------------------------------------
' Build Excel CSV file.
' NOTE: ASSUMES AT LEAST ONE RECORD IN RECORDSET
'-----------------------------------------------
Sub outputExcel
dim lIndex
dim hIndex
dim Cnt
dim excelHeader
dim excelRow

lIndex = LBound(displayFields)
hIndex = UBound(displayFields)

Response.ContentType = "application/vnd.ms-excel"
'Response.ContentType = "application/x-tsv"

' Now build column header
excelHeader = ""
For Cnt = lIndex to hIndex
If Cnt < hIndex Then
excelHeader = excelHeader & displayHeaders(Cnt) & vbTab
Else
excelHeader = excelHeader & displayHeaders(Cnt) & vbCrLf
End If
Next
Response.Write excelHeader

' Finally, output the data.
Do While Not objRecSet.EOF
excelRow = ""
For Cnt = lIndex to hIndex Select Case displayFields(Cnt)
Case "ARCHIVE_DATE"
excelRow = excelRow & OracleDate(objRecSet(displayFields(Cnt)))
'"DOC_DATE" will be included above when it is an actual date
Case Else
excelRow= excelRow & objRecSet(displayFields(Cnt))
End Select

If Cnt < hIndex Then
excelRow = excelRow & vbTab
Else
excelRow = excelRow & vbCrLf
End If

Next
Response.Write excelRow
objRecSet.MoveNext
Loop

objRecSet.Close
End Sub
%>

Hope this helps!

This discussion is closed to new replies.