How to Pass Access Data Across the Web

The solution described below should probably not be considered a
"best practice" but it works.  It came to life as I
was pondering the following conundrum:

How can I pass a little bit of data from an internal Access application
behind a firewall out to a public web site in real time?

In this case, the internal application maintained product orders.  As
an order matures, it passes through various stages:  quote to
confirmed to production to invoiced to … you get the idea.  Some
customers had many orders and they would call several times a day to check on
them.  "Wouldn’t it be great," my manager said, "if our
customers could get their order status information from our web
page."  The problem was that we did not host our own web site
internally, so there was no way to "link" the web to our internal
order system, written in Microsoft Access.

Well, I thought about it for a while and decided to create a small, mirrored
version of the database out at the web site.  All that would be needed were
the customer, order and status table, and not even all the fields for
each.  Customers were interested only in seeing the status of their
current orders, so the output table would consist of only a half dozen
fields.  That should be easy to pass in an ASP QueryString, right?

Right!  It is easy to pass and it works like a charm, but how does one
navigate to a web page from VBA code in an Access database?  Ahhhh
… that’s the real trick.  Read on.

The ASP QueryString

The QueryString object is a
very simple mechanism for passing data to an ASP web page.  You
simply append parameters and their values after the page address followed by a
question mark (?), delimited by the ampersand character(&), like this:

    process.asp?CustomerID=1234&CustomerName=ABC Limited&OrderDate=6/15/2005

The page,  process.asp in this case,  will parse everything that
follows the question mark and split it into the following:

Customer ID … with a value of 1234
CustomerName … with a value of ABC Limited
OrderDate … with a value of 6/15/2005

The example in the screen shot below shows
the URL that was used to generate the output page.  We see that the ID in
the URL is 35 and the page is reflecting that value, along with all the other
parameters that appear in the QueryString.  The ASP code to create this
page, which is neither trivial nor difficult, is displayed below.

What’s basically happening in the code below is the following:

1. Parameters from the QueryString are loaded into local
   variables.
2. Database is searched for an existing record
1. If not found, a record is inserted
2. If found, the existing record is updated
3. For confirmation, the record is accessed again and output
   to the screen in a table.

<%@ Language=VBScript %>
<% Option Explicit    %>
<%
Dim sConn, sPath, objCnn, sSQL, objRst, sOut
Dim sID, sCID, sSID, sSDate, sPoNum, sCust, sStatus
‘ Get path to database and create connect string
sPath =  Replace(LCase(Server.MapPath(“process.asp”)),”process.asp”,”remote.mdb”)
sConn = “Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=” & sPath
‘ Collect new values from querystring that will be used for INSERT or UPDATE
sID     = Request.QueryString(“ID”)
sCID    = Request.QueryString(“CID”)
sSID    = Request.QueryString(“SID”)
sSDate  = Request.QueryString(“SDate”)
sPoNum  = Request.QueryString(“PoNum”)
Response.Write(“<h3><font color=black>Welcome To The Order Prodess Page</font></h3>”)
‘ Open database connection
Set objCnn = server.createobject(“adodb.connection”)
Set objRst = server.CreateObject(“adodb.recordset”)
objCnn.open(sConn)
‘ Only perform INSERT/UPDATE if OrderID exists
If CLng(sID) > 0  Then
   ‘ Load the record:
   sSQL = ” SELECT COUNT(*) As OrderExists FROM qryOrderInfo WHERE OrderID=” & sID
   Set objRst = objCnn.Execute(sSQL)
   If objRst.Fields(“OrderExists”) = 0 Then
   ‘ No record Exists.  Insert new one.
      sSQL = “INSERT INTO tblOrder ([OrderID], [CustomerID], [StatusID], [StatusDate], [PONumber])” & _
             ” VALUES(” & sID & “,'” & sCID & “‘,” & sSID & “,#” & sSDate & “#,'” & sPoNum & “‘)”
      objCnn.Execute sSQL
      Response.Write(“<h3><font color=darkgreen>Inserted new order … ID=” & sID & “</font></h3>”)
   Else
      ‘ Record exists, update
      sSQL =  “UPDATE tblOrder SET [CustomerID]=” & sCID & “, [StatusID]=” & sSID & _
              “, [StatusDate]=#” & sSDate & “#, [PONumber]='” & sPoNum & “‘ ” & _
              “WHERE [OrderID]=” & sID
      objCnn.Execute sSQL
      Response.Write(“<h3><font color=darkred>Updated existing order … ID=” & sID & “</font></h3>”)
   End If
Else
   Response.Write(“<h3><font color=navy>Nothing to do … no order ID passed.</font></h3>”)
End If
‘ Now, go look up the updated record and output the field values
sSQL = ” SELECT * FROM qryOrderInfo WHERE OrderID=” & sID
Set objRst = objCnn.Execute(sSQL)
sOut = “<table border=1 cellspacing=0 cellpadding=0 ID=”Table1″>”
sOut = sOut & “<tr><td colspan=4 align=center><h3>Edited Order</h3></td><tr>”
sOut = sOut & “<tr><th>Customer</th><th>Status</th><th>Status Date</th><th>PO Number</th><tr>”
If Not objRst.BOF and Not objRst.EOF Then
   sCust   = objRst.Fields(“Customer”)
   sStatus = objRst.Fields(“Status”)
   sSDate  = objRst.Fields(“StatusDate”)
   sPoNum  = objRst.Fields(“PONumber”) 
   sOut = sOut & “<tr><td>” & sCust & “</td><td>” & sStatus & “</td>”
   sOut = sOut & “<td>” & sSDate & “</td><td>” & sPoNum & “</td><tr>”
End If
sOut = sOut & “</table>”
Response.Write sOut
Set objRst = Nothing
Set objCnn = Nothing
%>

Well, that’s it for the receiving part.  Now on to the trick … how to
send the data from VBA in Microsoft Access.