Access Database on Web Servers

[ctkevin]

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

[Trevor Best]

Re: Access Database on Web Servers

ctkevin wrote:[color=blue]
> 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.[/color]

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.
[color=blue]
> 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.[/color]

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

[ctkevin]

Re: Access Database on Web Servers

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.

[Trevor Best]

Re: Access Database on Web Servers

ctkevin wrote:[color=blue]
> 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.
>[/color]

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 FileSystemObjec t, 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.Server Variables, PATH_INFO and PATH_TRANSLATED IIRC)


--
This sig left intentionally blank

[billmiami2@netscape.net]

Re: Access Database on Web Servers

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 CreateSpreadshe et(objRS)
'Create the spreadsheet
Set ss1=CreateObjec t("OWC.Spreadsh eet")
Set c = ss1.Constants
Set ws=ss1.ActiveSh eet
'ws.range("A1") .VAlignment=2

' Set the row height and column width
'ws.Columns.Col umnWidth=322
'ws.Rows.RowHei ght=25

'Place the values into the cells
i=0
Do until objRS.EOF
i=i+1
With ws
'.cells(i,1).fo nt.size=8
.cells(i,1).val ue=i
.cells(i,2).val ue=myvalue4(obj RS.fields("Cust omerID"))
.cells(i,3).val ue=myvalue4(obj RS.fields("Last Name"))
.cells(i,4).val ue=myvalue4(obj RS.fields("Firs tName"))
.cells(i,5).val ue=myvalue4(obj RS.fields("Clas sLevel"))
.cells(i,6).val ue=myvalue3(obj RS.fields("AvgO fTableResult"))
.cells(i,7).val ue=myvalue3(obj RS.fields("AvgO fTheoValue"))
.cells(i,8).val ue=myvalue3(obj RS.fields("Coun tOfCustomerID") )
End With
objRS.MoveNext
Loop

'Export the spreadsheet
mypath=server.M apPath("theoval ueexport.xls")
ws.export mypath, c.ssExportActio nNone
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.Conten tType =
"applicatio n/vnd.ms-excel"

Here's some code to make the same report using this method:


Sub CreateTextFile( objRS)
nr=0
Set fso=Server.Crea teObject("Scrip ting.FileSystem Object")
mypath=server.M apPath("theoval ueexport.txt")
Set mytextfile=fso. CreateTextFile( mypath,True)

mytextfile.writ eline
"#,CustomerID,L astName,FirstNa me,ClassLevel,A ctualValue365,T heoValue365,Voy ages365"

Do until objRS.EOF
nr=nr+1
mytext=nr & "," & myvalue4(objRS. fields("Custome rID")) & "," &
myvalue4(objRS. fields("LastNam e")) & ","
mytext = mytext & myvalue4(objRS. fields("FirstNa me")) & "," &
myvalue4(objRS. fields("ClassLe vel")) & ","
mytext = mytext & myvalue3(objRS. fields("AvgOfTa bleResult")) & "," &
myvalue3(objRS. fields("AvgOfTh eoValue")) & ","
mytext = mytext & myvalue3(objRS. fields("CountOf CustomerID"))
mytextfile.writ eline mytext
objRS.MoveNext
Loop

mytextfile.clos e
Set mytextfile=Noth ing
Set fso=Nothing
End Sub
%>

I hope this helps you.

Bill E
Hollywood, FL

ctkevin wrote:[color=blue]
> 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[/color]
aware[color=blue]
> 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.[/color]