SQL in code versus created queries..... which is better?

[GComyn]

I've been having some problems with a couple of my databases. The database that has been giving me the most problems. has about 60 or so built queries in it, and sometimes, when I compact it, the entries in the MSysObjects table shows them as OLE, instead of queries, so it gives me an error.....

I rewrote the database, putting the SQL in the modules of my forms where they were called.

I was wondering if that was the best thing I could do.

Now, the queries are only called from one location, and that is where I put the SQL.

For the reports, I put the sql in the recordsource, instead of using a query.

I know, this is harder to change/debug than using built queries, but is it larger?

If someone could show me where to find information on this on the web (I've been looking, but can't find anything) I'd appreciate it.

GComyn

 

[Steve101]

(a) The idea of having queries in the queries window, is that it allows Access to pre-optimise them, which is supposed to make them quicker to run.

(b) Additionally, it provides a 'native' environment where a query can be defined once, and then used repeatably, as required. This is good, especially when the query has parameters associated with it, which can be varied at runtime.

(c) The problems you are having re errors when you compact the database are indicative of some form of database corruption. What I'd suggest here is that you create a brand new database, and then import the database objects into it. This will hopefully fix the problem. There may be some additional complexities here though, if you have to reconstruct the underlying relationships model and/or re-create the module references required by the program. Your decision to use queries or not should NOT be based on the fact that your database has some form of corruption in it. You need to fix this problem first, and as an independent exercise.

(d) Nothing wrong with 60 queries in the queries window, but they should be named with some form of naming convention to ensure that they are grouped together in a sensible way, and rationalised, so that those that are the same, except for some selection criteria, are combined into a single query, which uses parameters at runtime to control the selection OR uses report/form filters to control the selection. What I'm saying is that you may be able to reduce your 60 queries to somewhat less.

(e) I often also embed SQL in code, especially if its 'one off', and not likely to be used in other parts of the program. This is sometimes frowned upon, but it does give enormous flexibility, especially when combined with a form interface which allows a user to control the selection fields and/or order and/or grouping of the form/report that you may be building.

(f) You can of course use code to build query definitions into the query window. See "QueryDefs" online help for more info on the querydef "collection".

(g) Finally, there are no wrong or right answers to your question (although there are many purists who will argue one way or another). Stick with good naming conventions, and always make sure that your code is well commented. When you look at the objects listed in each database window, they should be easy to understand almost at face value, and should flow in some form of chronological order.

I hope that this helps.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au