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:
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:
- Parameters from the QueryString are loaded into local variables.
- Database is searched for an existing record
- If not found, a record is inserted
- If found, the existing record is updated
- 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.