Hi All,

Is there any way to get all tables (and all fields in each table) into
excel automatically without having to manually enter all table names
and field names into SELECT queries?

Ideally, one sheet per table with the sheets named with the table
names, but anything close would be good.

Thanks,

Alan.


--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

It is possible but would require some pretty intense coding which I don't
have time to work out right now. But the key would be using ADO to connect
to the database and then you can use the OpenSchema method to read the names
of the tables. You could iterate through these in a loop, append a worksheet
to your workbook, and then use standard ADO recordset methods to retrieve the
data from each table (SELECT * FROM TABLENAME should be sufficient). You
could get column headers, if desired, from the Field.Name property (iterating
through the fields).

Sorry I can't do all the details, but for info on reading the database
schema see this:
http://msdn.microsoft.com/library/de...openschema.asp

If you need info on ADO methods in general:
http://msdn.microsoft.com/library/de...troduction.asp
--
- K Dales


"Alan" wrote:


Hi All,

Is there any way to get all tables (and all fields in each table) into
excel automatically without having to manually enter all table names
and field names into SELECT queries?

Ideally, one sheet per table with the sheets named with the table
names, but anything close would be good.

Thanks,

Alan.


--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address