Code won't run twice - Run time error 462 2

[Whereismyhammer]

I am trying to create a command button in ArcMap 9.3.1 that closes any MS Access applications that are currently open. The code works fine the fist time it is run but if I try to run it a second time I get the following error:

"Run-time error '462': The remote server machine does not exist or is unavailable."

I have tried to resolve the problem and think it has to do with non-explicit referencing but I can't find where the non-explicit reference is located. My code is actually longer and does more than just close Access but the error seems to arise with this procedure. I have isolated each procedure of my code and only get the error for this one. I am very new to programming so any guidance people can provide will be greatly appreciated.

Private Sub QuitAccess_Click()

'Create the Access object
Dim oAccess As Access.Application
Set oAccess = Access.Application

'Quit Access
oAccess.Quit '<--This is where the error occurs the second time the code is run

'Release Access Object
Set oAccess = Nothing

End Sub

 

[SkipVought]

Check out the CreateObject and GetObject methods.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

If it runs the first time, what happens? All Access instances are closed, yes? So if you run it a second time, there should be nothing there to close. You need to error trap your code so you can check if there is an Access instance, or not.

Skip has the correct suggestions. Look up CreateObject and GetObject.

Set oAccess = GetObject("Access.Application")

and if there is an error....


unknown

 

[Whereismyhammer]

Brilliant! I now have a fully functional application linking spatial data in ArcMap to a report in Access.

Here is the new code for the procedure to quit Access:

Private Sub QuitAccess_Click()
'Create the Access object
Dim oAccess As Object
Set oAccess = GetObject("C:\My Documents\Monitoring\east_ca_vegplots_082310_Gabes.mdb")

'Quit Access
oAccess.Quit

'Release Access Object
Set oAccess = Nothing
End Sub

It works great with no errors. Thanks both of you! I still have a LOT to learn.

 

[SkipVought]

I would recommend reading the VB Help very carefully and using the excellent code examples as a tutorial.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}