I have a model, running from a macro in Excel. The model cycles through hundreds of thousands of iterations for forecasting purposes. I am taking the output from the model after each iteration and dumping it into Access.

I am experiencing two problems calling the Access macro (that imports the data and places it in the appropriate table) from Excel:

1.) The database connection times out. The speed of the Excel macro is drastically reduced if I open Access after each iteration to dump data in. Therefore I have connected to Access in the beginning of my Excel macro and am only calling the Access macro after each iteration.

2.) I call the Access macro in the following manner:

In the beginning of the macro the following is established:
Dim cn As New Access.Application
cn.OpenCurrentDatabase ("C:\name.mdb")
cn.Visible = False

Then after each iteration:
SendKeys "MacroName{ENTER}"
cn****nCommand acCmdRunMacro

Then at the very end:
cn.CloseCurrentDatabase
cn.Quit acQuitSaveNone
Set cn = Nothing

The problem is with the "SENDKEYS" statement. Sometimes, about 1% of the time, the macro seems to skip over the line. The macro window will open in Access prompting me to enter the macro name and "ENTER".

Any wisdom that could be imparted regarding these two issues would be greatly appreciated! Thanks for your help.