How to I update access with an excel sheet?

[superfly5203]

Hello,



I have searched in the forums and couldn't find anything quite like what I trying to do, but I am sure I just missed it. Anyways, I have a main data base that that I keep contract numbers and information in, and I have been going through and destroying physical files that are old. I made a list of physical files in excel with their contract numbers and a few other fields that match with the database. I was wondering how do I update the database with the new data from the spreadsheet? I tried an import and it made copies, which I don't want. There has to be an easier way then going through the database one by one and copying the data from excel into access, I have almost 500 contracts in the excel listing.

Thanks for any help!

[JoeM]

So, are you trying to add new records, or update existing records or both?

If you want to update existing records, either link your Excel data into Access, or import it into a "temporary" table. They use an Update Table to update the appropriate data from your temp table to your final table.

If importing new records, first follow the same step of linking/importing the Excel data, then do an unmatched query between this temp table and your final table to find your new records. You can then change that to an Append Query to add the new records to your final table.

You may want to read up on Action queries in Access's built-in help files, or search for tutorials on the web if you are unfamiliar with them (Update and Append queries are "Action" queries).

[superfly5203]

I am trying to update existing records. I will try what you suggested. Thanks for the input and i will let you know if it works!

[superfly5203]

I get to the part where I set up the update query and I changed the criteria to [Sheet1].[Contractor Name] and a two other fields, but when I hit run nothing happens. Down in the bottom left of the screen it says "This action or event has been blocked by disable mode". I ran the query before adding in those criteria and it grabbed only the contract numbers from database that were on the excel sheet, so I am doing something right i guess. What am I doing wrong to get the disable mode message?

[JoeM]

See here: http://stackoverflow.com/questions/1...-disabled-mode

[superfly5203]

Thanks! that worked and It updated my records! Saved me hours of time! Thanks a bunch!