"MSACCESS.EXE" process issue - How to terminate this process correctly
Hello and Happy New Year!
Hoping someone can help me resolve this weird issue, that I've been having since last year :think:
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.
Code:
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 = "\\CATOU-OGFSPUWSX\rosec$\G\EIRS_Demo\WFP-TESTING.accdb"
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
