Automate Import/Update of Database

[Wendy]

Hi all,

Office 2003 products, Win XP.

I would like to automate the updating of the MDB file, from the contents of
an Excel spreadsheet, and do this on a daily basis.

The spreadsheet creates perfectly fine, and does match up the fields exactly
to the ones in the MDB file. There is also a unique ID field in the
spreadsheet.

My problem is that while I can get this file to manually import/update, I
have not been able to find a way to automate the transfer of data without
adding duplicates to the file.

So far I have been trying to use the Transfer Spreadsheet function, which
does work when running the macro, to import datda when clicked, but you can't
run it multiple times without multiple duplicates being added. There doesn't
appear to be anywhere in this function to specify an update + import of new
data, only straight 'import of data'.

(This file is used for read-only purposes, and is pushed to the website via
FTP after manipulation).

My goal:

* Update an existing MDB file automatically with updates to existing record
entries, and add new entries, from an Excel Spreadsheet.

* Have this process run automatically.


Thanks for your help,

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com) are several small sample
databases which illustrate how to do this. Look for any that start with
"Update", specifically UpdateWStatus.mdb and UpdateImportSeekFind.mdb.
These will add new records and update existing records. They are highly
specific to the structure of the tables I used, but they will illustrate the
process.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Wendy]

Thank you.
--
Wendy

On my website (www.rogersaccesslibrary.com) are several small sample
databases which illustrate how to do this. Look for any that start with
"Update", specifically UpdateWStatus.mdb and UpdateImportSeekFind.mdb.
These will add new records and update existing records. They are highly
specific to the structure of the tables I used, but they will illustrate the
process.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L