Linking Access DB with Excel Spreadsheet

  • Thread starter Thread starter Allen Geddes
  • Start date Start date
A

Allen Geddes

Hello, I come from a strong background in Excel VBA Programming... but am
not quite sure how to accomplish this in Access.

I have an Access DB with 13 fields. Field 1 is an ID field. I want to
search Column 1 in an Excel Spreadsheet (which is also an ID column) and if I
find a matching ID, I want to update the corresponding fields in Access, with
the row in Excel. I hope that made sense...

Is this something I can accomplish with VBA in Access? Thanks in advance
for your help!
 
Hello, I come from a strong background in Excel VBA Programming... but am
not quite sure how to accomplish this in Access.

I have an Access DB with 13 fields. Field 1 is an ID field. I want to
search Column 1 in an Excel Spreadsheet (which is also an ID column) and if I
find a matching ID, I want to update the corresponding fields in Access, with
the row in Excel. I hope that made sense...

Is this something I can accomplish with VBA in Access? Thanks in advance
for your help!
Click to expand...

Don't actually need any VBA at all to do this! You can do it with a Query.

In Access, use File... Get External Data... Link; choose "Excel" from "files
of type" and navigate to your spreadsheet.

You'll now have a new (linked) table showing all the data in your spreadsheet.

Create a new Query with your Access table and the linked spreadsheet, joining
by ID. Change it to an Update Query. On the "Update To" line under each Access
table field that you want to update type

[linkedsheetname].[fieldname]

using the actual names of your linked table and its fields. Run the query by
clicking the ! icon.

One problem that may come up: Access enforces referential integrity and
uniqueness of ID (primary key) fields; Excel doesn't. What would you want to
happen if there were two or more rows in the spreadsheet with the same ID??
 
Oh wow, that was way easier than I thought! Thank you so much for your help!

Regarding the uniqueness of IDs, the Excel file is generated from an export
of another program, and will never have duplicate identical IDs, so I'm not
going to worry about Access handling them.

Thanks again!


John W. Vinson said:
Hello, I come from a strong background in Excel VBA Programming... but am
not quite sure how to accomplish this in Access.

I have an Access DB with 13 fields. Field 1 is an ID field. I want to
search Column 1 in an Excel Spreadsheet (which is also an ID column) and if I
find a matching ID, I want to update the corresponding fields in Access, with
the row in Excel. I hope that made sense...

Is this something I can accomplish with VBA in Access? Thanks in advance
for your help!
Click to expand...

Don't actually need any VBA at all to do this! You can do it with a Query.

In Access, use File... Get External Data... Link; choose "Excel" from "files
of type" and navigate to your spreadsheet.

You'll now have a new (linked) table showing all the data in your spreadsheet.

Create a new Query with your Access table and the linked spreadsheet, joining
by ID. Change it to an Update Query. On the "Update To" line under each Access
table field that you want to update type

[linkedsheetname].[fieldname]

using the actual names of your linked table and its fields. Run the query by
clicking the ! icon.

One problem that may come up: Access enforces referential integrity and
uniqueness of ID (primary key) fields; Excel doesn't. What would you want to
happen if there were two or more rows in the spreadsheet with the same ID??
Click to expand...
 
Hi,

My question is if it is possible to define primary key of Excel linked table in Access and to change data type to Autonumber. I have looked through many information and find no answer here. It seems that it is not to change data struction under design view in Access and I try to do it in Excel format. But I cannot locate any format similar to Autonumber.

Kindy advise.

Lilian
 
Back
Top