Hi Guys,
I've just joined the forum after reading all the helpful advice here on many occasions.
I'm a bit stuck with this one and I haven't seen any posts which relate directly to the same problem.
I'm using ADO SQL to read records back from an Access database table.
In the subroutine which follows, the code works fine when I want to bring back all records in the table. However, when I try and use SQL to bring back only records in a certain date range, I get nothing apart from the the table headers.
I'm not at all familiar with this area so it could be something really crazy I'm doing - like not having the correct SQL - though I don't have any errors reported.
Anyway here's the code, if anyone's got any ideas:
Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, TargetRange As Range, _
DBStartDate As String, DBEndDate As String, _
DBStartTime As String, DBEndTime As String)
'Define the connection to the database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
' open the recordset to select all records
' .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
' open the recordset with filtering
.Open "SELECT * FROM " & TableName & _
" WHERE DATE >= " & DBStartDate & _
" AND DATE <= " & DBEndDate & ";", cn, , , adCmdText
' write data from the recordset to the worksheet
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Hi Guys,
I worked this out myself, the problem is that the SQL needs to enclose dates in #'s as in:
.Open "SELECT * FROM " & TableName & _
" WHERE Date >= #" & DBStartDate & "#" & _
" AND Date <= #" & DBEndDate & "#;", cn, , , adCmdText
Also my local settings were defaulting dates in date format fields earlier to be DD/MM/YY before they were copied into the string format dates.
The date format for use in VBA SQL can only be MM/DD/YYYY !!!
I've just joined the forum after reading all the helpful advice here on many occasions.
I'm a bit stuck with this one and I haven't seen any posts which relate directly to the same problem.
I'm using ADO SQL to read records back from an Access database table.
In the subroutine which follows, the code works fine when I want to bring back all records in the table. However, when I try and use SQL to bring back only records in a certain date range, I get nothing apart from the the table headers.
I'm not at all familiar with this area so it could be something really crazy I'm doing - like not having the correct SQL - though I don't have any errors reported.
Anyway here's the code, if anyone's got any ideas:
Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, TargetRange As Range, _
DBStartDate As String, DBEndDate As String, _
DBStartTime As String, DBEndTime As String)
'Define the connection to the database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
' open the recordset to select all records
' .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
' open the recordset with filtering
.Open "SELECT * FROM " & TableName & _
" WHERE DATE >= " & DBStartDate & _
" AND DATE <= " & DBEndDate & ";", cn, , , adCmdText
' write data from the recordset to the worksheet
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Hi Guys,
I worked this out myself, the problem is that the SQL needs to enclose dates in #'s as in:
.Open "SELECT * FROM " & TableName & _
" WHERE Date >= #" & DBStartDate & "#" & _
" AND Date <= #" & DBEndDate & "#;", cn, , , adCmdText
Also my local settings were defaulting dates in date format fields earlier to be DD/MM/YY before they were copied into the string format dates.
The date format for use in VBA SQL can only be MM/DD/YYYY !!!
