PDA

View Full Version : is there a way to add to a list object?



samuelimtech
02-09-2015, 10:52 AM
Hi,
im using the below code to query a database and put the information into a table, you will see that there are 3 WHERE clauses. In actual fact theres 30 but it wont let me run that many so ive decided to loop through them but ive hit another snag.

is there a way to change the below code so that it appends to the table thats been created?

for clarity I will have one script like the below that will create the table and then a subsequent 9 that will append to the table the first created. thanks for the help guys :)



With Sheets("Test").ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=MS Access Database;DBQ=\\uksv0015\shared\Timesheet process\Timesheet - Copy.accdb;DefaultDir=Y:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT `Import Table`.ID, `Import Table`.`Alpha Name`, `Import Table`.`Employee Number`, `Import Table`.`Work Date`, `Import Table`.`Account Number`, `Import Table`.Explanation, `Import Table`.`Sub- L" _
, _
"edger`, `Import Table`.`Pay Code`, `Import Table`.`Billing Rate`, `Import Table`.Hours, `Import Table`.`Week No`, `Import Table`.`Timesheet Identifier`, `Import Table`.`Week Ending`, `Import Table`.Co" _
, _
"mments" & Chr(13) & "" & Chr(10) & "FROM `\\uksv0015\shared\Timesheet process\Timesheet - Copy.accdb`.`Import Table` `Import Table`" & Chr(13) & "" & Chr(10) & _
"WHERE (`Import Table`.`Alpha Name`='" & Name1 & "' OR" & _
"`Import Table`.`Alpha Name`='" & Name2 & "' OR" & _
"`Import Table`.`Alpha Name`='" & Name3 & "' )")
.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

Jan Karel Pieterse
02-09-2015, 10:15 PM
Why append, do the records come from different tables?

Zack Barresse
02-11-2015, 09:44 AM
Hi there,

No, you can't append to a Table doing it like this. If you return the data as a recordset then yes, you can. Although I don't think I'd do it that way. Since this is in a database I'd recommend getting a single query to perform what you want, then just return that query to a Table in Excel. Or better yet, just like it so you merely have to refresh the Table [data].

Also, since this is a standard SELECT query, and you're using 2007+ format of an Access database, I see no reason to use an ODBC driver, and I'd probably switch to a more updated OLEDB driver.

Here's what I suggest:

Create your query in Access in it's entirety
Record a macro of going to (in Excel) DATA | From Access... OR write code to grab the query, clear the table, then return the entire recordset to the Table body.