Parameterized SQL / Transactional SQL in VB / Access

I have got to get out of the legacy world and start posting something more current. I’m currently working for a client that has an enormous amount of data and code invested in MS Access and I have the go ahead to convert it to .Net… But. But I have to continue to support and debug the current system at the same time. It will be a while, but I’ll have this thing in tip top shape yet.

So, if you’re a poor sap like me and have to maintain some legacy code you may find this tidbit useful. How to write parameterized SQL in VB / Access code and I added transaction support to boot.

Private Sub DoBatch(ByRef rs As ADODB.Recordset)
Dim cn As ADODB.Connection
Dim cmd As New ADODB.Command

Set cn = CurrentProject.AccessConnection
Call cn.BeginTrans

Dim sCMD As String
sCMD = “update t_TableName set Var1=@inVar1, Var2=@inVar2, Var3=@inVar3, ” & _
“Var4=@inVar4 Where Var5=@inVar5”
Do While Not rs.EOF
With cmd
.ActiveConnection = cn
.CommandText = sCMD
Call .Parameters.Append(CreateParameter(“@inVar1”, nz(rs.Fields(“Var1”), 0), 4, adNumeric))
Call .Parameters.Append(CreateParameter(“@inVar2”, nz(rs.Fields(“Var2”), 0), 4, adNumeric))
Call .Parameters.Append(CreateParameter(“@inVar3”, nz(rs.Fields(“Var3”), 0), 4, adNumeric))
Call .Parameters.Append(CreateParameter(“@inVar4”, nz(rs.Fields(“Var4”), 0), 50, adVarChar))
Call .Parameters.Append(CreateParameter(“@inVar5”, nz(rs.Fields(“Var5”), 0), 4, adNumeric))
Call .Execute()
End With
Call rs.MoveNext
Loop

Call cn.CommitTrans

End Sub

Private Function CreateParameter(ByVal Name As String, ByVal value As Variant, ByVal Size As ADO_LONGPTR, ByVal theType As ADODB.DataTypeEnum, Optional ByVal direction As ParameterDirectionEnum = dbParamInput) As ADODB.Parameter
Dim tempP As New ADODB.Parameter
tempP.Name = Name
tempP.value = value
tempP.direction = adParamInput
tempP.Size = Size
tempP.Type = theType
Set CreateParameter = tempP
End Function

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: