updating new records w/ old backend data?

[willmafingerdo]

Hello,
Somehow we lost all the data for one of the fields in our DB, I was wondering if I can run a query that will match the record number in a backed up backend and fill in the name fields that are missing from the active database? So I would like to take the names from the old database and match the record numbers on the current one and populate the name fields?

[JoeM]

Import/link the data table from the old, backed-up copy of the database to the new database.
Then, create an Update Query, joining the two tables (old/new), updating the field with the value from the old table.

Note. Did you change any of the Properties of the field (i.e. data type, or options)? If you change properties where the data residing in that field does not follow the new rules, it could wipe out all the data. If that is what happened, be sure to change those Properties back to what they were previously, so your Update Query can write the values back.

[John_G]

This is easily done. First, in the active database, create a link to the table in the backup database.

Then in query design view, add the new (incomplete) table and the old backup table which you have linked to. Join the two tables on the PK field common to both.

(Make it a select query at first)

Add these fields to the query grid:

- The PK from either one of both tables (not really necessary, but it can help)
- The blank Name field from the incomplete table
- The valid name field from the linked backup table

You can add whatever other fields you want to help with record verification, not a problem.

Run the query as a SELECT query to verify you are matching the correct records

If it look's correct, then:

- Change the query to an Update query
- In the "Update To" line for the new (blank) field, enter [Backup table].[name field], changing the table and field names to match yours.

Run the query again and you're done.

Be sure to make a temporary backup of the active database first, because you just never know.....

Any records which were added to the active database since the last backup was done won't be updated - those you will have to fix manually, I think.

Hope you get it fixed!

[willmafingerdo]

Thank you all for you reply's. I will be working on this over the next couple days. Yes my backend field with the names was changed from "text" to "number" so I think that is what happened.. I think I understand what I need to do to fix it, if not ill msg

[willmafingerdo]

I lost the specialist name field, that's is what I needed to fix, I got the specialist names copied over and everything looks good... but now the issue is that it will not pull the specialist name in on the form for any of the records that are already in the DB. now If I enter a new person and search for their record it will display the specialist name. If I run a query it pulls the data just fine, I tried deleted the original specialist name field off of the form and added it back on but it didn't do anything.

[willmafingerdo]

I found the issue, there were some invalid entries for the Specialist names on the table and that was causing issues. Seems to be working now, thanks everyone!!