Best syntax? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Best syntax?

Author

Topic

NJSchell
Starting Member

1 Post

Posted - 2006-03-06 : 15:01:40

I'm in the process of taking over another developer's access app. More than one programmer worked on it so the syntax varies. I was wondering what's the most efficient syntax to use for SQL in a module.
Here are some examples of what's being used currently:

strSQL = "Delete * From tblTest"
DoCmd.RunSQL strSQL

RunQry ("qryTest")

db.Execute ("Delete * From tblTest...")

I was taught to use:

strSQL = "Select * From tblTest"
Set rstTest = db.OpenRecordset(strSQL), dbOpenSnapshot

strSQL = "Update tblTest...."
db.OpenRecordset.(strSQL), dbSeeChanges

db.Execute(strSQL), dbSeeChanges

TIA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-06 : 15:38:22

quote:

Originally posted by NJSchell

strSQL = "Delete * From tblTest"
DoCmd.RunSQL strSQL

RunQry ("qryTest")

db.Execute ("Delete * From tblTest...")

Keep in mind that none of those 3 examples are equivalent. the first runs in the context of the Access application and will invoke warnings using the UI, prompt for parameters, retrieve values from loaded forms, and so on. the second is calling a custom function that might do anything. The 3rd is running in the context of the JET database alone and will not interact with the UI or the Access application.

But either way, it's what you feel most comfortable with. I try to stay away from in-line SQL and use saved queries as much as possible in Access. Declaring and assigning a variable to use in 1 single statement is also something I do not normally do.

In terms of efficiency, the bottle neck will be the database engine itself 99% of the time so the syntax of your statements won't be a factor.

In general, of course, avoid opening up recordsets to process rows individually whenver you can (i.e., the jet equivalent of declaring a cursor). It will be much faster to use standard set-based SQL statements to process rows all at once.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources