VBA error 462 when updating table in Access from Excel

Question

I'm getting the 462 runtime error when updating an Access table from Excel VBA. I think the references are correctly qualified with the object variable as described here and here, but I'm still getting an error on the line where the number of records is assigned to dbImageCount using DCount.

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

Public AppAccess As Access.Application
...
Sub btnSave2Access_Click()
    Dim MyRow As Long, LastCaptionRow As Integer
    Dim sPath As String, STblName As String, CatalogNum As String, LotNum As String
    Dim i As Integer, dbImageCount As Integer
    CatalogNum = Trim(Sheets("Tier2Worksheet").Range("B2"))
    LotNum = Trim(Sheets("Tier2Worksheet").Range("B3"))
    LastCaptionRow = Range("E1000").End(xlUp).Row
    sPath = Sheets("Settings").Range("B16")
    STblName = "tblProductPictures"
    Set AppAccess = New Access.Application
    With AppAccess
        .OpenCurrentDatabase sPath
        For i = 1 To LastCaptionRow
            'error in next line
            dbImageCount = DCount("[SortOrder]", STblName, "[CatalogNum] = '" & CatalogNum & "' AND [LotNum] = '" & LotNum & "'") 'get current image count in DB for catNum/LotNum combo
            While dbImageCount < LastCaptionRow 'adds record to picture table when required
                dbImageCount = dbImageCount + 1
                .DoCmd.RunSQL "INSERT INTO " & STblName & " (CatalogNum, LotNum, SortOrder) VALUES ('" & CatalogNum & "','" & LotNum & "','" & dbImageCount & "');"
                DoEvents
            Wend
            With .DoCmd
                .SetWarnings False
                .RunSQL "UPDATE " & STblName & " SET PicPath='" & Range("E" & i) & "' Where [CatalogNum]='" & CatalogNum & "' and [SortOrder]='" & i & "' and [LotNum]='" & LotNum & "';"
                .RunSQL "UPDATE " & STblName & " SET FullCaption='" & Range("D" & i) & "' Where [CatalogNum]='" & CatalogNum & "' and [SortOrder]='" & i & "' and [LotNum]='" & LotNum & "';"
                .SetWarnings True
            End With
        Next i
        .CloseCurrentDatabase
        .Quit
    End With
    Set AppAccess = Nothing
    Application.StatusBar = False
End Sub

Manually setting the value of dbImageCount on the fly during debug (commenting out the DCount line) properly updates the database with the new picture data.

It's important to note that this problem does not occur consistently. After months of use, the error did not creep up until this week and even then it didn't happen for every update attempt. In addition, it never happened during development (on a different system).

At first, I thought it was a network glitch or something of the like, but then I read that the 426 error is specifically an Office automation problem, so I expect that we will see it again soon.

Answer 1

You need to use DCount as a method of the Access Application:

With AppAccess
    .OpenCurrentDatabase sPath
    For i = 1 To LastCaptionRow
        'error in next line
        dbImageCount = .DCount("[SortOrder]", STblName, "[CatalogNum] = '" & CatalogNum & "' AND [LotNum] = '" & LotNum & "'") 'get current image count in DB for catNum/LotNum combo
        While dbImageCount < LastCaptionRow 'adds record to picture table when required
            dbImageCount = dbImageCount + 1
            .DoCmd.RunSQL "INSERT INTO " & STblName & " (CatalogNum, LotNum, SortOrder) VALUES ('" & CatalogNum & "','" & LotNum & "','" & dbImageCount & "');"
            DoEvents
        Wend
        With .DoCmd
            .SetWarnings False
            .RunSQL "UPDATE " & STblName & " SET PicPath='" & Range("E" & i) & "' Where [CatalogNum]='" & CatalogNum & "' and [SortOrder]='" & i & "' and [LotNum]='" & LotNum & "';"
            .RunSQL "UPDATE " & STblName & " SET FullCaption='" & Range("D" & i) & "' Where [CatalogNum]='" & CatalogNum & "' and [SortOrder]='" & i & "' and [LotNum]='" & LotNum & "';"
            .SetWarnings True
        End With
    Next i
    .CloseCurrentDatabase
    .Quit
End With