I need help on MS Query, I am not sure if this is the correct venue.
I need to extract certain data from MS Access to Excel using a parameter between Start Date and End Date, currently I am using the below connection code (if that is what it called) It is working fine if the dates are already defined as shown on the blue highlight My problem, I cannot modify it in such a way that Start Date will be based on the value of cell $C$2 and End Date will be from $C$3.
I really need your help.
I need to extract certain data from MS Access to Excel using a parameter between Start Date and End Date, currently I am using the below connection code (if that is what it called) It is working fine if the dates are already defined as shown on the blue highlight My problem, I cannot modify it in such a way that Start Date will be based on the value of cell $C$2 and End Date will be from $C$3.
I really need your help.
Code:
[h=3][SIZE=2]Sub Macro4()[/SIZE]
[SIZE=2]'[/SIZE]
[SIZE=2]' Macro4 Macro[/SIZE]
[SIZE=2]'[/SIZE]
[SIZE=2]
[/SIZE]
[SIZE=2]'[/SIZE]
[SIZE=2] With ActiveWorkbook.Connections("Query from MS Access Database").ODBCConnection[/SIZE]
[SIZE=2] .BackgroundQuery = True[/SIZE]
[SIZE=2] .CommandText = Array( _[/SIZE]
[SIZE=2] "SELECT `2015`.ID, `2015`.FName, `2015`.LName, `2015`.BDay, `2015`.Count" & Chr(13) & "" & Chr(10) & "FROM `2015` `2015`" & Chr(13) & "" & Chr(10) & "WHERE (`2015`.BDay>=[COLOR=#0000cd][B]{t" _[/B][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000cd][B] , "s '1982-10-01 00:00:00'}[/B][/COLOR] And `2015`.BDay<=[COLOR=#0000cd][B]{ts '1991-05-21 00:00:00'}[/B][/COLOR])")[/SIZE]
[SIZE=2] .CommandType = xlCmdSql[/SIZE]
[SIZE=2] .Connection = Array(Array( _[/SIZE]
[SIZE=2] "ODBC;DSN=MS Access Database;DBQ=C:\Users\xxx\Desktop\Admin Automation\Sample Files\Working File\OFT v1.accdb;DefaultDir=C:\Users" _[/SIZE]
[SIZE=2] ), Array( _[/SIZE]
[SIZE=2] "\xxxx\Desktop\Admin Automation\Sample Files\Working File;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _[/SIZE]
[SIZE=2] ))[/SIZE]
[SIZE=2] .RefreshOnFileOpen = False[/SIZE]
[SIZE=2] .SavePassword = False[/SIZE]
[SIZE=2] .SourceConnectionFile = ""[/SIZE]
[SIZE=2] .SourceDataFile = ""[/SIZE]
[SIZE=2] .ServerCredentialsMethod = xlCredentialsMethodIntegrated[/SIZE]
[SIZE=2] .AlwaysUseConnectionFile = False[/SIZE]
[SIZE=2] End With[/SIZE]
[SIZE=2] With ActiveWorkbook.Connections("Query from MS Access Database")[/SIZE]
[SIZE=2] .Name = "Query from MS Access Database"[/SIZE]
[SIZE=2] .Description = ""[/SIZE]
[SIZE=2] End With[/SIZE]
[SIZE=2] ActiveWorkbook.Connections("Query from MS Access Database").Refresh[/SIZE]
[SIZE=2]End Sub[/SIZE]
[/h]