Access code using Excel object, error: 462 The remote server machine does not exist or is unavailable

Question

I am trying to create Lists in Excel based on a Query I create. I am getting a variety of errors, inconsistently with the same code. My research points to this article which describes the problem very well. Microsoft Support Article

The code gives the various errors that are outlined in the article on the line "With wkb.ActiveSheet.ListObjects.Add"

        wkb.Queries.Add Name:="qry" & strProcedure, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Odbc.Query(""dsn=" & strDSN & """, ""select * from " & strProcedure & "()"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"


    With wkb.ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=qry" & strProcedure & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & "qry" & strProcedure & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "qry" & strProcedure
'        .Refresh BackgroundQuery:=False
    End With

The article gives an example of how to reproduce the problem, and how to fix it. But it's not easy to see from their example how I should change my code to avoid the error.

From the article:

Note The error message occurs because the code refers to the method of the cell without preceding the call with the xlSheet object variable.

Stop the project and change the following line of code: xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello"

Change the line of code to resemble the following line of code. xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10,2)).Value = "Hello"

Run the program again. Notice that you can run the code multiple times without receiving an error message.

Edit: Here is the full code cleaned up. I run this from Access 2019 (365) Note: the excel.quit forces me to enter a file name and save and close the spreadsheet. Even after doing it, and no other Excel windows open, Excel stays in the task manager until I close Access.

Edit 2: As in the article, this code runs fine the first time but fails on subsequent runs with an inconsistent error message.

Public Sub CreateExcelQueryTest()

Dim AppExcel As Excel.Application

Dim wkb As Excel.Workbook
Dim qry As Excel.WorkbookQuery
Dim strProcedure As String
Dim strDSN As String

Set AppExcel = New Excel.Application

strDSN = "INSPIRE33"
strProcedure = "wwcustomers_addresses"

Set wkb = AppExcel.Workbooks.Add

wkb.Queries.Add Name:="qry" & strProcedure, Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Odbc.Query(""dsn=" & strDSN & """, ""select * from " & strProcedure & "()"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"

wkb.Worksheets.Add.Name = strProcedure

With wkb.ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=qrywwcustomers_addresses;Extended Properties=""""" _
    , Destination:=Range("wwcustomers_addresses!$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [" & "qry" & strProcedure & "]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "qry" & strProcedure
    .Refresh BackgroundQuery:=False
End With

AppExcel.Visible = True

AppExcel.Quit

End Sub

When it works, I see this:

Just now I got an error report from Excel when testing this function. Needless to say I sent MS a 'frown'.

Feedback Type:

Frown (Error)

Error Message: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Stack Trace: at Microsoft.Mashup.Client.Excel.NativeExcelFunctionsC2RBase.Microsoft.Mashup.Client.Excel.INativeExcelFunctions.SaveMashupData(IntPtr workbookPointer, String data) at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.SaveMashupData(IWorkbook workbook, MashupCustomXmlPart data) at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.NativeWorkbookStorageDeferrer.Release() at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action) at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action) at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbookIdentity workbookIdentity, UndoableActionType actionType, Action action) at Microsoft.Mashup.Client.Excel.Fill.FillManager.ForEachFillSessionByWorkbook(Action`1 action) at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.UpdateQueriesProgress() at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.b__9_0() at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Invocation Stack Trace: at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace() at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage) at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail) at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture) at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture) at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.b__0() at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback) at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e) at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action) at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.OnFillUpdateTimerTick(Object sender, EventArgs eventArgs) at System.Windows.Forms.Timer.OnTick(EventArgs e) at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Supports Premium Content: False

Update OK, I am calling this one sub from the immediate window. It works on the first attempt, I know it gets to the end because I now have a message box that says 'done' But the excel process does not end. If I click the Stop button (blue square) then the Excel process ends and the second attempt does not fail. The code should not still be running at this point. Never seen this before.

I cite the article for two reasons, one, my code also works the first time through but the exact code run again the second time causes an error. Also, the varying error messages I get are all listed in the article. I have updated by my post with clean test code. — Peter, Mar 29 '20 at 22:34
I get compile error on WorkbookQuery. Do I need another library reference? I expect fix will involve Destination Range reference. — June7, Mar 29 '20 at 22:46
Yes, I homed in on that too. at first I did not have the name of the sheet with ! before the range reference. — Peter, Mar 29 '20 at 22:50
Is it relevant that the Excel task does not disappear from Task Manager until Access closes, even though I have used AppExcel.Quit ? — Peter, Mar 29 '20 at 22:52
It can be. I have encountered this issue. Now I cannot get past Queries.Add. I get 'does not support' error. If you just need to feed data to worksheet cells, why establish ODBC connection and query in this new workbook? — June7, Mar 29 '20 at 23:58
I got this code by recording a macro: Data...Get Data...From Other Sources, From ODBC, then selecting my DSN, and typing in my SQL statement. The data is stored in PostgreSQL database. How else am I going to get the data into Excel? I too am suprised that the data source providor as "Microsoft.Mashup.OleDb.1" rather and ODBC;{PostgreSQL Unicode} as I'm used to seeing. — Peter, Mar 30 '20 at 01:01
Okay, but there are other ways to drop data on a worksheet. If you intend to create a new workbook every time you export data, why bother with creating a connection and query in workbook? Purpose would be to refresh data from Access every time workbook opens. If you are not going to open workbook again, connection and query serve no purpose. I tried macro recording and none came up with Queries.Add line. Maybe enough has changed since Access2010 so I won't be able to replicate this code. Sorry, I use only Access. — June7, Mar 30 '20 at 01:23
The intention is that going forward the spreadsheet is now stand-alone with a live query to refresh the data. I want users to take an existing sheet and add this (and other) queries to it when they want it. — Peter, Mar 30 '20 at 01:39
Suggest you manually build connection and query from Excel side and not bother with one-time Access code to accomplish setting up this workbook then give users that workbook. — June7, Mar 30 '20 at 01:42

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Consider several fixes:

As your cited docs indicate, qualify the Range for your cell destination which would be the worksheet.

A line of code that calls an Excel object, method, or property without qualifying the element with an Excel object variable
Avoid use of ActiveCell/ActiveSheet/ActiveWorkbook. Instead, use assigned object like your newly added worksheet, strProcedure;
Properly close workbook with Workbook.Close like you do on user interface: Workbook Close > App Exit;
As best practice in VBA, release any Set object with Set object = Nothing, not simply close the background process.

See adjustment:

With wkb.Worksheets(strProcedure).ListObjects.Add(SourceType:=0, Source:= _ 
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=qry" & strProcedure & ";Extended Properties=""""" _
        , Destination:=wkb.Worksheets(strProcedure).Range("$A$1")).QueryTable
        ...
End With

' NO NEED TO SHOW IF YOU IMMEDIATELY QUIT
AppExcel.Visible = True   

' CLOSE WORKBOOK AND EXIT APP
wkb.Close SaveChanges:=True
AppExcel.Quit 

' RELEASE RESOURCES
Set wkb  = Nothing
Set AppExcel = Nothing

Thank-you! It would seem that it was the Destination:=wkb.Worksheets(strProcedure).Range("$A$1")).QueryTable — Peter, Mar 30 '20 at 04:37
Great to hear and glad to hep! And please consider the other items to cleanly exit the Excel processes. — Parfait, Mar 30 '20 at 15:34

Access code using Excel object, error: 462 The remote server machine does not exist or is unavailable

1 Answers1