Transform data from sql 2000 to access 2000

[xbcnn]

Hi, all

I have an application with sql 2000 as back database. My problem is How can I easily transform all tables in a database into an Access file by code not by DTS wizard. cause I want to save all data to my custom by this way. So is there an easy way to take out all these data in vb code(maybe other program language)?

Any other opion is welcome.

thanks.

[skhanal]

Many ways.
1. Create a DTS package and call it from command prompt using DTSRUN.exe

2. Create a DTS package and write a program in VB to execute it.

3. Write a program in VB and use SQLDMO to get the data out.

[Rawhide]

4. Create linked tables in Access to the SQL Server tables.

5. Use bcp

[xbcnn]

Really thanks for all the suggestions.

But I'm not professional with DTS ,so could you direct me more about it,such as a good web page,or even some sample codes maybe much more helpful.

another question,if I have an existed access file,may i use bcp to copy data into it ?
and I like to know more about use SQLDMO way,but how to make it?

Thanks again.

[Rawhide]

Do you have the Books Online installed? It should be in your SQL Server program groups with your other client tools like Enterprise Manager and Query Analyzer. Books Online (often referred to as BOL in groups like this one) is always the first thing I reference if I need to look somethign up.

In your case, I think that DTS is the best way to go. It has a graphical designer that you can use to create a package. You can save the DTS Package and call the DTSRun command line utility to execute it.

You can also save the completed DTS Package as a VB file, and most of the VB code will be written for you.

[xbcnn]

If I transfer more than one database,do I have to create several defferent packages by DTS designer or the DTS wizard?

Can I create one and specify the database name via a variant?

Thanks.

[Rawhide]

Yes, you can do it with just one package. You can use global variables in DTS Packages and pass in a new value for the global variable or variables when you execute it.

For example, if I wanted to execute a package named "My Package" on server "My Server" using a trusted connection and passing in a database name of "MyDatabase" as a new global variable, my execute string would look like:

dtsrun.exe /S "My Server" /N "My Package" /A "MyDatabase" /E

[xbcnn]

Thanks Rawhide.

Do you any suggestion on create DTS package? Do I have to use DTS wizard?

By the way,skhanal mentioned use SQLDMO, Do you think it possible? And your bcp method,I use it transfer to a mdb file,but I can not open it?

Any way, thanks for your support.

[Rawhide]

bcp exports to a text file. So if you had it bcp to a file with an extension of .mdb, it still exported a text file.

Use the DTS Designer to create your DTS Package. In Enterprise Manager:

1. expand the server
2. expand Data Transformation Services
3. Right-click on Local Packages and click on New Package

As far as SQLDMO, I have to admit that it's not my forte.

[Achilles]

Although not specifically what you require, I thought maybe it would be somewhat helpfull in getting an idea of DTS. HERE

Hope that helps.

Cheers.

[xbcnn]

Thanks,Achilles

It's helpful.

But I have to design the data Transfering tasks,so I must know all about the database,such as how many tables it contains, and the table's schema....

If the table's schema is changed,the old package is useless. So it lack of something like universal use. Can I create one script for all database ?
Like bcp tool,I pass a database name and a destination mdb file name, then it transfer all data automatically.