"MSACCESS.EXE" process issue - How to terminate this process correctly

[xlguy]

Hello and Happy New Year!

Hoping someone can help me resolve this weird issue, that I've been having since last year

So, what happens is that with the code provided below, if I allow the "Access.Quit" statement to be executed i.e. not commented out, then the process "MSACCESS.EXE" is terminated, however, upon every consecutive run/execution of the code, I do receive the error "Run-time error '462': The remote server machine does not exist or is unavailable"...against the statement "Set ws = DBEngine.Workspaces(0)".

Now, on the other hand, if I comment out the same statement (and not allow it to execute) then I can run the code multiple times without receiving the above-mentioned error message, however, the process "MSACCESS.EXE" remains running, and if I try to open the database via Windows Explorer, it/Access does not open up. I do see a file with the extension ".laccdb" created in the same folder as the ".accdb" database file, but unless and until I manually (using Task Manager) kill the process "MSACCESS.EXE", I cannot launch Access.

So the question is, is there something different I need to do in order to both avoid receiving the error 462 whilst running the code multiple times sequentially, as well as to be able to open Access without having to manually kill the "MSACCESS.EXE" process?

Thanks.

Sub copyCMdataToAccessDB()
    Application.StatusBar = "Now exporting 'Current Data' to Access database..."    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim sDb As String
    Dim sSQL As String
    Dim qdf As QueryDef
    sDb = "[URL="file://\\CATOU-OGFSPUWSX\rosec$\G\EIRS_Demo\WFP-TESTING.accdb"]\\CATOU-OGFSPUWSX\rosec$\G\EIRS_Demo\WFP-TESTING.accdb[/URL]"
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(sDb)
    ' A stored query would be better
    sSQL = "Parameters p1 Text, p2 Datetime; " _
        & "INSERT INTO Table1 (AText,ADate) Values ([p1],[p2])"
    
    Set qdf = db.CreateQueryDef("", sSQL)
    qdf.Parameters!p1 = "ABC"
    qdf.Parameters!p2 = #1/17/2013#
    qdf.Execute dbFailOnError
    Debug.Print qdf.RecordsAffected
    
    db.Close
    ws.Close
    Set db = Nothing
    Set ws = Nothing
    
    ' ---REM--- Access.Quit
    
    Application.StatusBar = ""
End Sub

 

[Sektor]

The MSACCESS. EXE shouldn't be in Task Manager at all. Moreover, I didn't see your "Access" variable declaration.
Here's my test procedure:

Sub F()


    Dim eng As DAO.DBEngine 'Microsoft Office 14.0 Access database engine Object library
    Dim db As DAO.Database
    Dim q As DAO.QueryDef
    
    Set eng = New DAO.DBEngine
    
    Set db = eng.OpenDatabase("c:\MyDb.accdb")
    Set q = db.CreateQueryDef("", "PARAMETERS p1 INT; INSERT INTO Table VALUES (p1)")
    q.Parameters!p1 = "777"
    q.Execute dbFailOnError
    
    MsgBox q.RecordsAffected 'Shows "1"


End Sub

 

[xlguy]

Thanks Sketor...for suggesting a slightly different method/code...however I am receiving the following error, at the statement "Set db = eng.OpenDatabase...":

Run-time error '3343': Unrecognized database format 'path\DBname.accdb'

Any ideas why, or how I can resolve this error to see if I can proceed further along?

Thanks.

 

[Sektor]

I had same issue. The problem was that database was created in Access 2010 and was opened in Access 2007. I don't know what exactly was the problem, but I had to recreate database in Access 2007 in order for code to work. The thing is there are lots of thing which were deprecated and added in Access 2010.

 

[xlguy]

In the hopes of potentially helping someone else (who might be experiencing this same issue), the following code is what helped me resolve (both) my issues i.e.:

1) not being able to execute the code multiple times in succession without receiving the "Run-time error '462': The remote server machine does not exist or is unavailable" error, and

2) being able to run the code multiple times but not being able to launch Access (or the database) from Windows Explorer without first killing the "MSACCESS.EXE" process using Task Manager:

Sub copyCMdataToAccessDB()
    Application.StatusBar = "Now exporting 'Current Data' to Access database..."

    Dim oAccess As Access.Application
    Dim oDB As DAO.Database
    
    Dim q As DAO.QueryDef
    Dim sSQL As String

    Set oAccess = New Access.Application
    oAccess.OpenCurrentDatabase "c:\Temp\MyDb.accdb"

    Set oDB = oAccess.CurrentDb

    ' A stored query is best
    sSQL = "Parameters p1 Text, p2 Datetime; " _
        & "INSERT INTO Table1 (AText,ADate) Values ([p1],[p2])"
    
    Set q = oDB.CreateQueryDef("", sSQL)
    
    q.Parameters!p1 = "Testing"
    q.Parameters!p2 = #1/17/2013#
    q.Execute dbFailOnError
    
    '--- MsgBox q.RecordsAffected 'Shows "1"
    Application.StatusBar = ""
End Sub

 

[Sektor]

I don't understand why do you use Access application again after I gave you right solution?

 

[Macropod]

Cross-posted at: "MSACCESS.EXE" process issue - How to terminate this process correctly

Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[xenou]

In your original code were you running this from inside Access?

Your line of code (commented out) just says Access.Quit. It doesn't use any references or objects set in the procedure. I guess it would just quit whatever Access it happens to find (probably the one running the code), but that isn't likely what you wanted. You only needed to close the workspace you opened (which I think was your final solution, actually).