Getting records from Access tables and queries into an Excel Worksheet

[Arbiel Perlacremaz]

Hi there

I don't know whether I'm posting in the right group. If not, please tell me
where to post.

I'm just trying to the exchange data between Access and Excel using VBA. I
cannot figure out how I can select all records in a table or in a query to
copy them and paste them in an Excel Worksheet. I've been able to drive
Access from Excel and have it "DoCmd.OpenQuery".

I think I should use the "Recordset" objects, but there are no such object
in the Access Object Model.

Can I also update the SQL string of a query, as for changing the starting
and ending dates of a run ? I've not been able to access the "Properties" of
a query, even when it's open (I suppose I could read and write the property
"SQLStyring" or something comparable).

Thank you very much to whom may help me.

A newbie in Access (Access 2000)

Arbiel

 

[Van T. Dinh]

You can do this using the Menu File / Export ... and select "Save As Excel
{suitable version}" and use your Table / Query as the source..

In the DatasheetView of your Table / Query, you can also use Tools / Office
Link ... / Analyze with Excel.

In code, you need to use the TransferSpreadsheet method.

 

[Dominic Olivastro]

In VBA, you don't really want to use the Access Object, but either DAO or
ADO. The two are quite similar.

Using DAO:

dim r as dao.Recordset
dim d as dao.database
dim s as Excel.Worksheet

set d = dao.opendatabase ("MyDatabase.mdb")
set r = d.OpenRecordSet ("SELECT * FROM MyTable WHERE <logical test> ORDER
BY <etc>")
set s = ActiveWorksheet

do while (not r.EOF)
' Assume Row, Col have been set correctly
s.Cells (Row, Col) = r!Field.text
call r.MoveNext
loop

call d.Close
set d = Nothing
set r = Nothing

Hope this helps
Dom

 

[Arbiel Perlacremaz]

Thank you for your help. I understand what you suggest, but I have some more
questions.

1) If I have a 10000-row by 10-column table, isn't the loop going to be very
slow ?

2) I think that writing and testing the queries in Access in easier done
than inside a macro. I understand that in can read all records of a table
with such a string as "SELECT * FROM myTable WHERE TRUE" but I may have to
access the results at the query level. How can I retrieve the query string
tuned up with Access and use it in the macro ?

"Dominic Olivastro" <[email protected]> a écrit dans le message de
[email protected]...
| In VBA, you don't really want to use the Access Object, but either DAO or
| ADO. The two are quite similar.
|
| Using DAO:
|
| dim r as dao.Recordset
| dim d as dao.database
| dim s as Excel.Worksheet
|
| set d = dao.opendatabase ("MyDatabase.mdb")
| set r = d.OpenRecordSet ("SELECT * FROM MyTable WHERE <logical test> ORDER
| BY <etc>")
| set s = ActiveWorksheet
|
| do while (not r.EOF)
| ' Assume Row, Col have been set correctly
| s.Cells (Row, Col) = r!Field.text
| call r.MoveNext
| loop
|
| call d.Close
| set d = Nothing
| set r = Nothing
|
| Hope this helps
| Dom
|
|
|
| | > Hi there
| >
| > I don't know whether I'm posting in the right group. If not, please tell
| me
| > where to post.
| >
| > I'm just trying to the exchange data between Access and Excel using VBA.
I
| > cannot figure out how I can select all records in a table or in a query
to
| > copy them and paste them in an Excel Worksheet. I've been able to drive
| > Access from Excel and have it "DoCmd.OpenQuery".
| >
| > I think I should use the "Recordset" objects, but there are no such
object
| > in the Access Object Model.
| >
| > Can I also update the SQL string of a query, as for changing the
starting
| > and ending dates of a run ? I've not been able to access the
"Properties"
| of
| > a query, even when it's open (I suppose I could read and write the
| property
| > "SQLStyring" or something comparable).
| >
| > Thank you very much to whom may help me.
| >
| > A newbie in Access (Access 2000)
| >
| > Arbiel
| >
| >
|
|

 

[Arbiel Perlacremaz]

Thank you for your advice. I will try the TransfertSpreadSheet method.

