[AccessD] Database Operation from Remote Sites

S Lee slee at asu.edu
Thu Feb 12 12:20:47 CST 2004


I do a lot of this.  Here is a sample that will load a long listing fairly quickly:

<%response.buffer=true%>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>XXXXXX</title>
<meta name="Microsoft Theme" content="loosegst 111, default">
<meta name="Microsoft Border" content="tlb, default">
</head>

<body>
<%
Dim PlayerPoints 
Dim oConn
Dim oRS
Dim strQry
strQry= "SELECT TOP 100 player, Sum(points) AS SumOfpoints FROM Points WHERE (TYear='2004') GROUP BY player ORDER BY Sum(points) DESC, player ASC"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=XXXXXXXXXXX_Points"
Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open strQry, oConn
PlayerPoints = oRS.GetRows()
CALL CloseALL
NumColsPP = UBound(PlayerPoints,1)
NumRecsPP = UBound(PlayerPoints,2)
%>
<div align="left">
  <table border="0" cellpadding="5" cellspacing="0" width="550">
    <tr>
      <td colspan="2">
        <blockquote>
            <p align="left"><b><font size="3" face="Arial"> XXXXXXXXX</font></b></p>
        </blockquote>
      </td>
    </tr>
    <tr>
      <td align="right" width="60%"> 
        <div align="right">
            <table border="0" width="85%" cellspacing="0" cellpadding="3">
            <tr><td width="30%"><p align="center"><font face="Arial" size="2"><b>Rank
            </b></font></td><td width="40%"><p align="left"><font face="Arial" size="2"><b>Player Name
            </b></font></td><td width="40%"><p align="left"><font face="Arial" size="2"><b>Points
            </b></font></td></tr>
<%
For i = 0 TO NumRecsPP
	Response.Write "<tr><td width=""30%""><p align=""center""><font face=""Arial"" size=""1""><b>"
	Response.Write (i + 1)
	Response.Write "</b></font></td><td width=""40%""><p align=""left""><font face=""Arial"" size=""1""><b>"
	Response.Write PlayerPoints(0,i)
	Response.Write "</b></font></td><td width=""40%""><p align=""left""><font face=""Arial"" size=""1""><b>"
	Response.Write PlayerPoints(1,i)
	Response.Write "</b></font></td></tr>"
Next
%>
     </table>
        </div>
        <p> </td>
      <td align="left" width="40%"> 
        <p> </td>
    </tr>
  </table>
</div>
 </body>
</html>
<%
SUB CloseAll
   oRS.Close
   Set oRS = Nothing
   oConn.Close
   Set oConn = Nothing
END SUB
%>

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
DWUTKA at marlow.com
Sent: Thursday, February 12, 2004 10:45 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Database Operation from Remote Sites


