Import data from Excel to some Access records

[Sinon05]

Hello and thanks for any help. I'm a noob with Access but my company has asked a question about possibly importing some information for certain records in Access. Basically, I work for an insurance company and they have a claims system which was built in Access about a decade ago by someone who has left the company recently. The system works and is fine for current needs. However, we were recently asked to amend a field for certain records (claims). Because there are about 200-300 records, we are looking at a possible import solution.

The problem is, I have never done this before and am worried that it might affect other records or other fields in the records. The only thing that needs to change is one field for and the rest must remain unchanged.

I know the Access table name & record numbers (RecordNo - textbox in Access) and the name of the field I want to change (Reference - textbox in Access) but am unsure how this can be imported, how the excel file needs to be prepared and how to make sure no new records are added but instead existing records are amended. For instance, can I just have 2 columns, one called "RecordNo" and the other "Reference"? Or do I need to have blank columns as per the table in Access? Do I need to create a named range or an excel table or simply put the columns in Excel? Is there any specific formatting that I should be using (Text or General or something else)? When importing in Access, do I need to choose "Append a copy of the records to the table"? How will it know which record to amend as the Access table will contain thousands of records that I don't want changed in any way?

I also have access to the "Navigation Pane" in Access where I can find tables and queries etc and not sure if the records in question can be bulk amended on the table instead?

To make matters more complicated, the Access database is on the server and needs to be accessed by multiple users at the same time so I would ideally like to test this out on a separate copy. But copying it to my own computer does not sever the connection with the copy on the server and any changes are reflected in the original copy immediately.

I tried looking online but I can't seem to find anything that will quash my worries. I can find a few articles talking about importing issues but they are all for previous versions of Access and really I can barely understand the current version. We are using Excel and Access 2013

Thank you

[stimulus]

Hi Sinon05,

The way I would progress with this is to use a macro to run a loop that generates an update SQL statement for each record that you wish to update.
If you open the attached and look at the commented cells, it will show you all you need to do.

Please note that any ' that may be in the reference field will be replaced with ^ as ' is used as the field value separator when generating the SQL statement.

If you find this helpful, please consider adding rep

Thanks,
James.

[stimulus]

Also I forgot to say that as it uses a transaction, either all the records will get updated, or none.
And please remember to create a back-up of your database before running this.

[alansidman]

If you are just trying to amend the data in one field, then why not just run an update query. Import the excel data to a new table in Access. Make sure that you have a field that is common in the existing table. Once you have the new table. Create and run an update query for the field in question.