Hello there,
We have excel files that connect to an Access DB using 'Get & Transform'. Up until last week or so, it worked perfect, but something has change and now, from time to time, me and other users are getting the green "Unexpected error" message and after closing it, getting a debug with runtime error 1004 "attempted to read or write protected memory. this is often an indication that other memory is corrupt".
The error happens while on the " .Refresh BackgroundQuery:=False" line.
We all have Office 2016 32bit. I've tried to switch to retrieve data using "Get External Data", but that didn't help either since not all queries work using that method (because of having calculated fields).
Anyone know why this happens?
Here's the code:
Thank,
Udi
We have excel files that connect to an Access DB using 'Get & Transform'. Up until last week or so, it worked perfect, but something has change and now, from time to time, me and other users are getting the green "Unexpected error" message and after closing it, getting a debug with runtime error 1004 "attempted to read or write protected memory. this is often an indication that other memory is corrupt".
The error happens while on the " .Refresh BackgroundQuery:=False" line.
We all have Office 2016 32bit. I've tried to switch to retrieve data using "Get External Data", but that didn't help either since not all queries work using that method (because of having calculated fields).
Anyone know why this happens?
Here's the code:
Thank,
Udi
VBA Code:
ActiveWorkbook.Queries.Add Name:="qryModelHul", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Access.Database(File.Contents(" & x & "), [CreateNavigationProperties=true])," & Chr(13) & "" & Chr(10) & " _qryModelHul = Source{[Schema="""",Item=""qryModelHul""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " _qryModelHul"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=qryModelHul;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [qryModelHul]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "qryModelHul"
.Refresh BackgroundQuery:=False
End With