Is there a way to populate fields in access from fields in excel?

[Tenmakk]

Is there a way to populate fields in Access from fields in Excel in Office 2010? If so, what do I need to do? I'm not very good with Access programming. I've looked all over the net and couldn't find anything.



Thanks,
Brad

[John_G]

The easiest way is to link to the Excel spreadsheet, and then use queries to transfer the data. But, if the spreadsheet is fairly complex, with merged cells and the like, it can get tricky.

Can you give more details about what you want to do?

John

[Tenmakk]

The easiest way is to link to the Excel spreadsheet, and then use queries to transfer the data. But, if the spreadsheet is fairly complex, with merged cells and the like, it can get tricky.

Can you give more details about what you want to do?

John

Well, I'm doing data entry. There are several basic fields in Access that I need to enter into from Excel. Things like "Address", "Phone Number", "Date Joined", "Money Donated", etc. I just thought it would be great if I could have Access take some of the information from Excel to populate some of the fields.

Thanks

[John_G]

You might be able to do that with VBA, though I've never tried using a linked Excel spreadsheet as a recordset. I know DLookup works (just tried it), so if your spreadsheet contains a value that uniquely identifies each row, then a series of DLookup's to populate form fields might do it for you.

HTH

John

[Tenmakk]

OK. Thanks. I guess I was looking for a "magic button" that I could press to populate Access fields from each Excel row. I'll just do it the old fashioned way and will look at DLookup when I have time.

[John_G]

Two ideas, then:

If you just want to initialize an Access table with data from the Excel, create the blank table, then make an Append Query to transfer the data from Excel to Access
or
If you do have fields that are common to to each, and can uniquely identify the rows, and have some data in the Access table, try joining the two in an Update query, and update Access from the corresponding fields in excel.

Just some ideas!

John

[Tenmakk]

OK. Thanks. I'll work on this and reply with results.