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.

score 2 · Accepted Answer · answered Aug 02 '18 at 15:05

2

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

answered Aug 02 '18 at 15:05

Erik A

31,639
12
42
67

OK, I will try this out thank you. Could you venture a guess as to how the original code ever worked with DCount improperly configured? Why would VBA throw the 462 on that line in some runs and not others...and on some computers and not others? These kinds of issues can drive a developer crazy! – MBB70 Aug 02 '18 at 16:16
Afaik it just shouldn't work. `DCount` and other domain aggregates can only be used as functions when launched from Access. – Erik A Aug 02 '18 at 16:23
This is a strange one because when I was developing this I never saw the 462 error and Dcount was generating the correct value (the number of records in the table with the same catalognum and lotnum). I'll put it through some more rigorous testing and get back with you. – MBB70 Aug 06 '18 at 15:56
Maybe MS has updated Office to consider the Access.Application object the equivalent of an active Access session from which functions such as Dcount can be launched. I just tested this several times and in every case, DCount produced the correct record count. – MBB70 Aug 06 '18 at 18:05

VBA error 462 when updating table in Access from Excel

1 Answers1