Import data from Excel to some Access records

[Sinon]

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

 

[Micron]

Is the db split (I think so, based on your statement that your actions affect the source data)? In that case, you need to copy the FE and BE (front end/back end) databases to your local drive (network or pc). Ensure the new destination is trusted, lest you cause trust issues. Then you must open the fe and delete all the linked tables - not any that are not linked, and re-link them by pointing your fe copy to the new be copy. Linked tables are very obvious to anyone who has experience with them. If you are unsure, read up on linked tables and split databases.

When you have done this, you should experiment on the new db table you need to alter. I would change one value in one record by some harmless edit (such as an extra period in a text value) and shut down this db. Then go to the one that should NOT be affected (the original) and confirm that your change is NOT visible there. If you are successful, the edit happened only in your copy. Now you can experiment with your table, but I would make at least one copy of it in your new db first. If you blow it up, you have one or more backups.

It sounds like the change you want to make is an update to the values that are already in the field. The easiest approach might be to link the Excel file as a table (or import it) and use it as a native table in your new db. Using this Excel table and any others that are required for the update, create a select query that returns only the records you know need to be altered. When you are happy with this, turn the Select into an Update query and test it. If it fails, you have a backup table if needed. If not, you can apply your query to the original database. Again, in that one, the easiest approach is likely to link to the workbook that contains the data.
Hope that helps.