OpenRecordset vs. Querydef (Database speed/bloat)

[IanGlinka]

Howdy group,

I've gotten into the habit of reading / writing records in the tables in my database through the use of VBA modules and the OpenRecordset command with an SQL string passed into the command.

I've been experiencing significant issues with database bloat and some of the processes in this program are taking very long times to run.

From my initial research, I think I might need to switch to using querydefs and actual tables / saved queries, rather than dynamic SQL with OpenRecordset.

Just wanted to confirm that I'm thinking in the right direction to reduce bloat and increase speed.

Thanks,
Ian

 

[WizDumb]

When you say bloat, are you worried about how many MS Access Queries you have saved in your Database?

As for speed... are you using Access tables or Linked SQL tables?

-Wiz

 

[RoyVidar]

Some people voice the opinion that dynamic SQL is a cause of bloat, but some of the top of the top Access developers/community participants are more or less only using dynamic SQL and no stored queries - and have neither bloat nor problems with efficiency...

Bloat is probably coming from other design problems (temp tables, pictures, inefficient design...)

And say you store/compile your queries with only some smallish test data, what benefit will you have of that when the number of records in each table has grown (and perhaps not grown proportional)?

Most likely, dynamic SQL will be faster ;-)

Now, to address these issues, I'd suggest posting in one of the Access fora, and perhaps some details of the process creating the bloat/inefficiency.

Roy-Vidar