Hi

Sorry if this is a FAQ, I tried to search the archives but was unable
to find it.

I need to dynamically export all tables and views from SQL Server to
Access. The Access database do not contain the table so this has to be
dynamically created, no transformation should be done.

It could be several tables or one single it will be scheduled so there
is no option like creating a manual export task as there may be changes
in the table structure.

I tried to solve my problem using SELECT INTO from SQL but since this
seems to not work using a linked server in the INTO clause.

I also looked at using the DTS Bulk export task from
http://www.sqldts.com/default.aspx?272 but this only exports to txt and
csv.

Thanks
Si

Hello Si,

Ok so one way to do it would be to read the tables collection in Access and
compare to whether that table is already in SQL Server. if it is not then
you read the metadata about that table from access and generate a CREATE
TABLE statement for SQL Server. You then need to dynamically build the transformations
between the Access table and the SQL Server table.

You will only ever have 1 * Transform Data task and you regenerate the definition
every time.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello Allan,
Thanks for your answer maybe I was a bit unclear it is from SQL to
Access but I guess that I need to do something similar. I was looking
for pointers on how to do it, you know code that you do not have to
write is good code. :-)

Thanks
Si