Coding standards regarding executing SQL from VBA?

[bhamilton42]

Hello all

I was just curious, what's the general opinion on coding standards/efficiency when it comes to executing an SQL query from VBA?

Basically I have a subform datasheet, and the record source is going to be set as the result of a recordset populated from an SQL query. The user enters parameters and the query runs with these parameters, populating a recordset object and then that recordset is used at the record source for the subform.

So, when doing this, I could go to create and make a query and put in [] for the parameters, and then in my code i would use a query def and fill in the parameters and then use doCmd.RunSQL(name of my query, dbOpenDynaset) or, I could just save the query as a string variable in my VBA code, and then just use DoCmd.RunSQL(SQL,dbOpenDynaset)

It's not a tiny query, it's the amalgamation of 4 tables from the database (summation report for the higher ups) and there are 20 requested fields. But it's not the biggest i've run by far. So what might be he best way to have that? saved query or VBA string? what do coding standards say and what runs more efficiently?

 

[twoplustwo]

What's in the tables out of curiosity?

If I wanted to do this and display the results I would probably build the SQL in the VBA editor, passing variables from my form/user. I would then pass that SQL to a query via the QueryDef.

http://msdn.microsoft.com/en-us/library/office/bb177500(v=office.12).aspx

 

[bhamilton42]

I'm actually unable to discuss what is in the tables unfortunately.

thanks for the reply though I was just wondering what people thought would run better and look better. I think I'll go with a string variable and use the RunSQL. If I dont have to save an object in there (query) then all the better (unless you think it runs better like that). That way I dont have to worry about things getting deleted or whatnot.

Thanks Again

 

[delikedi]

I've come across frequent emphasis on the fact that queries that are created as resident objects are more efficient/faster, due to the fact that they are already compiled as opposed to query strings generated by VBA which have to be compiled at execution time.

 

[Galaxiom]

You plan has a major flaw.

RunSQL is only for action queries and won't open a recordset.
(BTW The second argument of RunSQL is UseTransaction and dbOpenDynaset is not one of its options.)

You would need to use OpenRecordset to do what you propose but I don't really see why you would do it that way. You might as well just feed the completed SQL string to the RecordSource of the form and let Access do the rest.

There is not really that much difference in performance between a stored query and a dynamic one. However if you want the best performance use a stored query with parameters appended from a form using VBA.

 

[vbaInet]

There is not really that much difference in performance between a stored query and a dynamic one. However if you want the best performance use a stored query with parameters appended from a form using VBA.

... in which case you would use the form's Filter and FilterOn properties. E.g.:

With Me.SubformControlName
    .Filter = "ID = " & Me.ID
    .FilterOn = True
End With

 

[bhamilton42]

Hey thanks guys good to know @galaxiom you're very correcy about runSQL and infact I was using open Recordset lol. I was just really tired when I wrote the question.. taking time to do that was my idea of taking a break from code writting.

As for the rest I think I will go with a saved squery and feed it parameters since the general concensus seems to think that runs faster

@vbaInet, so basically in my stored query id have my where clause like this "WHERE Field_name = parameter..." and then in my VBA ID have frmMyForm.Form.Recordset = "Query_Name" and then I'd put the filter property
"Filter = 'Parameter = ' & Me.UsersInputValue" in my vba code like that? That's basically correct?

Sounds like it should be, I'm going to try it myself soon.. I have a working knowleage of vba but not too much practise with it so things like that are kinda iffy with me

Anyways thanks all for the great replies

 

[vbaInet]

The query that is bound to your subform will contain no criteria. You pass the criteria via the Filter property and turn it on using the FilterOn property. I hope that helps.

 

[bhamilton42]

Ohhh I got it. Even better thanks much!

 

[vbaInet]

Glad we could help.

 

[mdlueck]

I was just curious, what's the general opinion on coding standards/efficiency when it comes to executing an SQL query from VBA?

Please see my post here for my input...

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

In the case that you are not accessing a SQL BE DB, my suggestions still apply...

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Always: ADO --> VBA Variables, DAO --> Access Tables

 

[Galaxiom]

The query that is bound to your subform will contain no criteria. You pass the criteria via the Filter property and turn it on using the FilterOn property.

The down side of this approach is that without any criteria the form may retrieve a very large recordset when all you wanted was a handful of records.

Opening the form with criteria in the Where argument of the OpenForm Method is another alternative.

 

[vbaInet]

Quite right Galaxiom.

Here's a link re Galaxiom's alternative:
http://baldyweb.com/wherecondition.htm

So when you're opening the form use Galaxiom's method above and if you need to perform this operation multiple times whilst the form, you can use the Filter method.

 

[Galaxiom]

So when you're opening the form use Galaxiom's method above and if you need to perform this operation multiple times whilst the form, you can use the Filter method.

One should not take it for granted that filtering is going to be a more efficient way to get the records you want from a larger recordset. Often it is faster to hit the database multiple times to get just the records that apply than to pull them all and filter locally.

I remember one application where I filtered a recordset several hundreds of times to check for matches and write to another recordset depending on the outcome. The procedure took half a minute which seemed reasonable.

Then I decided to try opening the recordset hundreds of times with just the records I wanted each time. I thought it had failed because it was finished in a few seconds. In fact it had worked perfectly.

In a practical application each alternative needs to be considered. It is often worth trying a couple of different ways.