Hi All
I have an Access database with around 200,000 rows, this increases by a few
hundred per day. In this database is a column containing the actual date
(dd/mm/yyyy). I wish to open this database from Excel, extract the following
1. The first and last date in the date base ( the Access table is not in
date order)
2. A list of all dates with a count of the number of records

Look forward to your responses

Cheers

hi,

try something like this - you need to add a reference to the DAO library:


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 first date
strSQL = "SELECT min(datefield) from table" ' enter your own info here
Set rs = db.OpenRecordset(strSQL)
Range("a1").CopyFromRecordset rs

' get last date
strSQL = "SELECT max(datefield) from table" 'enter your own info here
Set rs = db.OpenRecordset(strSQL)
Range("b1").CopyFromRecordset rs

' 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("a3").CopyFromRecordset rs

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing
Set app = nothing

End Sub

you can do it with ADO as well but this will work fine

Chris


"Nigel RS" wrote:

Hi All
I have an Access database with around 200,000 rows, this increases by a few
hundred per day. In this database is a column containing the actual date
(dd/mm/yyyy). I wish to open this database from Excel, extract the following
1. The first and last date in the date base ( the Access table is not in
date order)
2. A list of all dates with a count of the number of records

Look forward to your responses

Cheers