asked on

sql to access in vb 6 code

I am writing code to select data from sql database to put into an access database. I know I can create two datasets and read from sql and write to access. Is there a way to call a the export wizzard from enterpise manager from vb? Is my first method best way to do this? there are 18k records and it will continue to grow monthly. I need to make sure it will run quickly.

Thanks for your opinion and help.

Guy Hengel [angelIII / a3]

You could use a DTS package that you defined from VB. I assume that you now have configured such a package, in VB the code would be:

dim p as DTS.Package
set p = NEW dts.package
p.LoadFromSQLServer 'servername' , 'packagename' etc...
p.Execute
set p = nothing

The DTS packages can be defined using the Enterprise manager, and be stored in the SQL Server, or also in a file (which would then make the above code to be changed into LoadFromFile ...

CHeers

littlemama

ASKER

No I haven't configured a DTS package. I guess I would use loadfromfile. People here are rather picky as to what goes up on the server ... could you give me more details or example ... I take it I need to include a new component in vb in order to use dts.package? This is a start .... any other help would be greatly apprecieated.

thanks for your help.

nisheed

Hi

You'll need to use the SQL DMO (sqldmo.dll which sits in the \Tools\Binn directory under
your Microsoft SQL Server installation).

Take a look at the following article.

http://www.databasejournal.com/features/mssql/print.php/10894_1494301_3

littlemama

ASKER

I tried out the DTS package setup. There is an option to save as vbcode. Is this good to do or not? If so then I think I can make this work and adapt that code to fit my needs. Will this take more time or is it not allowed? I am new to this part of vb, I;ve never done this before. Please let me know what you think. Thanks for your help.

littlemama

ASKER

nisheed, thanks will check this out.

littlemama

ASKER

nisheed, and angelll, I think this will work but I still need answer to my other question. Is saving package as vbcode a good thing to do or will results be the same as reading dataset in sql and writing in access??? I need to understand which is the best way to do this ...

thanks for any input i think i would like to give both of you half the points .... how would i do this?

ASKER CERTIFIED SOLUTION

nisheed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial