By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,098 Members | 1,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,098 IT Pros & Developers. It's quick & easy.

Access Database on Web Servers

P: n/a
I am an experienced Access user but recently moved an Access database
to the web for my employer.

We use a host that does not allow the use of data access pages.

Is there anyway to pre-build a query and/or report in Access that could
generate an Excel download? I was thinking of using a macro, but I
have no idea how to invoke the macro.

Thanks in advance!

Kevin

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
ctkevin wrote:
I am an experienced Access user but recently moved an Access database
to the web for my employer.

We use a host that does not allow the use of data access pages.
Not surprising, DAPs are really just a mini Access running inside a web
page and gets the data from the MDB direct from the client side, this
would require that the client's PC could see a share on the web server
and see the MDB. It's a shitty marketing ploy to make Access users think
they can web enable their application and whoever invented it should be
hanged, drawn and quartered, stitched together, brought back to life and
killed all over again.
Is there anyway to pre-build a query and/or report in Access that could
generate an Excel download? I was thinking of using a macro, but I
have no idea how to invoke the macro.


That would require that Access actually ran on the server, I seriously
doubt your ISP would allow that either, they want their server to stay
up for more than half a day and/or can't afford to hire a full time
person to kill the hung processes as Access displayed it's error
messages to no-one.

You need to look into using ASP 3.0, ASP.NET, CGI, Cold Fusion or
similar technology to web enable the application.

--
This sig left intentionally blank
Nov 13 '05 #2

P: n/a
Hey Trevor - thanks for the commentary.... :)

Guess I should've mentioned that I used Dreamweaver to build an
application around this database using ASP and VB. I'm just not aware
of any method for allowing a user to take a pre-built query or report
and downloading it as an Excel file. If this can be done using ASP,
can you direct me to an appropriate resource for reading? I have
several books on ASP I just don't know where to begin looking.

Nov 13 '05 #3

P: n/a
ctkevin wrote:
Hey Trevor - thanks for the commentary.... :)

Guess I should've mentioned that I used Dreamweaver to build an
application around this database using ASP and VB. I'm just not aware
of any method for allowing a user to take a pre-built query or report
and downloading it as an Excel file. If this can be done using ASP,
can you direct me to an appropriate resource for reading? I have
several books on ASP I just don't know where to begin looking.


Using normal BASIC I/O you can produce a csv file, readable in Excel but
since ASP's version of VBScript is a little light BASIC i/o is not
implemented and you need to use the Scripting FileSystemObject, which
may prove troublesome is the host is running an older version of some AV
programs with script blocking.

Otherwise it would need to have the Excel object libraries on there for
you to create an Excel object to create the excel file. You'll need to
know the local and virtual paths (local to create, virtual for user to
download it), they're available in the server environment
(Request.ServerVariables, PATH_INFO and PATH_TRANSLATED IIRC)
--
This sig left intentionally blank
Nov 13 '05 #4

P: n/a
kevin,

You should never attempt to open MS Excel or any other office
application directly from your web application on your server. This
will completely bog down your application. Instead, Microsoft has
create Office Web Components (OWC) specifically for generating Excel
worksheets and charts on the server which can then be viewed in the
browser as Excel. You can download a reference for using OWC from the
microsoft site at
http://msdn.microsoft.com/office/und...c/default.aspx. As
long as you install MS Office on the server, you will have use of OWC.

I don't use ASP any more, but here's a snippet of code I once used to
generate an Excel worksheet with OWC:

<%
Sub CreateSpreadsheet(objRS)
'Create the spreadsheet
Set ss1=CreateObject("OWC.Spreadsheet")
Set c = ss1.Constants
Set ws=ss1.ActiveSheet
'ws.range("A1").VAlignment=2

' Set the row height and column width
'ws.Columns.ColumnWidth=322
'ws.Rows.RowHeight=25

'Place the values into the cells
i=0
Do until objRS.EOF
i=i+1
With ws
'.cells(i,1).font.size=8
.cells(i,1).value=i
.cells(i,2).value=myvalue4(objRS.fields("CustomerI D"))
.cells(i,3).value=myvalue4(objRS.fields("LastName" ))
.cells(i,4).value=myvalue4(objRS.fields("FirstName "))
.cells(i,5).value=myvalue4(objRS.fields("ClassLeve l"))
.cells(i,6).value=myvalue3(objRS.fields("AvgOfTabl eResult"))
.cells(i,7).value=myvalue3(objRS.fields("AvgOfTheo Value"))
.cells(i,8).value=myvalue3(objRS.fields("CountOfCu stomerID"))
End With
objRS.MoveNext
Loop

'Export the spreadsheet
mypath=server.MapPath("theovalueexport.xls")
ws.export mypath, c.ssExportActionNone
Set c=Nothing
Set ws=Nothing
Set ss1=Nothing
End Sub
%>

OWC is OK but the fastest way to create the report is to generate a
text file and save it as a CSV file, which has already been mentioned.
Once saved you can have the user open it with a link. Even better, I
think that you can stream the output directly to the browser and have
it opened as Excel by setting Response.ContentType =
"application/vnd.ms-excel"

Here's some code to make the same report using this method:
Sub CreateTextFile(objRS)
nr=0
Set fso=Server.CreateObject("Scripting.FileSystemObjec t")
mypath=server.MapPath("theovalueexport.txt")
Set mytextfile=fso.CreateTextFile(mypath,True)

mytextfile.writeline
"#,CustomerID,LastName,FirstName,ClassLevel,Actual Value365,TheoValue365,Voyages365"

Do until objRS.EOF
nr=nr+1
mytext=nr & "," & myvalue4(objRS.fields("CustomerID")) & "," &
myvalue4(objRS.fields("LastName")) & ","
mytext = mytext & myvalue4(objRS.fields("FirstName")) & "," &
myvalue4(objRS.fields("ClassLevel")) & ","
mytext = mytext & myvalue3(objRS.fields("AvgOfTableResult")) & "," &
myvalue3(objRS.fields("AvgOfTheoValue")) & ","
mytext = mytext & myvalue3(objRS.fields("CountOfCustomerID"))
mytextfile.writeline mytext
objRS.MoveNext
Loop

mytextfile.close
Set mytextfile=Nothing
Set fso=Nothing
End Sub
%>

I hope this helps you.

Bill E
Hollywood, FL

ctkevin wrote:
Hey Trevor - thanks for the commentary.... :)

Guess I should've mentioned that I used Dreamweaver to build an
application around this database using ASP and VB. I'm just not aware of any method for allowing a user to take a pre-built query or report
and downloading it as an Excel file. If this can be done using ASP,
can you direct me to an appropriate resource for reading? I have
several books on ASP I just don't know where to begin looking.


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.