Extacting data from Access
 

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

Extacting data from Access
 

Hi Chris
Great! I had not considered using DAO/ADO as I assumed wrongly the only way
was to use Query.

I will give it a try. Many thanks

Nigel

"Chris Wilkinson" wrote:

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

Extacting data from Access
 

Sorry, I should have put for that part
strSQL = "SELECT datefield, Count(*) from table GROUP BY datefield " & _
" ORDER BY datefield"

"Nigel RS" wrote:

hi Chris
That works!

I get the count by date, but not the actual date for the count.

Is there anyway I can show both the DATE in column 1 and and the COUNT in
column 2

Cheers


"Chris Wilkinson" wrote:

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

Extacting data from Access
 

hi Chris
That works!

I get the count by date, but not the actual date for the count.

Is there anyway I can show both the DATE in column 1 and and the COUNT in
column 2

Cheers


"Chris Wilkinson" wrote:

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

Extacting data from Access
 

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