QueryDefs - is it a minefield (1 Viewer)

[SmallTime]

Just looking for some reassurance here.
I’ve been brushing out cobwebs in an old database which was getting quite full with queries, so I’m rebuilding building many of them on the fly as and when needed with QueryDefs, I recon I could replace many of the 180 or so queries with about 5 temp queries.

All’s looking good so far (albeit I haven’t yet tested in runtime). Since this is my first real attempt at playing with QueryDefs is there any good reason why I shouldn’t be using temp queries, I suppose what I’m looking for is a heads-up of any potential problems I might face later if I carry on along this un-trodden path (for me anyway).
Your thoughts.

SmallTime

 

[vbaInet]

Are you talking about working with the QueryDef in code? And what do you mean by temp queries?

 

[SmallTime]

Sorry for the lack of info, thought I'd make it clear but reading back maybe not.

Rather than have 180 or so static queries (queries that have been previously created)

For example, let's say this is one of many a queries in my Db and permanently exists as QryUserLogOns

SELECT TblEmployees.ScreenName, TblEmployees.EmpPassword
FROM TblEmployees;

I'm playing with getting rid of only creating it as and when needed

Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Set db = CurrentDb

Set qdef = db.QueryDefs("TEMPQuery1")
    
qdef.SQL = "SELECT TblEmployees.ScreenName, TblEmployees.EmpPassword " & _
"FROM TblEmployees;"

qdef.Close
db.Close
Set db = Nothing
Set qdef = Nothing

I could do this with many of my existing queries and just redefine TEMPQuery1 each time reducing the clutter in the DB.

(I know I could dispense with TEMPQuery1 as well and just create this too as and when needed)

So, QryUserLogOns is what I referred to as a static query and has a permanent structure and name. and TEMPQuery1 as a temporary query who's structure is constantly being redefined.

Hope that makes things a little clearer.

SmallTime

 

[vbaInet]

To be honest I would rather have 180 precompiled queries than have to write down 180 SQL statements in the VBA editor. Meaning for each SQL staement you will have to write it as constants in VBA and if there are any modifications you need to re-rewrite it too.

Queries are "precompiled" and this helps to speed up data retrieval. Whereas, each time you amend a querydef it needs to go through this process of compilation before it's run.

 

[jdraw]

Further to vbaInet's comments, what exactly is the "issue" that you are trying to resolve?

Compiled queries are certainly useful.
Maybe, just maybe, you need a few forms with some selectable fields/values that can "build a tailored query" fitting specific conditions/circumstances and be executed via a button.

It isn't clear to me WHAT you are trying to solve.

 

[spikepl]

And as vbaIntet and jdraw ask: what are you solving? Touching working code alone for the purpose of beautification costs work. And afterwards, the software works just as did before ...

 

[SmallTime]

You're all absolutely correct in that the end product is no better than before, except perhaps that since accdb's\accde's are no longer secure, (not that mdb's\mde's were very secure before) but an mde was a degree or two harder for the casual user to mess with than and accde. Anyone can import tables and queries from an accde into a new accdb but not forms, modules, reports or macros.

Now if I don't want anyone 'messing' with my work, I might, decide not to have pre-compiled queries. The client can still import tables but that's OK because it's their data, but the carefully crafted queries to manipulate the data is partly what I get paid for, so I might not want to leave them exposed, especially in organisations that have many many users.

I haven't quite sunk to this stage of paranoia yet, but am practising just in case I do one day slip into the darkness. I had a couple of free days so decided play a little and quite got used to the idea. Doesn't actually take that much longer to write the query as I normally write code in Wordpad anyway and then past it into access.

Smalltime

 

[SmallTime]

Sorry forgot to acknowledge vbaInet's very good point about speed. I was wondering how much I'd be sacrificing by creating new queries at runtime and I don't have the answer. Could it really take much longer or is the extra overhead measured in milliseconds?

 

[Galaxiom]

And afterwards, the software works just as did before ...

If you are lucky.

 

[SmallTime]

Hi Galaxiom,

How you keeping?

I think the overwhelming answer is 'don't be silly stay with what you got'.

Heeded.

Many thanks to all
SmallTime