CODEWITHSUNDEEP
vbaexcelms-access
davidjwest
davidjwest

Reputation: 546

Update Access Database using Excel - best approach

I have an Access Database and an Excel spreadsheet with a few hundred lines of data to update into the DB. Some of the fields in the Access database are not in the Excel spreadsheet.

Is it best to write an Excel VBA script to update the Access DB or is there a better approach?

I am not very familiar with Access and my VBA skills are rusty so any examples are appreciated.

Some sample data, this is from the Excel:

    MN      File Identifier
587034901   PN000352
3248029409  PN000352
3066978806  PN000456
574565507   PN000477

MN would be the "unique key" in Access (I'm used to SQL).

These will be updates and not inserts.

Thanks.

Upvotes: 0

Views: 1663

Answers (1)

iDevlop
iDevlop

Reputation: 25262

If your spreadsheet is "clean" and structured, I would find it easier to link your spreadsheet into your Access database. You can then update your Access data with a simple update query, since the list in your spreadsheet will appear like any attached table in Access.
By "clean", I mean:
- consistent data types within a given column,
- no blank row between the titles and the data
- a single line of title

Upvotes: 2

Related Questions