Update Existing Access Tables with Data from Excel

[ChelseaC]

I am trying to determine a way to update multiple tables in Access from a single spreadsheet in Excel. I need to be able to change data in an existing record based on the Excel spreadsheet (only if something is different) and add new records if there isn't a matching record between Access and Excel.



The Excel file is set up so each project has many many columns of information. The information in certain columns needs to go to fields in certain tables.

What would be the recommendation in this situation? Are there any references to this sort of thing?

[June7]

Lots of code.

Lots of references. Approach depends on structure of the spreadsheet.

Options involve:

1. set link to Excel sheet. Treat the link as a table (except cannot edit data). Use it as source for queries (SELECT, UPDATE, INSERT)

2. open the Excel sheet as an object in VBA and manipulate the object to pull in data and analyze and do what you want with it - update existing records or add new records

[JeffChr]

Your success depends on your level of expertise. Judging from your question, I'd say you have a rather steep learning curve IMO. I'd start by linking the Excel worksheets in Access. Access will treat the link as a table. Next, I would look at queries, especially if you do not have a working knowledge of VBA. The query learning curve is faster, but still involved. Hope this helps a little.

[ChelseaC]

The challenge I face with trying to link the Excel file (because that was my first idea) is that the Excel file will not always be the same. I just can't guarantee that it will be able to work that way. I have used VBA in Excel quite a bit, but have very limited knowledge of using VBA in Access. It's different and I just haven't been finding a good resource to learn from.

How would queries be used in this situation? Is there a guide or resource of this being shown?
Thank you!

[JeffChr]

The Excel file "will not always be the same": is that because the data will change or the structure. If structure, what kind of structural changes. For instance, Excel column headers are typically used as field names in Access, so efficient db engineering dictates those don't change. Although they can move in the workbook, ie shift columns, column header names should remain static.

[ChelseaC]

I understand that. The data will definitely change. However, I can't guarantee that the headers in the Excel file will remain exactly the same. It would make sense for them to remain the same and for the most part they will, but I have to hand control of this database over to a a different group of people in a few weeks and it needs to be as dummy proof as possible. I think I can deal with headers not being exactly the same, but I don't know how to work with data in Access.
The point of my original post was to see if there was a method out there that could be used to update info that's in multiple tables in Access from a single worksheet in Excel. However, that doesn't seem to be the case, so I need to write all of the VBA code to accomplish what I need. I know I can do the Excel side of things, but I am not confident with the Access side of things. Do you have a solid reference for VBA in Access?

[June7]

Lack of consistency in Excel structure makes this very difficult to impossible. If you don't know the Excel column names in advance, how will you be able to programmatically 'map' to the appropriate fields in Access table? Really need to coordinate with editors of the Excel sheet and settle on a fixed structure.

[JeffChr]

No way can VBA skills be developed in a "few weeks". Probly best bet is guru.com or hire somebody here. sorry.