Export to Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Export to Access

Author

Topic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-07 : 06:25:00

The follwing is used to export sql server 2000 table to access

INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="D:\Test.mdb";
User ID=Admin;Password=')...SQL(no,n,x)
SELECT * FROM tt

However it is required to create that table in Access. The above will export the data to access table. How can I modify the above to create table and export data?

Madhivanan

Failing to plan is Planning to fail

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-09 : 01:00:36

create the table before you insert into it.

-ec

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-09 : 01:03:53

I want to know how to create a table in Access using SQL Server2000 query

Madhivanan

Failing to plan is Planning to fail

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-09 : 01:25:36

I don't think DDL is allowed through OPENDATASOURCE or OPENROWSET functions.

just pre-create the table.

-ec

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-09 : 01:30:16

I am trying to export all the tables from SQL Server2000 to MS Access. Thats why I need that query.

Madhivanan

Failing to plan is Planning to fail

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-09 : 01:43:31

quote:
Originally posted by madhivanan

I am trying to export all the tables from SQL Server2000 to MS Access. Thats why I need that query.

Madhivanan

Failing to plan is Planning to fail

pre-create the tables. You can't create them from your query. Use DTS as an alternative.

-ec

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-09 : 01:57:47

In DTS I am able to export only one table at a time. I want to export all the tables in a Database.

Madhivanan

Failing to plan is Planning to fail

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-03-09 : 05:22:53

DTS will allow you to export more than one table at a time

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources