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:

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.

Send Data to URL

The majority of the VBA code performs the string concatenation, forming the actual URL that will be hit.  The code is shown below and should be pretty straightforward to understand.  Start out with a constant that identifies the base URL for the page and add parameters and their values.  Once the URL is complete, it takes only one line of code to launch it, the one highlighted in yellow below.  However, there are a couple of tricks to make this work.

Public Function 
  UpdateOrderInfoWebSite() As Long
On Error Resume Next  
   

   Dim objDoc As SHDocVw.InternetExplorer
   Dim sURL As String
   Dim lOrderID As Long
   
   lOrderID = Forms!frmOrder!txtOrderID

   Const cURL As String = 
     "http://localhost/Orders/Process.asp"
   sURL = cURL & "?ID=" & lOrderID
   sURL = sURL & "&SDate = 
     " & Forms!frmOrder!txtStatusDate
   sURL = sURL & "&SID   =   
     "  & Forms!frmOrder!cboStatusID 
   sURL = sURL & "&PONum = 
     "  & Forms!frmOrder!txtPONumber 
   sURL = sURL & "&CID   = 
     " & Forms!frmOrder!cboCustomerID
   
   Set  objDoc = New SHDocVw.InternetExplorer
   objDoc.Navigate sURL, , , True

   ' Need something to pause execution 
     while URL is hit.
   Dim i As Integer, j As Integer
   For i = 0 To 1000
      j = DCount("*", "MsysObjects")
   Next

   UpdateOrderInfoWebSite = Err.Number
    
End Function

Notice above that objDoc was declared as type   SHDocVw.InternetExplorer?  This declaration will fail to compile unless you set a reference to the Microsoft Internet Controls.  Open any module and select References from the Tools menu.  You will see the screen below.  Scroll down the list until you find the Internet Controls reference and select it.  Click OK to save your selection.
 

The other trick is not so clear, and quite frankly, I do not have a great solution for it, or even a great explanation for why it exists.  It seems that the process needs to pause for a few seconds to let the Navigate to URL command work before the object goes out of scope.  There are some great API calls that will pause execution, but in this case I just elected to execute the DCount() function a thousand times.  That is enough of a pause to allow the process to complete and it does not require any complex explanations of API declares.

You should be able to copy and paste the above code into any VBA module and run it, so long as you set the reference mentioned above.  All the code is available in this month's download, but if you try to test it, you will need to set up the remote.mdb and asp pages on a web server.  The Local.mdb has a form for testing the process and can be run from any computer with access to your web page.  In this case, I have set the AfterUpdate Event property for all the controls to = UpdateOrderInfoWebSite() which calls the above code.  Each time something is updated, our web database will, in short order, become synchronized with it.

What Could Go Wrong? 

As I mentioned, this works, but it is not necessarily a great idea.  Just imagine the local network traffic it would generate if you had 100 employees editing records all day long.  We ran into these kinds of problems at first, but decided that, with a little better planning, we could reduce the number of calls to the UpdateOrderInfoWebSite() function.  We continued to use the method, but reduced the number of times it was called by sending an update only after a record was saved as a whole, not each time someone changed a single control, as is the case with this demo application.

There are other issues to consider as well.  What happens when you add new Customers?  New Statuses?  The same method can be replicated to update all the tables in this remote database, but again, all this adds overhead.  As you can see, the process could mushroom to the point where you are replicating the entire database through QueryString variables to an offsite database.  Yes, this solution works, but is not very scalable.

On the other hand, it is a pretty nifty trick for dumping small pieces of data through your firewall to a web site.  As usual, if anyone has a better solution, I would love to hear it.