How to Pass Access Data Across the Web - Page 2

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.

» See All Articles by Columnist Danny J. Lesandrini