Updating a website from Access
May 21, 2008
A while back a client wanted to be able to update stock levels from their back office system which was an Access Database. Recently I was asked about this so I'd thought I'd share the concept.
With a mix of some Visual Basic code and a few DLLs and a CFC you can pass a WDDX packet from Access to a ColdFusion webservice. You will need to register some DLLs (wddx_com.dll, ASPtear.dll, xmlparse.dll, xmltok.dll) on the clients PC to get it to work.
This is the VBA code you need to add to your Access database.
Option Compare Database
Public Sub SendToWeb()
' ====================================================================
' required DLLs in System32 directory
' wddx_com.dll, ASPtear.dll, xmlparse.dll, xmltok.dll
'
' must do
' regsvr32 c:\windows\system32\wddx_com.dll
' regsvr32 c:\windows\system32\ASPTear.dll
'
' Module requires tools->reference->wddx_com
' ====================================================================
Const Request_POST = 1
Dim wddxSerializer As wddxSerializer ' Adobe/Allaire's WDDX serializer
Dim wddxRs As WDDXRecordset ' Adobe/Allaire's WDDX recordset
Dim swAspTear As Object, strRetval As String ' Softwing's ASPTear
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strScript As String
Dim strSQL As String
Dim strResponse, strPostData
Dim CurrentRow As Long
strScript = "http://www.mydomain.com/com/webservice.cfc?wsdl&method=ProductUpdate"
strSQL = " select product_id, product_instock from [Products] "
' Create instance of WDDX.Serializer, Recordset and ASPTear object
Set wddxRs = CreateObject("WDDX.Recordset.1")
Set wddxSerializer = New wddxSerializer
Set swAspTear = CreateObject("SOFTWING.ASPtear")
Set cnn = CurrentProject.Connection
rst.ActiveConnection = cnn
rst.CursorLocation = adUseClient
rst.Open strSQL
' create column headers
For Each x In rst.Fields
wddxRs.addColumn (x.Name)
Next
CurrentRow = 0
Do Until rst.EOF
wddxRs.addRows (1)
CurrentRow = CurrentRow + 1
For Each x In rst.Fields
wddxRs.setField CurrentRow, x.Name, x.Value
Next
rst.MoveNext
Loop
' Serialize it into a WDDX Packet
wddxPacket = wddxSerializer.serialize(wddxRs)
' MsgBox (wddxPacket)
strPostData = "WDDXPacket=" & wddxPacket
strResponse = swAspTear.Retrieve(strScript, Request_POST, strPostData, "", "")
' MsgBox (strResponse)
End Sub
An example of handling the WDDX in your CFC
<cfcomponent>
<cfset this.Datasource = "mydsn" />
<cffunction name="ProductUpdate" access="remote">
<!--- data passed as wddx packet so convert to query for easy handling... --->
<cfwddx action="wddx2cfml" input="#form.wddxpacket#" output="qryWddx" />
<cfloop query="qryWddx">
<cfquery name="qryUpdateOption" datasource="#this.Datasource#">
Update [Products] set
product_instock = <cfqueryparam value="#qryWddx.product_instock#" cfsqltype="cf_sql_integer" />
where product_id = <cfqueryparam value="#qryValid.product_id#" cfsqltype="cf_sql_integer" />
</cfquery>
</cfloop>
</cffunction>
</cfcomponent>
You will need to add in some validation and error trapping. It's also worth sending back a response to Access. This is worthwhile as you could let Access know if the update was successful or how many rows were updated etc. I'm just keeping it simple for the purposes of this post.
- Posted in:
- ColdFusion
No comments
Leave a comment
If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)