What's faster - vba sql or query recordsource

[trevor40]

What is faster, sql code in vba to set the record source or using vba to set the record source to a query for a form?
Can I copy the query sql into vba and use that? or does it need to be modified?

[Missinglinq]

Have no idea as to the first question; I've only worked with medium size databases and have seen no difference. I suspect that you could only see a real difference if you were dealing with really large RecordSets.

As to copying the SQL from the Query and using in VBA, this kind of thing will do it:

Private Sub Form_Load()

Dim strSQL as String

  strSQL = "SELECT ActualTableName.ID, ActualTableName.Field1, ActualTableName.Field2, ActualTableName.Field3 FROM ActualTableName;"
  Me.RecordSource = strSQL

End Sub

Linq ;0)>

[drexasaurus]

I've heard that for a traditional Access database, the query objects are going to be slightly faster since optimization info is kept and saved to query the information. I don't let that dictate how I manage an application, however. Having a huge mess of queries that you're not sure what they're used for any more, and recreating the same queries for the same questions because you reflexively create a new object every time you need to come up with a solution isn't very helpful. Doing the same thing with code doesn't help either.

I think having a manageable application is more important, and whatever speed issues you do come across will most likely be solved through better design, queries, and code, not decisions like these. I'd be interested if someone had a different experience on this issue in particular though.

[ipisors]

There may be some difference but that's not the most important question brought up by this choice. the most important question by far is scalability and sustainability. Large advantage there in saved query objects. On the other hand, I do favor using code for simpler queries.....No need to bloat your database by saving 30 similar queries with a few different criteria when youcould set them all in code.

Better yet: I usually save a query object for anything that is even >= "moderately complex", then use VBA code to manipulate the .SQL property of the DAO.QueryDef object, in order to change the minor thing I want to change. Sometimes running in loops, etc.

But (for exampe, hypothetically), being in the habit putting huge amounts of VBA/SQL in your vba project when you could use one of the above methods? Mistake IMO. Almost impossible to maintain & edit. Whereas the saved query will be easy to maintain and edit, and you can still use VBA code to manipulate various aspects of it at runtime.

Even saving the sql in the memo field of a table would be better.

As you have seen by now, the answer to your question "can it be done", is yes, BTW. Check out the Helpfile's definition of a form's Recordsource.