Error 462 when code runs second time (1 Viewer)

[schniggeldorf]

I wrote some code to evaluate each email as it arrives in MS outlook, evaluate whether it contained useful data, and copy the data from the body of the text. I then wrote the following Sub to send that data to an existing Access database. Everything works exactly as intended when I first run it. However, when I run it again, I get Error 462: The remote server machine does not exist or is unavailable.

I've read multiple posts suggesting that I need to fully specify each object, but as far as I can tell, I've done that already. The code throwing the error is marked in red.

Sub ExportToAccess(MRN As Long, PatientLastName As String, PatientFirstName As String, AttendingLastName As String, _
    AttendingFirstName As String, strEncounterDate As String, Optional Status As Integer, Optional PendingType As Integer, Optional ClosedType As Integer)
'    Transfer data from emails (form responses) to Access

    Dim appAccess As Access.Application
    Dim wrkspc As DAO.Workspace
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strMonth As String
    Dim Month As Integer
    Dim Day As Integer
    Dim Year As Integer
    Dim EncounterDate As Date
    Dim CcfMdId As Integer
    Dim SQL1 As String
    Dim SQL2 As String

On Error GoTo Err_ExportToAccess
    Set appAccess = CreateObject("Access.Application")
    [COLOR="Red"]Set wrkspc = DBEngine.Workspaces(0) 'This is the bug!![/COLOR]
    Set db = wrkspc.OpenDatabase(constDbPath)
    Set rst = db.OpenRecordset("tblKpEmailUpload", dbOpenDynaset) 'Specify the table where the data go after they're processed (e.g. tblPatients).
    rst.FindFirst "MRN= " & MRN

    'If record already exists, edit. Else add new.
     If rst.NoMatch = False Then 'If recExist > 0 Then
        rst.Edit
    Else
        rst.AddNew
        rst.Fields("MRN").Value = MRN
    End If

    rst.Fields("PatientFirstName").Value = StrConv(PatientFirstName, vbProperCase)
    rst.Fields("PatientLastName").Value = StrConv(PatientLastName, vbProperCase)
    rst.Fields("AttendingFirstName").Value = AttendingFirstName
    rst.Fields("AttendingLastName").Value = AttendingLastName

    EncounterDate = CDate(Mid(strEncounterDate, 4, 2) & "-" & Left(strEncounterDate, 3) & "-" & Right(strEncounterDate, 4))
    rst.Fields("EncounterDate").Value = EncounterDate
    rst.Fields("Status").Value = Status
    rst.Fields("PendingType").Value = PendingType
    rst.Fields("ClosedType").Value = ClosedType
    rst.Update
    
'   Run queries to append this record to tblPatients in IMATCH Data File.accdb, and to delete the records in tblKpEmailUpload afterwards
   
    SQL1 = "INSERT INTO tblPatients (MRN, FirstName, LastName, RefDate, Status, PendingType, ClosedType, PatientCcfHaMdId )" _
    & "SELECT tblKpEmailUpload.MRN, tblKpEmailUpload.PatientFirstName, tblKpEmailUpload.PatientLastName, tblKpEmailUpload.EncounterDate, " _
    & "tblKpEmailUpload.Status, tblKpEmailUpload.PendingType, tblKpEmailUpload.ClosedType, tblCcfHeadacheStaff.CcfStaffID " _
    & "FROM tblKpEmailUpload INNER JOIN tblCcfHeadacheStaff ON (tblKpEmailUpload.AttendingLastName = tblCcfHeadacheStaff.LastName) " _
    & "AND (tblKpEmailUpload.AttendingFirstName = tblCcfHeadacheStaff.FirstName);"

    SQL2 = "Delete tblKpEmailUpload.MRN " _
    & "From tblKpEmailUpload " _
    & "WHERE (((tblKpEmailUpload.MRN)>0));"

    db.Execute SQL1 'This uploads the new data from tblKpEmailUpload to tblPatients
    db.Execute SQL2 'This empties tblKpEmailUpload

'   Close open objects
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing
    wrkspc.Close
    Set wrkspc = Nothing
    appAccess.Quit
    Set appAccess = Nothing
    
Exit_ExportToAccess:
    Exit Sub

Err_ExportToAccess:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_ExportToAccess
End Sub

Thanks for your help.

 

[MarkK]

