Clear table and append new records in one-step?

[Bennie47250]

Typically, I will run a delete query to clear out a table before repopulating it with new data using an append query. Is it possible to do this with one step/query? Using a make table query is not possible as I need to preserve the table layout/format.

Thanks
Bennie

 

[PaulBricker]

Not any quick or easy way. The process you are using is the generally accepted method.

Paul

 

[GingerR]

Why do you want to do it in a single step? You can write a macro or code to run all your queries in order.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Bennie47250]

As it is now, I need to write 2 queries, one to delete and the other to repopulate. Would be nice if I could write just one query. Yes I will run them one after the other in a macro.

 

[GingerR]

Just wondering why it'd be "nice"? You only need to write them one time, right?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Bennie47250]

Right again Ginger but I have to write about 10 of them for this project and I know I will have this situation on future projects too. Just looking for ways to be more efficent.

 

[dhookom]

I generally don't used saved query for this. I build SQL statements in code and execute them. Make sure you comment your code so you can easily identify the purpose of the statements.


Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[GingerR]

Instead of a query, you can write

Currentdb.Execute ("Delete * from Table1")
Docmd.OpenQuery ("Table1-ADD")

Currentdb.Execute ("Delete * from Table2")
Docmd.OpenQuery ("Table2-ADD")

that'll eliminate the delete queries, just copy/paste the execute line for the deletes...

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Bennie47250]

While I have been doing Access for years, just last week I learned one could do an "UPSERT" (Update or Insert)in one query. Was hoping to learn that it was possible to do a clear and append in one step too.

Thanks for the suggestion, will give it a try.

 

[dhookom]

UPSERT"? I thought this was an "INDATE" ;-)


Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[GingerR]

Or DELPEND?


I just looked up UPSERT, seems it's a SQL Server command.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Bennie47250]

I found this article that explains how to do an UPSERT in Access. Seems to work very well

http://groups.google.com/group/micr...k=st&q=upsert+access+bradley#92c75e54d9001808

 

[GingerR]

Sorry but I don't see a explanation at all, just code for an Update query and someone asking if both can be done at the same time with no reply....This is not something available in MS Access. I've googled and read a lot of forum posts, seems it's in MySQL for one, but definately not in MS Access.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Bennie47250]

But did you try it? I have and it works in the test I have performed.

I see both the question and answer in the link I provided.

The question was:
Is there a way to do two things at once?


I want to update TableOld using TableNew to include revisions made to existing records, and also to add any new records.

I know I can write an update query and an append query to do this separately; can these be combined somehow?

The reply is:

In Access, yes, sometimes referred to as an UPSERT query. In SQL Server, no.


Link TableNew to TableOld using a left join (include all from new) base on whatever your key field is.
Change the query type to an UPDATE query,
Then drag every column in TableOld to the Grid and in the update row, reference that column from TableNew.


The SQL might look something like the following if you were updating the lastname, firstname,and SSN values. The down side of this particular example is that I used the SSN as the key field, and if you changed the SSN in the new table, you would end up with a new record (same person but with two different SSNs), so you need to make sure
that the key field you are using to link the two tables has not been changed.


UPDATE tblNew
LEFT JOIN tblOld
ON tblNew.SSN = tblOld.SSN
SET tblOld.NameLast = [tblNew].[NameLast],
tblOld.NameFirst = [tblnew].[namefirst],
tblOld.SSN = [tblnew].[ssn];

 

[dhookom]

I have created this type of query only a few times in the past. This works in Access but I don't recall ever creating a similar SQL statement that would work with MS SQL Server.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[JoeAtWork]

You may be looking for efficiencies in the wrong places. Less SQL text does not necessarily mean it's more efficient. I would be interested to know if that "UPSERT" statement is more or less efficient then running two statements, i.e. an UPDATE and an INSERT. I'm guessing the 2 statements would be less overhead because you are not doing a JOIN.

As for your original question, I have never heard of a combined DELETE/INSERT statement. Even if there was one, it probably wouldn't be more efficient than doing the two separate statements.