Can someone please recommend tried and true bulletproof code that would
reside in an Excel file to manipulate an Access database file. I have not
coded Access from Excel in a long time, so I'm especially interested in code
you have used that you know from experience has done the job in these two
scenarios:





Scenario #1



Data in two Excel files needs to be combined and manipulated for pivot
tables, reports, the usual programming stuff which is no problem. So far,
that data has been getting into Excel manually, by first opening up an
Access database, manually executing two queries (named MyQuery1 and
MyQuery2), and downloading each Query's recordset into its own spreadsheet
file. I need reliable code that will:

From within Excel with a macro, open C:\My\File\Path\MyDatabase.mdb, execute
MyQuery1 and MyQuery2, with each query creating its own Excel file, then
close the MyDatabase.mdb file. I can deal programmatically with the 2 new
Excel files from that point.





Scenario #2



I have a table of records in my Excel file in Sheet1 (row 1 is header and
the records start in row 2) that needs to be added to an existing table in
an Access file named C:\MyFile\Path\OtherDatabase.mdb. So, from Excel, I
need to open said Access file, add the Excel Sheet1 records to a table named
MyTable, close said Access file, and then maybe go have a beer.





Using Office 2003.



Thanks !!