PDA

View Full Version : [SOLVED] SQL in excel VBA



samuelimtech
01-29-2015, 03:02 AM
Hi all, im sure this is possible but i dont know where to start....
I have a query that ive written access to take data from one table and put it in another if that data meets the criteria.
I also have a code to import ALL data from that table to an excel file.

what im after is mixing the two together to get the excel file to only import data that meets the query criteria. see below for the import script and SQL string.

SQL (INSERT data from the Import table INTO the Hours table WHERE employee number = JDE)


"INSERT INTO ["Hours]( [Alpha Name], [Employee Number], [Work Date], [Account Number], Explanation, [Sub- Ledger], [Pay Code], [Billing Rate], Hours, [Week No], [Timesheet Identifier],
[Week Ending] )" & _
"SELECT [Alpha Name], [Employee Number],[Work Date], [Account Number], Explanation,[Sub- Ledger],[Pay Code],[Billing Rate],Hours,[Week No],[Timesheet Identifier],[Week Ending]" & _
"FROM [Import Table] WHERE [Employee Number] = " & JDE & ";"




With Sheets("Hours_Table").ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\uksv0015\shared\Timesheet process\Timesheet - Copy.acc" _
, _
"db;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Passw" _
, _
"ord="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Trans" _
, _
"actions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:D" _
, _
"on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex D" _
, "ata=False;Jet OLEDB:Bypass UserInfo Validation=False"), Destination:=Sheets("Hours_Table").Range( _
"$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("Import Table")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"\\uksv0015\shared\Timesheet (file://\\uksv0015\shared\Timesheet) process\Timesheet - Copy.accdb"
.ListObject.DisplayName = "Import_Table"
.Refresh BackgroundQuery:=False
End With
Dim xConnect As Object
For Each xConnect In ActiveWorkbook.Connections
If xConnect.Name <> "ThisWorkbookDataModel" Then xConnect.Delete
Next xConnect



thanks