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.
littlemamaAsked:
Who is Participating?
 
nisheedConnect With a Mentor Commented:
Here my opinion:

I think you can make the query run faster using ADODB, getrows and raw SQL.

Raw SQL is faster.

So this is what I would do.

1). Get the records from SQL Server in an ADODB.Recordset, disconnect it.
2) Call getrows which returns a two dimensional array.
3). Construct the insert statement by looping through the array and creating a string.
4) Connect to access and passing the query String.

Take a look at this it might give you a rough idea.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/comsrv2k/htm/cs_sp_profileobj_bzmp.asp

http://members.evolt.org/help/caspdoc/html/ado_recordset_object_getrows_method.htm




0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
littlemamaAuthor Commented:
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nisheedCommented:
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
0
 
littlemamaAuthor Commented:
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.


0
 
littlemamaAuthor Commented:
nisheed, thanks will check this out.
0
 
littlemamaAuthor Commented:
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?


0
All Courses

From novice to tech pro — start learning today.