dcsimg
Results 1 to 11 of 11

Thread: Import mdb to SQL Srv using VB6

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2010
    Location
    Far East
    Posts
    165

    Import mdb to SQL Srv using VB6

    Hi experts

    I just installed SQL Server Express 2017 and was able to create a database in MyPc/SQLX using following ADODB.Connection.

    cnn.ConnectionString = "Provider=SQLOLEDB.1" & _
    ";Integrated Security=SSPI;Persist Security Info=False;" & _
    "Data Source=MyPC\SQLX"

    cnn.Open
    rst.Open "CREATE DATABASE NewTbl", cnn ‘where rst is ADODB.Recordset

    I can also import a .mdb using the SQLSrv import/export interface which use OLE DB Provider for SQL Server.

    I hope YOU EXPERTS can help by showing the VB6 codes to do the import instead of using the interface. I am sure you guys who know how as it should be very straight forward. Thank you first for your help which will certainly make my learning super fast.

  2. #2
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,570

    Re: Import mdb to SQL Srv using VB6

    I use this tool to migrate (structure and data) from MDB to SqlServer:
    Access to MSSQL
    http://www.bullzip.com/products/a2s/info.php

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2010
    Location
    Far East
    Posts
    165

    Re: Import mdb to SQL Srv using VB6

    Gibra Thank you for sharing the tool. I am actually looking for VB6 codes so that i can put into an application.

    Can you you indulge?

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    May 2010
    Location
    Far East
    Posts
    165

    Re: Import mdb to SQL Srv using VB6

    Gibra Thank you for sharing the tool. I am actually looking for VB6 codes so that i can put into an application.

    Can you you indulge?

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Import mdb to SQL Srv using VB6

    it should be very straight forward
    I doubt it. I imagine it will actually be a lengthy and complex development.

    You're going to have to identify all the tables you want to import, access the schema for each to create a corresponding table in the target DB, create referential integrity and any programmed constraints, sprocs, functions, views etc... all before you even think about moving the actual data. Oh yeah, and you're going to have to respect dependency order throughout the whole process.

    Realistically the complexity is going to be pretty much exactly the same as if you were to use the import wizard or some other ETL package except that they're already offering you a bunch of GUIs to make your life easier.

    Why do you want to do this in VB6 anyway? That sounds like you're creating a large and painful rod for your own back.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    May 2010
    Location
    Far East
    Posts
    165

    Re: Import mdb to SQL Srv using VB6

    I imagine so as sql server function went through table & create a similar then import the data of each

    I was motivated by the idea that we can create table using template. With in depth understanding of complex template Iooking a short cut

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    May 2010
    Location
    Far East
    Posts
    165

    Re: Import mdb to SQL Srv using VB6

    I imagine so as sql server function went through each table & create a similar then import the data of each

    I was motivated by the idea that we can create table using template. Without in depth understanding of complex template I was Iooking a short cut

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    May 2010
    Location
    Far East
    Posts
    165

    Re: Import mdb to SQL Srv using VB6

    From another angle

    How to create a copy of a .mdf already in the sql Server. That should be easy right?

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    May 2010
    Location
    Far East
    Posts
    165

    Re: Import mdb to SQL Srv using VB6

    Now i am thinking for using a scripting tool to script the mdf.

  10. #10
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,570

    Re: Import mdb to SQL Srv using VB6

    Quote Originally Posted by FuzMic View Post
    Gibra Thank you for sharing the tool. I am actually looking for VB6 codes so that i can put into an application.
    Too complex and useless since there are already tools for this purpose.
    In any case, you should search for somes languages and tools for data access like: SQL, DDL, DML, ADOX, and so on...
    Good luck.

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: Import mdb to SQL Srv using VB6

    How to create a copy of a .mdf already in the sql Server. That should be easy right?
    Yes, that's very easy. Your mdf file is already available on your disk so you just take a copy, same as you would with any other file in windows.

    If you don't know where the file is, open management studio. Right click on the database in question and select properties. Go to the Files tab and check the Path - that's where your mdf file is going to be.

    Note that it's not a great idea to start copying the database while it's in use. there's actually two files that describe the state of the database (mdf and ldf) and if they're out of synch your copy may be corrupt. The easiest thing to do to avoid this is to Detach the database first. In Management Studio, right click on the database in question, go to Tasks then to Detach. Make sure you've made a note of the path to the mdf first so you know where to go to reattach it. The Attach option can be found by right clicking on the "Databases" node.

    You can also use BackUp and Restore to effectively take a copy and that may be better depending on what your goal is. There's also an option to script an entire database.

    Speaking of which, what really is your goal? I ask because you've started by saying you need to migrate a mdb access db into sql server, now your talking about taking a copy of an existing sql server database which is a completely different proposition. The chances are that there's already a good solution for what you want to do and the advice I'm giving you here may not be the best for you.

    What is the overall problem your trying to solve and why? Don't give us your proposed solutions because it's likely there will be a better solution that we'll miss if we focus on your proposal. So give us the problem and the background of why you need to solve it.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width