Update records from excel into access

Topic

New Reply

I would like to update records to the deceased section in access from a excel spreadsheet.
I have a access dbase with patient #, name, and a deceased date. The Excel spreadsheet is setup the same way. Every month I get a excel deceased spreadsheet. How can I add the date of death from the excel spreadsheet to my access deceased date section? Please help I just manually typed in over 200 deceased dates. I’m using Access 2003

Reply | Quote

Replies

Buckshot,

What you want to do is:

[*]Link the Spreadsheet as an external data source.
[*]Write an Update Query that does the following:

[*]Links the two tables {Access & Excel} on the Patient#
[*]Set the link so it shows only matches between the two tables.
[*]Update the Deceased date field in the Access table using the data from Excel.

[/LIST]
This is a general outline of what needs to be done. If you need more detailed help please post back. :cheers:

Update: Ok I’ve tested, using Excel 2007 but will be basically the same for all versions, and added graphics {make sure to click on graphics to see the whole thing} to show the entire process. Note the XLS file is the last graphic but I should have posted it first. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RetiredGeek thank you sooooo much–your the best

Reply | Quote

Buckshot,

You’re welcome. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG…Wonder if you can assist an old Aussie with this. I have an Access (2010) tblBookings with a DateCompleted field. In an Excel (2010) Template I have a linked tbl to the Access Dbase. I would like to enter a date into the Excel DateCompleted field in the Excel linked tbl and have it update the Access Date completed field. Is the problem I have related to the fact the Excel file is a “Template”……which is after completion saved under a different name ? Many Thanks

Reply | Quote

Is the problem I have related to the fact the Excel file is a “Template”……which is after completion saved under a different name ? Many Thanks

Bill,

Welcome to the Lounge as a new poster. :cheers:

Yes, if you use a different name each time you will have to change the file name in the update query. HTH

BTW: It is proper Lounge etiquette to start a new thread with a new question and not add on to an existing one.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Well, since this appears to have evolved into an old guys thread, I’ll chime in (not sure who is older, but I’ve been around this forum for well over a decade). If you are working repeatedly with different Excel workbooks, linking to them may not be the best solution. You might instead create a VBA procedure that automated Excel from Access, and let’s you use DAO or ADO to update the appropriate record. It’s not a subject for the total novice to consider, but if you have some VBA skills, it opens up some additional options for move data between Excel and Access. If you want to explore the subject of automation, have a look at our Automation 101 tutorial. We use that technique to create some very complex reports for specific recipients in Excel, and then email them to the recipient (using automation of Outlook of course).

Reply | Quote

RG & Wendell,

Many thanks. Having some success by putting data in a separate XL file and updating Access from there. The Access file presently only gets 1 field update from 1 XL file. This coul change of course if I ever get up to speed . Like the idea of the DAO or ADO update but as a novice will have to burn some midnight oil. Appreciate the tip on the Tutorial and will give it a try.
Thanks again…Bill

Reply | Quote