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
– Peter, Commented Mar 29, 2020 at 22:34
I get compile error on WorkbookQuery. Do I need another library reference? I expect fix will involve Destination Range reference. — June7
– June7, Commented Mar 29, 2020 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
– Peter, Commented Mar 29, 2020 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
– Peter, Commented Mar 29, 2020 at 22:52

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 30, 2020 at 3:59

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Over a year ago

Thank-you! It would seem that it was the Destination:=wkb.Worksheets(strProcedure).Range("$A$1")).QueryTable

Parfait Over a year ago

Great to hear and glad to hep! And please consider the other items to cleanly exit the Excel processes.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related