I see, that is a lot to ask for.  Let me explain how I learned ASP.  I had
been developing in VB, and in Access.  I liked both.  I switched our
company's Intranet from a file server, to an actual IIS server (web server).
This allowed for server side scripting, so I just decided to tinker.  ASP is
actually pretty simple (I'm almost ashamed to admit this, but I just
recently discovered 'conditional' HTML (I think that's what you call it),
which makes ASP even that much easier to develop in).  What you really need
to learn is HTML, and a little about how web pages work.

Anyhow, I just began to tinker.

I started with a db, with a table, and I wanted to see how to get the data
pushed from the db, into a webpage.

So, I built the following page: (Or something close to it)

<%
dim cnn
dim rs
dim strSQL
set cnn=server.createobject("ADODB.Connection")
set rs=server.createobject("ADODB.Recordset")
strSQL="SELECT MyField FROM tblMyTable"
cnn.Provider="Microsoft.Jet.OLEDB.4.0"
cnn.Open "D:\Mydatabase.mdb"
rs.open strSQL,cnn,1,1
rs.MoveFirst
Do Until rs.EOF=True
	response.write rs.Fields(0).value
	rs.MoveNext
Loop
rs.close
cnn.close
set rs=nothing
set cnn=nothing
%>

sure enough, I then had an asp page full of one field out of my table.

I then began tweaking the HTML, so instead of 'response.write
rs.Fields(0).value', I put 'response.write rs.Fields(0).value & "<br>"'

Which now put the data down a row.  the 'source' behind the page, however
was still one big line, which is a nightmare to try and decipher, so I
changed that line to 'response.write rs.Fields(0).value & "<br>" & vbcrlf'.
Now the page showed a 'column', and the source showed a column too.  Much
easier to read from both aspects.

>From there on, I then had to learn about how to 'get' data from an ASP page.
As you can see, pushing data out is easy, getting it turns out to be just as
easy.  I wrote/posted a beginners guide to ASP (it should be on my
website...http://www.wolfwares.com.  It goes into pushing data from a db, to
a website, and getting data from a web client.

A few tricks I have picked up, which aren't in those documents, but would
have been something I would have REALLY liked to have known when I was
starting out.

First, in the sample code above, if the line 'rs.MoveNext' was removed, or
never present in the first place, you will have just sent the IIS server
into an endless loop.  In VB, or VBA, you would just hit ctrl-break, and
stop the code.  However, you don't have that sort of access to the ASP
'engine'.  However, if you run 'iisreset' from a command line (or
Start-->run-->iisreset), it will 'restart' the webserver, even if it's in an
endless loop.  (Stopping the actual Web service won't do this, the stopping
process just hangs).

Next, the 'conditional' HTML I mentioned is REALLY handy.

You can use ASP to either show/hide HTML, or even repeat it.

For instance, if I wanted to display a certain note, if there were no
records, I could do this (assume the first part of the code above):

<%if rs.eof=true and rs.BOF=True%>
<h1>Sorry, No Records</h1>
<%end if%>

That HTML, will only be displayed, if the conditional statement in the ASP
let's it by, otherwise the HTML before the ASP's end if (or else, if you use
that) will be skipped.

You can also 'repeat' HTML that way, for instance:

<%if rs.eof=true and rs.BOF=True%>
<h1>Sorry, No Records</h1>
<%else%>
<table width="100%">
	<%
	rs.movefirst
	do until rs.EOF=True
	%>
	<tr>
		<td><%=rs.Fields(0).value%></td>
	</tr>
	<%
	loop
	%>
</table>
<%end if%>

Kind of handy.  Hope this helps you on your way!

Drew	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of MACE, Terry
Sent: Wednesday, February 11, 2004 5:49 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Database Operation from Remote Sites


Drew, thanks for your info so far. I would like to see the in and out of how
to create something if that is not too big an ask, but any info would be
good.

Terry Mace

-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com]
Sent: Thursday, 12 February 2004 09:53
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Database Operation from Remote Sites


By sample/demo, do you mean a complete in and out look at how to create
something, or just a working ASP interface?

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
Helmut.E.Kotsch at t-online.de
Sent: Wednesday, February 11, 2004 12:45 AM
To: Access Developers discussion and problem solving
Subject: AW: [AccessD] Database Operation from Remote Sites


Good morning,
where could I find a sample / demo for the ASP approach?

Regards Helmut Kotsch

-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von
DWUTKA at marlow.com
Gesendet: Mittwoch, 11. Februar 2004 02:43
An: accessd at databaseadvisors.com
Betreff: RE: [AccessD] Database Operation from Remote Sites


ASP is a very good approach for multi-site systems.  The real question is
how complex is the data entry?  If you are only talking about a few forms,
then ASP is the way to go.  Have Site1 host the ASP pages, with the db on
their network.  That would allow for no modifications necessary for the data
mining process.

However, if the data entry is very complex, you may want to go with a
Citrix/TS approach.  More costly to initially implement, but no real
'development' would be required.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of MACE, Terry
Sent: Tuesday, February 10, 2004 6:41 PM
To: 'AccessD at databaseadvisors.com'
Subject: [AccessD] Database Operation from Remote Sites


Hi all,

I'm after some information on the best way to proceed with the expansion of
an existing Access97 database application which is used for maintenance
activity recording.

My company currently runs this database at two sites, call them Site1 and
Site2.  The BE is changed at Site2 as a function of its operations, each
night the BE is copied to Site1 where it is used for admin and data mining
purposes with the data acknowledged as being up to 24hrs out of date.  This
has served the company well up to now as only one site can change the data.

The company now want to expand the number of sites that use and i/p data.

The new layout will have at least 4 sites.  Site1 will be the admin and data
mining centre, Site2, 3, and 4 will i/p and change data.  Sites 1, 2, and 3
are on a WAN with Site3 being the companies main site and hosting the
companies Internet gateway.  Site4 is overseas and not on the WAN.

What I would like is some idea of the best approach - I have read a bit
about replication and ASP but have no practical experience with either.

Thanks for any assistance.


Terry Mace
Logistic Support Officer & Maintenance Supervisor
BAE SYSTEMS
677 Victoria Street,
Abbotsford, VIC 3067
Ph: +61 3 9208 0924
Fax: +61 3 9208 0588
Mailto: terry.mace at baesystems.com <mailto:terry.mace at baesystems.com>


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list