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:
mytable_uno [integer/identity field]
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.
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.