Microsoft Access - HOW TO: Create a Parameterized Jet Stored Procedure using DDL in Acces

Asked By chaz on 26-May-11 09:01 AM

Dearly beloved:

I refer you to this:

http://support.microsoft.com/?kbid=202116

I'd love to understand exactly what's going on here, esp this part (and esp(esp(the bold part)):

Public Function RSFromParameterQuery(strCity As String)
Dim prm As ADODB.Parameter
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "qryCustByCity"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("prmCity", adVarChar, _ adParamInput, Len(strCity))
prm.Value = strCity
cmd.Parameters.Append prm

Set rst = New ADODB.Recordset
rst.Open cmd

Do Until rst.EOF
Debug.Print rst(0), rst(1), rst(2)
rst.MoveNext

Loop

End Function

...It appears that all this is a funky way of creating an SQL statement? Wow, that's a lot of work.

Standard ADO and ADO.NET

Robbe Morris replied to chaz on 26-May-11 09:36 AM

qryCustByCity is expecting an input parameter of prmCity to be populated with some value. Assuming qryCustByCity doesn't use dynamic sql (I don't "think" stored queries in Access can do this anyway), this tactic is a best practice because (one of many reasons) it protects the application from SQL Injection attacks.

I suspect that if you are surprised by this tactic and think it is a lot of work, your previous applications probably concatenated sql strings and then executed them. If so, there are major security holes in every application you've written using this tactic.

chaz replied to Robbe Morris on 26-May-11 10:08 AM

this tactic is a best practice because (one of many reasons) it protects the application from SQL Injection attacks.

Very interesting.

I suspect that if you are surprised by this tactic and think it is a lot of work, your previous applications probably concatenated sql strings and then executed them. If so, there are major security holes in every application you've written using this tactic.

No, but I appreciate the concern. It's a lot of work because you're writing a command to write a command. My background is OOP -- VBA and C++, and the SQL Studio. We didn't do alot of that.

Robbe Morris replied to chaz on 26-May-11 10:14 AM

It is just a formalized OOP way to piecing together parameter, parameter types, and their values so the underlying providers, where they be ODBC, OLEDB, or the native SQL Server providers can properly execute their commands to the database.

DAO works like this and so does ADO.NET (regardless of the underlying database).

chaz replied to Robbe Morris on 26-May-11 10:24 AM

Speaking of "best practices," is there one for making orderly a pile of dependent queries?

Hmm, in Access and classic ADO

Robbe Morris replied to chaz on 26-May-11 10:31 AM

I don't recall robust support for transaction commits and rollbacks with Access. I may not be the best one to answer that. My last real world VB 6.0 and Access app was 11 years ago and I don't have VB 6.0 installed anymore to go test it out. Sorry :)

chaz replied to Robbe Morris on 26-May-11 11:42 AM

Just want to say I think this website is a great idea. Adding the financial incentive changes the entire tone of such a forum, upgrades the quality of the information, and makes for an interesting "new economy" model.

Pat Hartman replied to chaz on 27-May-11 02:57 PM

ADO is a little more complicated in some cases than DAO. With Access, you can use either. If your tables are Jet/ACE, it is best to use DAO, especially with older versions of Access. With A2007, ACE was introduced as a replacement for jet. The SQL Server team maintains Jet but now the Access team maintains ACE so there is more of a DAO slant again. If you are using SQL Server tables and you are worried about performance, you can time the two procedures to see which opens the recordset quicker. Here's a simple DAO example with two parameters:

Dim qdFields As DAO.QueryDef

Dim rsFields As DAO.Recordset

Dim db as DAO.Database

Set db = CurrentDb()

Set qdFields = db.QueryDefs!qFetchFieldNames

qdFields.Parameters![EnterDataSourceName] = "tblAuditParms"

qdFields.Parameters![EnterDocID] = DocumentID

Set rsFields = qdFields.OpenRecordset(dbOpenDynaset, dbSeeChanges)

The dbSeeChanges argument is required (SQL 2005 and newer) whenever you open a recordset from a table with an identity column primary key.

Pat Hartman replied to Robbe Morris on 27-May-11 04:09 PM

Hi Robbe,

Stored queries (querydefs) in Access are static SQL but they can take parameters. they are not subject to SQL injection attacks because they are bound and would fail if SQL was injected. Dynamic SQL can only be built via SQL strings in VBA. They would be subject to SQL injection attacks if the programmer didn't validate the parameters.

I couldn't remember if transactions could be used with linked ODBC tables or if they could control both ACE and SQL server updates so I built a little 2007 test db with the following code. One table is linked to SQL Server and one is linked to ACE and the rollback rolled back both updates. So, if the tables are linked, they are within the scope of the DAO.Workspace and can be controlled by a single transaction.

Sub testTran()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim rs1 As DAO.Recordset

Dim td As DAO.TableDef

Dim td1 As DAO.TableDef

Dim wrkCurrent As DAO.Workspace

Set db = CurrentDb

Set wrkCurrent = DBEngine.Workspaces(0)

wrkCurrent.BeginTrans

Set td = db.TableDefs("dbo_tblRoles")

Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)

rs.AddNew

rs!RoleName = "newname3"

rs!UpdatedBy = "Patsky"

rs.Update

Set td1 = db.TableDefs("tblType")

Set rs1 = td1.OpenRecordset(dbOpenDynaset, dbSeeChanges)

rs1.AddNew

rs1!Type = "newtype3"

rs1.Update

wrkCurrent.Rollback

End Sub

Pat

Excellent follow up post

Robbe Morris replied to Pat Hartman on 27-May-11 04:31 PM

Yeah, I didn't think Access stored queries supported dynamic sql but wasn't sure.

Good follow up info on transactions!