A few things...
1) You don't need the Access.Application. Notice you create it, but you never use it for anything until you do 'appAccess.Quit' at the end.
2) You don't need a DAO.Workspace, you can just use the OpenDatabase method of the DBEngine object to get your hands on the DAO.Database. This simplifies your code to...

Sub ExportToAccess(MRN As Long, PatientLastName As String, PatientFirstName As String, AttendingLastName As String, _
    AttendingFirstName As String, strEncounterDate As String, Optional Status As Integer, Optional PendingType As Integer, Optional ClosedType As Integer)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
[COLOR="Green"]    'many unused variables here - removed[/COLOR]
    Dim SQL1 As String
    Dim SQL2 As String

On Error GoTo Err_ExportToAccess
    Set db = DBEngine.OpenDatabase(constDbPath)

3) rather than open a recordset and then search through it using .FindFirst, it is always quicker to open exactly the recordset you need, so I would suggest your next few lines of code as...

   Set rst = db.OpenRecord( _
      "SELECT * " & _
      "FROM tblKpEmailUpload " & _
      "WHERE MRN = " & MRN)
   With rst
      If .EOF then
         .AddNew
         !MRN = MRN
      Else
         .Edit
      End If
      ...

See how in that code we open the exact recordset we need using MRN as the criteria? Then, if there are no records we add one, otherwise we edit the existing one. Same outcome as yours, but avoiding the use of DAO.Recordset.FindFirst.

hth
Mark

 

[Orthodox Dave]

Microsoft has described exactly your scenario, but when Word (not Access) is the application being controlled by automation. i.e. it works the first time but the second time produces error 462. The scenario they describe is where:

"Visual Basic has established a reference to Word due to a line of code that calls a Word object, method, or property without qualifying it with a Word object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than once."

So your error is not in your line in red, but from the fact there is a reference still open from the first time you ran it.

The example they give in the article is to change this line:
.PageSetup.LeftMargin = InchesToPoints(1.25)
to this:
.PageSetup.LeftMargin = oWord.InchesToPoints(1.25)
(where oWord is the Word.Application)

You can get the article by googling this:
You may receive the "Run-time error '-2147023174' (800706ba)" error message or the "Run-time error '462'" when you run Visual Basic code that uses Automation to control Word

(Sorry I don't have permission to supply a url)

This may or may not apply to your case, but it's worth a look.

 

[static]

You don't need to open an Access file to update it.
Specify the filename in the SQL and execute it from the currentdb.

e.g.

const tbl as string = "[c:\somefolder\otherdb.accdb].tblKpEmailUpload"

Set rst = currentdb.OpenRecordset("select * from " & tbl & " where MRN= " & MRN)

'If record already exists, edit. Else add new.
If rst.eof Then 
	sql = "insert into " & tbl & " (PatientFirstName,PatientLastName,AttendingFirstName...
Else
	sql = "update " & tbl & " set PatientFirstName='" & StrConv(PatientFirstName, vbProperCase) & "'...
End If

currentdb.execute sql

 

[schniggeldorf]

Mark:
Thanks for your help. I eliminated the workspace and rearranged the other code as you suggested. Subsequently, I got the same error message as before, just triggered by the Set db = DBEngine.OpenDatabase(constDbPath) statement.

The reason I had created appAccess in the first place was that in the absence of that statement (and its explicit closure later) the Sub would work fine, but I would then be unable to open the target database subsequently. Because of this, I put appAccess back in, and substituted "Set db = appAccess.DBEngine.OpenDatabase(constDbPath)" for the statement you'd recommended. This did the trick, so I no longer get the error 462, and I can still open the access database after my code runs.

Thanks for your help.

 

[schniggeldorf]

Orthodox Dave:

Thanks for the link. I'd read that post earlier, but it took until this morning before I figured out how to fully specify the Set db=... statement. (See above for what eventually worked.)

 

[schniggeldorf]

Static:

Thanks for your advice. I found that if I didn't open an instance of Access, then after my code ran my database wouldn't open. By explicitly opening access, then closing it at the end of the Sub, I avoided that problem.

 

[static]

That makes no sense.

The database that you are running code from must already be open and it wouldn't affect being able to open the other database in any way.

 

[MarkK]

I assumed the OP was running the code in Excel.

 

[static]

Oh he said Outlook. In that case I have no idea.