"Van T. Dinh" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
| You can do this using the Menu File / Export ... and select "Save As Excel
| {suitable version}" and use your Table / Query as the source..
|
| In the DatasheetView of your Table / Query, you can also use Tools /
Office
| Link ... / Analyze with Excel.
|
| In code, you need to use the TransferSpreadsheet method.
|
| --
| HTH
| Van T. Dinh
| MVP (Access)
|
|
|
|
| | > Hi there
| >
| > I don't know whether I'm posting in the right group. If not, please tell
| me
| > where to post.
| >
| > I'm just trying to the exchange data between Access and Excel using VBA.
I
| > cannot figure out how I can select all records in a table or in a query
to
| > copy them and paste them in an Excel Worksheet. I've been able to drive
| > Access from Excel and have it "DoCmd.OpenQuery".
| >
| > I think I should use the "Recordset" objects, but there are no such
object
| > in the Access Object Model.
| >
| > Can I also update the SQL string of a query, as for changing the
starting
| > and ending dates of a run ? I've not been able to access the
"Properties"
| of
| > a query, even when it's open (I suppose I could read and write the
| property
| > "SQLStyring" or something comparable).
| >
| > Thank you very much to whom may help me.
| >
| > A newbie in Access (Access 2000)
| >
| > Arbiel
| >
| >
|
|

 

[Dominic Olivastro]

--
Dominic Olivastro
ipIQ, Inc.

web: http://www.ipIQ.com
fax: 1-856-546-9633
voice: 1-856-546-0600 (ext 224)
email: (e-mail address removed)

Thank you for your help. I understand what you suggest, but I have some more
questions.

1) If I have a 10000-row by 10-column table, isn't the loop going to be very
slow ?

The size of the table does affect the time, but 10000 does not seem to be
very large at all. You can look at different cursortypes, for example, a
static record set takes less time than others, but you can not edit the
results.

2) I think that writing and testing the queries in Access in easier done
than inside a macro. I understand that in can read all records of a table
with such a string as "SELECT * FROM myTable WHERE TRUE" but I may have to
access the results at the query level. How can I retrieve the query string
tuned up with Access and use it in the macro ?

You do not need "SELECT * FROM myTable WHERE TRUE". You can simply use
"SELECT * FROM myTable". The "WHERE TRUE" clause does not add anything.

In access, you can use the grid to create a query as is usually done, but
then go to the "View ... SQL View" menu option to see the actually SQL
string used to create the query. You can copy and paste this into your
program.

However, the SQL string generated by Access is very efficient in the sense
that it is optimized, but it is difficuly to read and edit. For example,
you might find:
"SELECT [t_Table].[LName], [t_Table].[FName] From t_Table"

This is called "completely qualified". If you were typing it yourself, you
would just use:
"SELECT LName, FName from t_Table.

Hope this helps.

Dom

 

[John Nurick]

Wouldn't it be better to replace the loop with

s.Cells(1,1).CopyFromRecordset r

 

[Dominic Olivastro]

I always have trouble with the CopyFromRecordset method.

First, are you sure it will work with s.Cells(1,1), or do you need a range
that will perfectly fit the recordset in rows and fields? You need to do a
fair amount of coding just to get the range, and it seems easier to loop.

Second, some fields don't seem to copy for me, but maybe that is the more
complex datatypes, like objects.

Third, at some level isn't something looping? There's no free lunch.

In any case, that's been my experience.

Dom

--
Dominic Olivastro
ipIQ, Inc.

web: http://www.ipIQ.com
fax: 1-856-546-9633
voice: 1-856-546-0600 (ext 224)
email: (e-mail address removed)

 

[Van T. Dinh]

For your first question, you only need to specify the top-left cell for the
CopyFromRecordset. I have a few automation code using CopyFromRecordset and
I always specified only the top-left cell.

 

[John Nurick]

As Vinh says, you only need specify the top left cell. But it's not a
big deal to get the number of rows and columns in the recordset (and for
bombproof code you need to do it anyway to ensure that the recordset
will fit on the worksheet).

As Help for CopyFromRecordset says, "If the Recordset object contains
fields with OLE objects in them, this method fails."

Yes, I'm sure there are at least two levels of loop in the
CopyFromRecordset code, but I'd expect them to be significantly tighter
and faster than anything that can be achieved in VBA.

 

[Van T. Dinh]

Thank, Jurick <bg>

 

[John Nurick]

Thank, Jurick <bg>

Touche!