I posted the following thread earlier today in the Excel forum, but haven't received any advise. I am reposting it here as the VBA trouble may be the same for both Access and Excel...
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.Visible = False
Then after each iteration:
Then at the very end:
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.