Unable to refresh Query table using Excel VBA

[margarita13]

Hello there,

I have recorded some code which creates a table selecting certain rows based on an SQL query (which I plan to make a dynamic string later on). This is a query table within Excel 2010 which imports data from an Access database. When I run the code I get the error message 'Run time error 1004': A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table.'

Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=I:\SAM\ANALYSIS\Database2.accdb;DefaultDir=I:\SAM\ANALYSIS;DriverId=25;FIL=MS Ac" _
), Array("cess;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range( _
"$AD$1")).QueryTable
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM `I:\SAM\ANALYSIS\Database2.accdb`.Sheet1 Sheet1" & Chr(13) & "" & Chr(10) & "WHERE (Sheet1.Samp_Spreadshee" _
, "t_Dates>{ts '2011-04-16 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY Sheet1.ID")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_MS_Access_Database_1"
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Save
End Sub

How do I edit this code so I can keep running it to refresh the table? I want to change the SQL SELECT statement occasionally by changing a cell containing the SQL query and that is why I want to refresh this table using this code. Please help me amend this code and Thanks in advance.

 

[RoryA]

Welcome to the forum.

That code adds a new table each time, rather than just refreshing the existing one. A simple fix would be to delete the existing one first:

On Error Resume Next
Range("$AD$1").Listobject.Delete
On Error goto 0

 

[margarita13]

Thanks a lot for your help! Your code works