Updating Access database

[scrumpyjack]

Is there any way I can automatically update my Access data base from my excel spreadsheet?

[xLJer]

You can update an Access database from Excel. I worked out an example
in the attached Excel and Access files, but you'll need to figure out
the steps to be done for your specific application. The attached has
two fields, a name and a score.

My example database has a link to the Excel worksheet. From the Excel
code in Module1, a macro in the Access database is run, which first
runs an Access query to append new records, if there any any, from the linked
Excel file to a permanent keyed table, then the same macro runs a
second query to update a second field in the permanent table, the scores. In the Access
macro, warnings are turned off so that you do not see the standard warning
messages where you would have to click a button to continue.

If you try out these files, put them in the same folder.

Excel code in Module1:

Please Login or Register  to view this content.

[scrumpyjack]

OK thanks I'll have to sit down and play and see what happens.
Thanks again
Fred

[xLJer]

This solution was initially tested on a Windows 7 PC. I ran it on another PC with Windows XP
and got and error when setting the Access security level. Assuming you are using one of these
operating systems, the Excel Module1 replacement code below should eliminate this error.

Also, you will need to re-link the Excel file in the Access database.
Do these steps:
- open PScores.mdb in Access
- view list of Access Tables and delete the currently linked Excel "Sheet1"
- click on File
- Get External Data & Linked Tables
- navigate to the folder with the Excel file
- choose Excel filetypes to display the file in the dialog box
- select the Excel file RunAccMacroText.xls
- check First Row Contains Headings if it is not already checked
- click Next. Linked Table Name should be "Sheet1"
- click Finish

New Excel code:

Please Login or Register  to view this content.

[scrumpyjack]

Thanks again
Fred