Make Table vs. Delete and Append Queries

Good morning Experts,

I have five pass-through queries that will create five tables for a total of about half a million records. This process is to audit a customers account and will need to be repeated on a daily basis to monitor account activity and track any changes.
The last time I did something like this I used make table queries to call the pass-through queries. The process took several hours to run.
I was wondering if instead of running make table queries every day I run them just once to create the tables in my Access database. Then each day run delete and append queries to update the account information.
Do you think this will help the overall process run faster?
I need the most efficient way to get all the data locally so a team can work the account. I am trying to cut down on the run time as much as possible.
Any other suggestions are greatly appreciated.

Thank you,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi TylerDerden,
Are you saying that a single account requires 500k records?
Or is this the total for all accounts?

If it's all accounts then you should change tack and just pull down data for each customer as you require it.

I don't see how a delete& append would be faster than a make table but I don't know for sure.

The delete/append will certainly be slower as executing a maketable query.
When you want to speed up de deletion the "DROP TABLE" will get rid of all data in one go, a "delete *" will have to process all rows :-(

Most efficient way will  be to use linked tables and run your queries on that or to use passthrough queries with a "heavy" WHERE clause to filter out as many rows as possible.

The "turn around" time will depend on the use of the table you need.
Multiple queries on the same data will indeed speedup the process when having the tables internally, but two or three simple queries can be faster to execute directly as passthrough query.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TylerDerdenAuthor Commented:
To answer you question, yes one customer and about 500k records. Don’t ask. Lets just say it a very large customer and there are many attributes I need to capture about the account.


So just to summarize, you suggests the five pass- through queries called by five make table queries to be run each day.
This would be more efficient than running five delete queries then five append queries, correct?
Yes, I am make extensive use of the Where clause.

Any other suggestions or do you think we’re good to go?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

I would also Compact and Repair the database at the beginning of each Day, since Access does NOT do a particualrly good job of recovering 'deleted' space when any Access object (record, Table, Form, Report, Query, Module) is 'deleted' - the object is marked as 'deleted' and is thus no longer accessible, but the space is still used, and is not truly recovered until the next time you execute a Compact and Repair operation.

TylerDerdenAuthor Commented:

Good point on the compact and repair. Thank you. The Access database is on a file server. Once I get the process of pulling all the data in I will set up queries for the team to use so they can work the account.
I’ve been told not to use compact and repair when Access is not on my local C drive. Do you agree with this?
Leigh PurvisDatabase DeveloperCommented:
What do you use the data for?
If it's anything that can hold a lock on the table (mailmerges can do this when they go even slightly off course) then your maketables would fail (as the original table can't first be deleted which it needs to be - whereas DELETE and INSERT statements wouldn't).

Just worth bearing in mind - the eventual aims.
(Although I dread to think what the tongue of the poor person would look like if they have to lick 500,000 envelopes ;-)

(OK - they have self sticky ones now - and you also very likely aren't mailmerging.  An open recordset will do the same).
TylerDerdenAuthor Commented:

I am extracting customer information from a corporate data warehouse. That part is accomplished by the pass-through and make table queries.
Once that is done then I will set up some select queries on those tables so the end users can do their work. Nothing to fancy.
No mail merge and no envelopes, thank heavens.

Am I to take it that you do not think compact and repair on an MS Access database is not a problem when it is on a shared drive not my local drive?
If a compact does not complete fully it just leaves the original file unchanged.  You might then get dbx.mdb files where x = 1/2/3 etc  which were created during the compact but you've not really lost anything.

Every network-traversing action is more risky in Access than in a client server database.  Doesn't stop us using it though does it?


I thought Leigh was actually IMPurvius
the real problem is that without doing Compact and Repair, at least occasionally, the MDB will grow rather quickly, especially if you are deleteing Tables, and the re-creating them on a daily basis, with Your MakeTable queries.  As I indicated, the tables, when deleted, are not actully GONE, they are simply marked, internally as having been 'deleted', and thus are no longer accessible - the data is still there, but no code that can ever be written can ever access that 'deleted' data.  But since it is still present, it is still taking up space, and the new tables will simply add to the amount of space used by the database.  Since Access has a strict upper limit to the maximum size of an MDB file, eventually, all of the 'deleted' objects will push you to the max size limit.

If possible, you could 'schedule' a Compact and Delete to take place every night at Midnight (I assume that there are very few of your users who would be connected to the Database at Midnight???)

>So just to summarize, you suggests the five pass- through queries called by five make table queries to be run each day.
>This would be more efficient than running five delete queries then five append queries, correct?
Yes :-)

One option to streamline your .mdb processing might be to create a "master" database with the passthrough and user queries.
Then from this (scheduled) database create each time a new database (E.g. Customer20060424.mdb, etc.) and create the tables and copy the user queries.
Thus you get a new fresh database each time and even multiple days can be compared when needed.

Leigh PurvisDatabase DeveloperCommented:
I was only trying to warn that an open recordset (just as an example like one implicitly opened by a non closed mailmerge) won't allow you to delete the table (which is required when you run a Maketable - as it first deletes the old one).
Hence a DELETE to empty the table and then APPENDing the records in doesn't require that the lock be lifted.
Sure - you don't want bad locks lingering around in any case - but at the same time - if one does happen, you don't necessarily want it to lay waste to your ability to proceed :-)

However... (again ;-)
I'd have imagined your import of records would be one of (if not the first) thing your application does.
so wayward locks are indeed unlikely.

I'm probably just being a naysaying harbinger of doom really...

(Yeah right)
Leigh PurvisDatabase DeveloperCommented:
So to re-iterate - yeah I think compacting on close is fine (and likely vital in your scenario).
Wasn't commenting on that at all.

(Struggling now)
TylerDerdenAuthor Commented:

Thank you everyone for your feedback. I'll close the question and try to split up the points fairly to those who contributed the most.
Glad we could help and thanks for splitting !

glad to be of assistance

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.