How do I execute VB code on an access db backend from the frontend?

Question

I have my database split into a backend containing the data tables and a frontend containing forms, queries, reports, and modules, with the tables linked. One of the tables gets its data from an excel sheet; normally I'd use a linked table but if any queries are open then the excel sheet cannot be opened, so instead I use a saved import to pull all the data from the excel sheet into the table.

Now, I'd like to add a button to the menu on my frontend that runs DoCmd.RunSavedImportExport "savedimportnamehere" except the saved import is on the backend, so I'd have to send that command to the backend... and I'm not quite sure how to do that. If anyone could point me in the right direction I'd appreciate it.

Answer 1

You can run a command against an instance of MS Access, so:

Dim apAccess As New Access.Application
apAccess.OpenCurrentDatabase ("c:\docs\backend.mdb")
apAccess.DoCmd.RunSavedImportExport "savedimportnamehere"

You can also run a query against an instance of the db or a connection:

sSQL="INSERT INTO Table1 ( ADate ) " _
& "SELECT SomeDate " _
& "FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$a1:a4]"