N
Nigel RS
Hi All
Chris Wilkinson kindly provided me the following code that extracts from an
Access Database with around 200,000 records a summary of the number of rows
by Date.
This displays the count for each date, however I am unable to figue out how
to show the date as well.
Currently I get in one column:
2,345
4,567
589
I would like to get in two columns....
23/3/2007 2,345
24/3/2007 4,567
25/3/2007 589
Sub extractData()
Dim strSQL As String
Dim app As DAO.DBEngine
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set app = New DAO.DBEngine
Set db = app.OpenDatabase("C:\db1.mdb") ' enter your own info here
' get count of records by date in date order - enter own info below
strSQL = "SELECT COUNT(*) from table GROUP BY datefield ORDER BY
datefield"
Set rs = db.OpenRecordset(strSQL)
Range("A1").CopyFromRecordset rs
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Set app = nothing
End Sub
Chris Wilkinson kindly provided me the following code that extracts from an
Access Database with around 200,000 records a summary of the number of rows
by Date.
This displays the count for each date, however I am unable to figue out how
to show the date as well.
Currently I get in one column:
2,345
4,567
589
I would like to get in two columns....
23/3/2007 2,345
24/3/2007 4,567
25/3/2007 589
Sub extractData()
Dim strSQL As String
Dim app As DAO.DBEngine
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set app = New DAO.DBEngine
Set db = app.OpenDatabase("C:\db1.mdb") ' enter your own info here
' get count of records by date in date order - enter own info below
strSQL = "SELECT COUNT(*) from table GROUP BY datefield ORDER BY
datefield"
Set rs = db.OpenRecordset(strSQL)
Range("A1").CopyFromRecordset rs
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Set app = nothing
End Sub