Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel access data from Access?! | Excel Discussion (Misc queries) | |||
How do I access the access data via Excel 2002 with auto update ? | Excel Programming | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
Calculate data from Access and return to Access | Excel Programming | |||
Access data -work in Excel- save in Access | Excel Programming |