Hi,
Not sure if the subject is more like supposed to be in the fields of Access forum but the destination is in Excel. Please forgive me if I came to the wrong site.
I copied a part of the code from Youtube but I need to set up a query to output a small subset of data from an Access table (the original tab is huge). The WHERE clause should have a date range like 'startdt <= 1/12/2022'. Can I use BETWEEN? I tried both but none of them worked. Besides, as I used underscore: "_" to continue at the next row but not working either.
Thanks in advance
Here goes the code:
Sub ExportAccessDBtoExcel()
Dim ConnObj As ADODB.Connection
Dim RecSet As ADODB.Recordset
Dim ConnCmd As ADODB.Command
Dim ColNames As ADODB.Fields
Dim DataSource As String
Dim intlp As Integer
DataSource = "S:\shared\BIS_Reports\Reserving and Reinsurance Database\DataExtractDB.accdb"
Set ConnObj = New ADODB.Connection
Set ConnCmd = New ADODB.Command
With ConnObj
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = DataSource
.Open
End With
ConnCmd.ActiveConnection = ConnObj
ConnCmd.CommandText = "SELECT * FROM CO_PNC_CO_POLICY_TERM WHERE [STATUS] LIKE 'Active' AND [PAYMENT_STATUS] LIKE 'Current'"
ConnCmd.CommandType = adCmdText
....
....
Not sure if the subject is more like supposed to be in the fields of Access forum but the destination is in Excel. Please forgive me if I came to the wrong site.
I copied a part of the code from Youtube but I need to set up a query to output a small subset of data from an Access table (the original tab is huge). The WHERE clause should have a date range like 'startdt <= 1/12/2022'. Can I use BETWEEN? I tried both but none of them worked. Besides, as I used underscore: "_" to continue at the next row but not working either.
Thanks in advance
Here goes the code:
Sub ExportAccessDBtoExcel()
Dim ConnObj As ADODB.Connection
Dim RecSet As ADODB.Recordset
Dim ConnCmd As ADODB.Command
Dim ColNames As ADODB.Fields
Dim DataSource As String
Dim intlp As Integer
DataSource = "S:\shared\BIS_Reports\Reserving and Reinsurance Database\DataExtractDB.accdb"
Set ConnObj = New ADODB.Connection
Set ConnCmd = New ADODB.Command
With ConnObj
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = DataSource
.Open
End With
ConnCmd.ActiveConnection = ConnObj
ConnCmd.CommandText = "SELECT * FROM CO_PNC_CO_POLICY_TERM WHERE [STATUS] LIKE 'Active' AND [PAYMENT_STATUS] LIKE 'Current'"
ConnCmd.CommandType = adCmdText
....
....