Can I Create An Append Query That Overwrites 1

[JoeF]

I would like to create an export query that writes from one Access database to another and overwrites the information needs to get updated. The only way I know to do this is to go into the appended tables of the second database, delete the data that needs to be updated, and then run the append query from the first database to update the information. Can I have the append query overwrite the updated information somehow? I can't just overwrite the whole table either, because there is good information in the appended tables that should stay there.

 

[llkhoutx]

An overwrite is an Update not an Append. Link the table you want to overwrite to your acctive database and build an Update query.

 

[Guest_imported]

The make table query overwrites everytime you run the query.

 

[JoeF]

A make table query won't work becuase I will only be updating a portion of the data and all the other data would get deleted if I were to totally overwrite the table.

An update query won't do the trick, because the table I need to update is in another database. I don't think you can do an update query to another database, can you?

How about this?
1. I can create a make table query to the other database.
2. Then have a macro in the second database that checks for this table when you open it (How can I set a macro/visual basic code run at open?)
3. The macro will run an update query to update the data from the newly created table (Table A) into the table I need to update (Table B). After the update, it will delete the Table A. If Table A doesn't exist, the macro will end immediately.

This seems like a very "round about" way to do what I want. If anyone knows of a better way, please let me know!

 

[Katerine]

Is there a restriction (network permissions, or other restriction) which prevents you from creating a link to the update table in the second database? This seems like the best way.. you can then have update queries in the main database. (Linked tables work the same way as base tables as far as queries are concerned).

Otherwise, you could code the updates instead of using queries:

dim dbsMain as database, dbsData as database
dim rstInputTable as recordset, rstMain as recordset
dim lngID as long
dim strCriteria as string

set dbsMain = CurrentDb
set dbsData = Back end database (the one with the update table)
set rstInputTable = dbsMain.OpenRecordset("source table", dbOpenDynaset)
set rstMain = dbsData.OpenRecordset("Update table", dbOpenDynaset)

if not rstInputTable.BOF 'Check for empty input table
rstInputTable.MoveFirst
Do until rstInputTable.EOF
'Assuming the primary key of both tables is "ID"
lngID = rstInputTable!ID

strCriteria = "[ID] = " & lngID
rstMain.FindFirst strCriteria
if rstMain.NoMatch then
rstMain.AddNew
else
rstMain.Edit
end if
rstMain![Field Name 1] = rstInputTable![Field Name 1]
rstMain![Field Name 2] = rstInputTable![Field Name 2]
..etc.
rstMain.Update

rstInputTable.MoveNext
loop
end if

Hope that helps! Let me know if there are any questions..
Katie

 

[ghubbell]

JoeF:
File-Get External Data-Link Tables. Link to the table you need to work on/with and fire away with an append query from your first Db. It will work as though your table is in your first Db! Gord
ghubbell@total.net

 

[Quehay]

You can also use a delete query to remove the common records:

DELETE *
FROM tbl_Existing
INNER JOIN on tbl_New.PrimaryKey = tbl_Existing.PrimaryKey

Then run your append query to tbl_Existing

OR...if your cautious:

Create a new in the other db with the new records and append the old records to it WHERE PrimaryKey NOT IN tbl_Archive_Date. This way you create a new combined rcds table and keep the existing as an archive.

 

[Quehay]

Sorry, that's "you're" not "your" (English is a code that gets slayed too often in programmer forums :*>)

 

[MichaelRed]

JoeF,

Sorry to putting more on the already overcrowed table.

The 'correct' approach is the UPDATE query. Wheather to do a link or to 'include' the foregin table via code depends on a lot of details which you have not supplied. Either approach will support the use of hte Update query. You should sort through the plethora of responses, and re-organize your thoughts and seperate the wheat from the chaff.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JoeF]

Thanks for the advice everybody. I didn't realize you could insert a link to a table in an external database and then act on it as if it was within the same database. Since I need to add new records as well as append, I think the easiest way for me to do this is to create a link and then run a couple of queries. I can either 1) do an update query and then an append query for any records that are new or 2) I can do a delete query for any matching records and then an append query to insert the updated and new records.