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
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