Update or Append Query?

[mraetrudeaujr]

I'm working with a database where the users are inputting new records throughout the day. I am actually working on a copy, but I would like to put a command button on my form that I could use that will 'update' the database from the 'live' database without having to go back to the database window and use the "Get External Data" menu item. What type of query should I be using? How would I go about it? I've tried the using the 'transferdatabase' Docmd method, but it (obviously) will not allow the imported database to overwrite the existing database. Any help is greatly appreciated.

 

[dhookom]

If you want to add records to a table in another MDB, the easiest method is to link the other table into your MDB. Then, use an append query that appends your local table records into the linked table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BoxHead]

Do you want to replace the database or a table in the database?

If I understand what you're after, I had a situation that was similar with a split database. I had a 'development' copy on my PC and the live version on the server. Having the copy kept me from periodically locking all the records when I was working out some new query or report.

I found it easiest to set up shortcuts to the local folder and the server folder and just drag the server backend into my local db folder. I clicked okay to 'replace existing file' and it was done.

That, of course, updated all tables. If your db is not split, that wouldn't work for you, but looking at the TransferDatabase method, there is a Destination argument that should allow you to rename the table to something like 'tblTemp'.

Once you've got the updated data in tblTemp into your local database, you could delete all records in your local main table and append all records from tblTemp to that table. Or, you could delete all records in tblTemp that already exist in the Main table and append the records that remain in tblTemp.




John

Use what you have,
Learn what you can,
Create what you need.