Microsoft Access, ADO, and Passing Parameters to a SQL Server

There are times you want to be able to run SQL Server commands – T-SQL Commands – with parameters from MS Access without necessarily having a stored procedure.

Unfortunately, either through ignorance or some issue with the underlying technology, I was struggling with this recently. I’ll give you the solution first and back story later. While I was attempting to “Append” parameters to an ADO command object, this was NOT working. No errors, just NULL data in the fields I was attempting to update. Maybe you’ve run into the same thing. Null values in your SQL database when setting parameters using the parameters.append method of the command object.

Instead, I passed parameters successfully with the execute method of the command object. Here is what it looks like.

Consider the following table and fields:

Table: tblMyTable
mytable_uno  [integer/identity field]
fldName [varchar(50)]
fldAddress [varchar(100)]

The Code that works:


Dim cn as ADODB.Connection, rs as ADODB.Recordset, cm as ADODB.Command
 Dim strSQL as String, strName as String

strName = “Smith’s Plumbing”
 intUniqueKey = 12

Set cn = New ADODB.Connection
 cn.Open ConnStr()  ‘I have a function ConnStr with my ADO connection string
 strSQL = “Update tblMyTable Set fldName = ? Where mytable_uno = ?”  ‘note the question marks.

Set cm = New ADODB.Command
 cm.CommandType = adCmdText
 cm.CommandText = strSQLupd

‘create an array of the values to replace the question makes in the same order as they appear in the SQL statement.
 aryParms = Array(strName, intUniqueKey)

‘run the execute command and the second parameter of the method is the array created above.
 Set rs = cm.Execute(,aryParms)
 Set rs = Nothing
 Set cm = Nothing
 Set cn = Nothing

And end your recordset, command, and connection objects. Voila! Done.

Why Parameters

This isn’t a web app. It is desktop Access app that is run inside a client, so SQL Injection is not the issue here. Quite simply, it had more to do with apostrophe’s inside a varchar field. The possibility of the  ‘ made building a SQL update query unfeasible.

Using the same table and fields as above.

Now this code:


strName = “Matt”
 intUniqueKey = 10  ‘this assumes the record I want to update’s key (identity integer) field is equal to 10
 strSQL = “Update tblMyTable Set fldName = ‘“ & strName & “’ WHERE mytable_uno = “ & intUniqueKey
<h3>'When concatenated, this produces:</h3>
Update tblMyTable Set fldName = ‘Matt’ WHERE mytable_uno = 10

And that’s cool. It works, no problem.

But what about:


strName = “Smith’s Plumbing ”
 intUniqueKey = 12  ‘this assumes the record I want to update’s key (identity integer) field is equal to 12
 strSQL = “Update tblMyTable Set fldName = ‘“ & strName & “’ WHERE mytable_uno = “ & intUniqueKey
<h3>'When concatenated, this produces:</h3>
Update tblMyTable Set fldName = ‘Smith’s Plumbing’ WHERE mytable_uno = 12

This is a problem, because the apostrophe in “Smith’s” makes SQL Server terminate the string field, leaving it to try to understand, “s Plumbing’” – which is not a command. And so you get an error.

I tried using the parameters using the command.parameters.append and command.createparameters methods but it always resulted in null values in my database. Passing the parameters on the execute method was the answer for me.

This is simple but it’s come up enough online that I figured I would blog about it.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>