Using Microsoft Access via Excel VBA, Houston we have a problem

[Guest]

Using the following code I'm able to open Access Databases, and with the proper autoexec macro within the database it will run the required tasks. Access however even once it has performed it's tasks and should terminate, it stays running behind the scenes sucking resources. Is there a command that should be added to close access? It never really opens, it simply is running behind the scenes so there isn't a way to close it

Sub start_access1(
' Initialize string to database path
Const strConPathToSamples = "C:\Documents and Settings\rcheek\Desktop\
strDB = strConPathToSamples & "autoruntest.mdb
' Create new instance of Microsoft Acces
Set AppAccess =
CreateObject("Access.Application"
' Open database in Microsoft Access window
AppAccess.OpenCurrentDatabase strD
End Su

I have a quit function within the Access Macro, but it is still running in the background sucking system resources. Is there something that I'm missing? Should Excel close Access instead of Access closing itself? If so, how would Excel know that the Access Macro's have completed running?

Any help is greatly appreciated.

 

[Jan Karel Pieterse]

Hi ImAGine,

I have a quit function within the Access Macro, but it is still running
in the background sucking system resources. Is there something that I'm missing?
Should Excel close Access instead of Access closing itself?
If so, how would Excel know that the Access Macro's have completed running?

You should close access yourself from Excel:

AppAccess.Quit
Set AppAcces=Nothing

Since you set an object reference to access (AppAccess), you can use access code from within Excel to open the database.
That way, the code of the autoexec will execute before the next macro command in Excel will be done.

Something like this (untested):

(Set a reference to the Microsoft Access library using tools, references)

Dim AccessApp As Access.Application
AccessApp.OpenCurrentDatabase "YourFIle"
DoEvents
AccessApp.Quit

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com