VBA or Queries ??????

[MossleyMike]

In a number of the database applications I've written I tend to "mix and match", using both vba code and ordinary queries.

What does the team think is the most efficient way of writing applications - vba or queries or a mix ??? Which is more efficient ?? Are there any space considerations ??

Don't know why it's took me so long to ask this - it's just that I've never seen any sort of tips/advice to go one way or the other

I do remember back at Access 95 someone saying "avoid macros" - yet they're still around at 2003 !!

 

[Mile-O]

Well, seeing as VBA and queries are totally different then I'd have to say a mix.

If you are using VBA to build queries then:

• Is the query static when you code it? i.e. Does it change any way depenant upon circumstance? If not then just create the query.

 

[MossleyMike]

VBA or Queries

I think the point I was trying to get over was which is better - a query as in:

"Select A,B,C from Table_Z"

or a bit of vba as in:

Dim dbs as Database
Dim rst as RecordSet
Set dbs = Currentdb

Dim strSQL
strSQL = "Select A,B,C from Table_Z"
Set rst = dbs.OpenRecordSet(strSQL) etc. etc.

I guess the second way gives more flexibility if you were to be amending/updating the recordset, but the first way seems simpler even if I end up with nearly 100+ queries under the query tab !!

 

[Mile-O]

Okay, having a query - if possible - is definitely better. Every time you create a query through code it contributes to database bloat. Having a defined query is already created and can be run any time - it has been created once and is stored, it will not contribute to database bloat. A coded query will.

 

[Mile-O]

The DAO method above with that query would be better as a defined query and then the DAO to access the query.

ie..

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryMyQuery")

 

[MossleyMike]

VBA or Queries

Thanks for the advice ...

 

[Pat Hartman]

Here is a reference to one of my posts regarding static vs dynamic SQL. There are others if you care to search for them.
Static vs Dynamic SQL