sql to access in vb 6 code

Posted on 2003-03-10
Medium Priority
Last Modified: 2008-02-01
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.
Question by:littlemama
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8102599
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...
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 ...


Author Comment

ID: 8102720
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.

Expert Comment

ID: 8102883

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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8102893
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.


Author Comment

ID: 8102943
nisheed, thanks will check this out.

Author Comment

ID: 8103993
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?


Accepted Solution

nisheed earned 300 total points
ID: 8105028
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.




Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Simple Linear Regression

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question