CONVERT_ACCESS (VB script)
This example shows you the principles on how
data can be interpreted by using an extra database (Access) in your script. It
uses OLE DB4 and ADO techniques to do this.
So, this is not a standard function. What is
outlined here must be interpreted for each case.
The sample: We created a website where people
can order stuff. The order itself must be posted into your order handling
system, which we will call ERP (Enterprise Resource Planning) in this sample.
Because you want to give visibility to your customer on when delivery will
happen, you post back this information to your website. However, at the time
your customer creates his order on the site, you must give him an order
reference. That's tricky because usually it's your ERP creating those
references, and your ERP is not connected real-time with your website. Now we
need to work with a website order number and an ERP order number. How to link
The challenge in this is to find a common
primary key, which is not the order number. The primary key could be the
customer id together with purchase order number of the customer. A key that was
posted with the order, but not stored on the website.
Knowing this, the solution could be:
- Make a separate Access database
(OrderXreference.mdb) with one table:
- In your PRODBX configuration, when your
order is posted from the website, make sure the website ordernumber is also
written in WebId, the customer number in CustomerNo and the purchase order
number in PONo. Of course, at this point you don't know the ERP order.
- When the order is handled by your ERP it
posts the ERP order number, the customer number, the purchase order number
and the delivery date (which you want to publish). To convert the ERP order
number to WebId we created the following script (Providing you wrote the
purchase order number to the stack and you're currently handling the
'Build the connection with the access database
Set AdoConnection = CreateObject("ADODB.Connection")
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0"
ConnectString = ConnectString & ";Data Source="
ConnectString = ConnectString & "D:\OrderXReference.mdb"
ConnectString = ConnectString & ";Jet OLEDB:Database Locking Mode = 1"
AdoConnection.ConnectionString = ConnectString
'Make the SQL query
Set AdoRecordset = CreateObject("ADODB.Recordset")
SQL = "Select WebId From OrderXReference where CustomerNo=" & InputValue
SQL = SQL & " and PONo ='" & Stack & "'"
'Go and get the record
- When writing this last information you may
also update the ERPOrder in the Access table. With that you would be able to
find it back when the customer wants to update something, like his PO