Questions

Exporting SQL Datatbases to Access and Dbase programmatically in Vb .net

Tags:
+
0 Votes
Locked

Exporting SQL Datatbases to Access and Dbase programmatically in Vb .net

nosa2
How do you export SQL 2000 Databases into Access and Dbase by means of using Visual Basic .Net and C# programs?

Nosa
nosa2@juno.com
  • +
    0 Votes
    Tony Hopkinson

    Basically you read it in from one and write it to the other.

    Google ADO.Net, but this will be fragile, limited and above all hideously slow and a severe network hog.

    Why don't you just export it from one and imprt it to the other. Lot of re-inventing of wheels in this.

    Probably not very round ones as well.

    Look at DTS.

    +
    0 Votes
    nosa2

    I want to do both the structure and the Data. I have used DTS to perform this function. All I really want to do is for the users of my program to be able to export the SQL databases that my program is running on to Access and Dbase. I have been able to provide the "export to Excel" option in the program, from searching through forums like this.

    +
    0 Votes
    Tony Hopkinson

    but really really painful.
    A heck of a lot of work.
    Discovering the schema (unless you are 'hard coding it, giving you versioning problems), then translating it. If your database is simple and you don't need things like user/ ciolumn permissions sp, triggers et al. (and never will) then it's just donkey work.

    You can access the table meta data (Column names and types) potentialy translate, then creteat the table, then the data (pssibly having to condition that as well.

    What you are doing though is reinventing DTS.
    How about just calling the DTS package?

    +
    0 Votes
    nosa2

    Thanks for your help and input. How do you call DTS Package programmatically without user?s interaction with the DTS Package wizard? When I did the DTS Package, I told it to save it as a VB file, I went in to try to run the program. There were so many errors and with my level of programming skill I gave up trying to make it work.

    Believe me, the last thing I want to do is reinvent the wheel. If you can show me an easy way of interacting with the DTS through the use of codes, I will greatly appreciate it and I will put this nagging headache to rest. Once more, thanks for your help.

    Nosa

    +
    0 Votes
    Tony Hopkinson

    it'ss a wee app that come with DTS you just give it the package saved as a file and off it goes.
    You'll need to look up ShellExecute and VB if you haven't run another file from VB before.

    +
    0 Votes
    nosa2

    Thanks for your reponse.
    I created the dts package and saved it as a package file.

    In vb .net I called the package with the Shell command like you suggested, I got an error message. I called it thus: Shell("TestPakcage.dts").

    When I call the package from the SQL Enterprise Manager using the Data Transformation Services to open the package, it works. Is there a way of calling the DTS to open the package from vb?

    Nosa

    +
    0 Votes
    cballinger

    What you are describing is the perfect fit for ETL (Extract/Transform and Load). The tool I've used to do this type of work since 1998 was Data Junction (Now owned by Pervasive) and now called Data Integrator. Even if it involves data transformation between SQL 2000 and Access or DBASE it is much, much faster than any VB or C++ developer could ever be and load/run time is measured in seconds, even on a PC workstation. Check out their web site at www.pervasive.com and look for Data Integration tool for more info. Using connectors you can go from many different sources to many different targets in record time and perform a vast array of data transformations if necessary.

  • +
    0 Votes
    Tony Hopkinson

    Basically you read it in from one and write it to the other.

    Google ADO.Net, but this will be fragile, limited and above all hideously slow and a severe network hog.

    Why don't you just export it from one and imprt it to the other. Lot of re-inventing of wheels in this.

    Probably not very round ones as well.

    Look at DTS.

    +
    0 Votes
    nosa2

    I want to do both the structure and the Data. I have used DTS to perform this function. All I really want to do is for the users of my program to be able to export the SQL databases that my program is running on to Access and Dbase. I have been able to provide the "export to Excel" option in the program, from searching through forums like this.

    +
    0 Votes
    Tony Hopkinson

    but really really painful.
    A heck of a lot of work.
    Discovering the schema (unless you are 'hard coding it, giving you versioning problems), then translating it. If your database is simple and you don't need things like user/ ciolumn permissions sp, triggers et al. (and never will) then it's just donkey work.

    You can access the table meta data (Column names and types) potentialy translate, then creteat the table, then the data (pssibly having to condition that as well.

    What you are doing though is reinventing DTS.
    How about just calling the DTS package?

    +
    0 Votes
    nosa2

    Thanks for your help and input. How do you call DTS Package programmatically without user?s interaction with the DTS Package wizard? When I did the DTS Package, I told it to save it as a VB file, I went in to try to run the program. There were so many errors and with my level of programming skill I gave up trying to make it work.

    Believe me, the last thing I want to do is reinvent the wheel. If you can show me an easy way of interacting with the DTS through the use of codes, I will greatly appreciate it and I will put this nagging headache to rest. Once more, thanks for your help.

    Nosa

    +
    0 Votes
    Tony Hopkinson

    it'ss a wee app that come with DTS you just give it the package saved as a file and off it goes.
    You'll need to look up ShellExecute and VB if you haven't run another file from VB before.

    +
    0 Votes
    nosa2

    Thanks for your reponse.
    I created the dts package and saved it as a package file.

    In vb .net I called the package with the Shell command like you suggested, I got an error message. I called it thus: Shell("TestPakcage.dts").

    When I call the package from the SQL Enterprise Manager using the Data Transformation Services to open the package, it works. Is there a way of calling the DTS to open the package from vb?

    Nosa

    +
    0 Votes
    cballinger

    What you are describing is the perfect fit for ETL (Extract/Transform and Load). The tool I've used to do this type of work since 1998 was Data Junction (Now owned by Pervasive) and now called Data Integrator. Even if it involves data transformation between SQL 2000 and Access or DBASE it is much, much faster than any VB or C++ developer could ever be and load/run time is measured in seconds, even on a PC workstation. Check out their web site at www.pervasive.com and look for Data Integration tool for more info. Using connectors you can go from many different sources to many different targets in record time and perform a vast array of data transformations if necessary.