import data from SQL 7 to Access with VB

[Karin]

Hi there

I am looking for the easiest way of importing data from Sql Server 7 to Access with VB, tried to work with the DTS package object and stumbled. Is there a query syntax that makes this job easy?

Thanx

Karin

[ksw]

Have you tried Importing the data from Access? When you are on the Tables tab, right click anywhere in the white space and select Import... Since you are trying to connect to a SQL Server database, you will need to create an ODBC connection for Access to use. This will allow you to import entire tables.

If you only want selected data from the tables, then DTS would definately give you more options. When you say that you want to Export Data, you should get the option of building a query that would let you limit what records you want to pull.

You mentioned the DTS package object. Did you try to just select Export Data from right clicking in the tables folder of the SQL Server database? This will bring up the wizard so that you don't have to build a package from scratch.

Hope this helps.
--KSW

[Karin]

Thanks,

The import has to be controlled by a vb application. Activating an existing DTS package seemed the easiest, but for some reason it does not work.
Creating linked servers on SQL and a distributed query would be the next best thing, but it would add to the "things to do when implementing"

Cheers
Karin

[ksw]

Sorry, I missed the part where you said "with VB". You should be able to execute a DTS package from VB. One of the programmers I work with has done it. He did find that there are some odd things that had to be set such as the ExecuteInMainThread property must be set to True for each step in the package. If this hasn't been set, VB will crash. This can be done in code or in the DTS Designer (WorkFlow Properties, Options Tab, Execution Section, the Checkbox labeled "Execute on main package thread")

Also, if you have your DTSPackage variable declared WithEvents, then you need to have the stubs of the following subroutines in your code even if you don't add any of your own code to them. Replace DTSPackage with your variable's name

DTSPackage_OnError
DTSPackage_OnFinish
DTSPackage_OnProgress
DTSPackage_OnQueryCancel
DTSPackage_OnStart

These are most likely querks that come from the fact that DTS is so new (brand new to SQL Server 7, in fact).

Hope this help you out some.

--KSW