Closing Access after Automating

[datahound]

Hello Folks,

I am opening up an instance of Access and creating a table from a text file (Phoo.txt) from within VB6. However, when I go to shut down the Access Application from within MS Access itself, the database closes but the instance of Access remains open and will not close without terminating from the Task Manager.
Here is the code that we are using to open Access...help?

Public Sub OpenAccess()

'Use automation to open up MSAccess
Dim oAccess As Object
Dim db As Database
Dim ws As Object

Dim strMDBFileName As String

strMDBFileName = "C:\a_Phoo.mdb"

Set oAccess = CreateObject("Access.Application&quot

Set ws = oAccess.DBEngine.Workspaces(0)

On Error Resume Next
'**********************
Kill (strMDBFileName)
'**********************

Set db = ws.CreateDatabase(strMDBFileName, dbLangGeneral)

oAccess.OpenCurrentDatabase (strMDBFileName)

DoCmd.TransferText acImportDelim, , "zs_Phoo", "C:\a_Phoo.txt", True

oAccess.Visible = True

oAccess.CloseCurrentDatabase

Set db = Nothing
Set ws = Nothing
Set oAccess = Nothing

End Sub


Thanks

Graham and Mike

 

[JustinEzequiel]

unless I misunderstood you, don't you need "oAccess.Quit" somewhere in your code?

 

[Elysium]

Graham and Mike,

I ran into this same exact problem, although it was with Excel. Still, you can use the code below to solve the problem with Access.

....
On Error Resume Next
Set oAccess = GetObject(,"Access.Application&quot
oAccess.Visible = False

If Err <> 0 Then
Set oAccess = CreateObject(&quot;Access.Application&quot
End If
....

Here's what's happening. Even though you close the Access application, the exe is still running in the background, i.e. it's still instantiated. This is an inherent flaw with MS products. So, the only way to get rid of the exe from running in the background is to manually &quot;kill the process&quot; through the task manager. However, that doesn't lend to an automated program.

The answer is to then check for a running instance of Access, and if there is none, create one. The GetObject function will return an error code of (0) if Access has already been instantiated (running an exe), so you can continue on with your code. However, if the GetObject function can't find a running instance of the Access exe file, a non (0) error is returned and your code knows to instantiate Access through the CreateObject function. At this point everything is just fine and you won't get any nasty errors.

If this doesn't work for you, let me know.

Elysium

 

[datahound]

Thanks tonnes, worked like a charm!!