Populate table using a query on access

How can i drop a table and insert the values from a query on it ?
Jose BredariolPMPAsked:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
DROP TABLE mean to remove that table - so you couldn't insert records into it if you did that. You could instead remove all records:

Currentdb.Execute "DELETE * FROM YourTable"

Now insert your query values:

Currentdb.Execute "INSERT INTO YourTable SELECT * FROM YourInsertTable"

Of course, YourTable and YourInsertTable must have exactly the same structure. If they do not, you will have to list out the columns:

Currentdb.Execute "INSERT INTO YourTable(Col1, Col2, Col3)  SELECT Col1, Col2, Col3 FROM YourInsertTable"
Jose BredariolPMPAuthor Commented:
But how can i do that in just one command ? Or like a .bat ? Is that possible ? Like macro ?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could create saved queries in Access, and then create a macro that runs those queries. I don't use macros, so can't really help much with that, but it's pretty straight forward with the OpenQuery action in your macro.
A make-table query will over write any existing table so that is one step.  However, generally, I would avoid this (as well as the delete/insert) whenever possible because it bloats the database.  If you are working with temporary data, the best solution is to link to the external file rather than importing it.

If the recordset is large and/or you want to update it as you work with it, I recommend using a "template" database.  For this technique, I create a database with only empty tables in it (make sure it is compacted).  Then as part of the import process, I copy the template to the working directory, link to the empty copy and import into the linked database.  That keeps the bloat out of the main database.  Each time you do an import, you start with an empty template and that avoids the need to frequently compact.

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
Jose BredariolPMPAuthor Commented:
Thanks all
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.