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.
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 =
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")
UpdateOrderInfoWebSite = Err.Number
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