Copying data from one table to another (two files)

[chirschfeld]

This is a questions that has been asked so many times, but not response I found matches exactly what I'm looking to do.



I have two identical access databases. I copied the master file to my desktop to work on it while someone else was in the shared file. I edited about a hundred rows in the Data Entry table (the records exist in both files), and now I want to copy those changes back over to the master file without messing up any of the other data. I'm not sure what the other people changed in the file, I just know what records I have that need to be copied over. Again, they exist in both files, I just want to overwrite the records in the other file with my updated records without affecting other data.

Any suggestions?

[jzwp11]

You said that you know which records you changed. Do each have a unique value such as a primary key? If so, then you can run an update query for just those records you have identified.

In order to run the update query, both tables have to exist in the same database file. To do this you will have to bring the table from the other database into your database by linking to it (do not import the table). Once you establish the link, just run the update query.

To prevent this from happening in the future, you will need to split the database into front (user interface: all forms, report, queries, macro, modules) end and back (tables only) end. The back end would go on a server while each user would get a copy of the front end. This way all users can work with the data simultaneously.

[chirschfeld]

Thanks for the reply. Yes everything has a unique ID. I was looking up how to do an update query, but have a question.

What I want to update is in a different file, so how would I up make the update query pull the update values from a different file?

And thank you for the front end/back end suggestion. i will definitely try to incorporate that into my design next time.

Thanks!

[jzwp11]

What I want to update is in a different file, so how would I up make the update query pull the update values from a different file?

The only way to do that is via a linked table.

From the ribbon:

External Data-->Access-->select the file name of the other database --> click the second radio button (Link to the data source by creating a linked table)

[chirschfeld]

Ok so I linked my desktop file to the master file so both "nearly identical" tables show up in my table list.

I start an update query, but what do i input into the "Update to" field to change to data in the master file to what I updated it to in the linked file? The primary key is the same, just the data needs to be updated for some records.

Thank you for your help.

[jzwp11]

Create a new SELECT query that includes the master table and the table where you made your edits, join them via the primary key field. You will have to specify which fields of the master you intend to update (or all if that is easier)

To limit the records to only those that you updated, you will need to include some criteria.

Where mastertable.primarykey in ( )

You would input the key values in above separated by a comma

You would then change the query type from select to update. You will then have to change the Update statement based on the fields you want to update.

The query would look something like this

UPDATE tblMaster INNER JOIN mytable ON myTable.pkFieldID = tblMaster.pkFieldID SET tblMaster.mymaster1 = [mytable].[myfield1], tblMaster.mymaster2 = [mytable].[myfield2]
WHERE (((tblMaster.pkFieldID) In (1,3)));