Results 1 to 11 of 11

Thread: VB6 --> mySQL connection

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2009
    Posts
    6

    VB6 --> mySQL connection

    HI all,
    For some reason it's very hard to find over the web a simple example for a connection to mySQL from VB6. There are lots of links to places, parts of code, .dlls etc ...

    Does anyone know of a full code to connect to mySQL db, get some lines from a table, and show them on screen ?

    Thx

  2. #2
    Frenzied Member
    Join Date
    Mar 2009
    Posts
    1,182

    Re: VB6 --> mySQL connection

    Option Explicit should not be an Option!

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB6 --> mySQL connection

    Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

    The link above is important, but will get you one part of the puzzle.

    To actually make use of it you will need code to go with it. You can find that in the "Classic VB: ADO" section of our Database Development FAQs/Tutorials (at the top of this forum).

    I recommend starting with the "ADO Tutorial", which explains the code needed (as well as providing the link that vb5prgrmr gave, and explaining how to use it), and gives you a full program to interact with a database.

  4. #4
    Hyperactive Member
    Join Date
    Apr 2009
    Posts
    364

    Re: VB6 --> mySQL connection

    You need to put this on the beginning of the app
    Code:
    Dim db_name, db_server, db_port, db_user, db_pass, constr As String
    And implement to the VB code couple of "things" like that:

    Code:
    Private Sub ConnServer()
      'connect to MySQL server using MySQL ODBC 3.51 Driver
      Set conn = New ADODB.Connection
      conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                            & "SERVER=localhost;" _
                            & " DATABASE=PERSONAL;" _
                            & "UID=root;PWD=root; OPTION=3"
    conn.Open
    End Sub

    Code:
    Private Sub OpenServer() 'Connect MySQL Server Without ODBC setup
        constr = "Provider=MSDASQL.1;Password=;Persist Security Info=True;User ID=;Extended Properties=" & Chr$(34) & "DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=" & db_name & ";SERVER=" & db_server & ";UID=" & db_user & ";PASSWORD=" & db_pass & ";PORT=" & db_port & ";OPTION=16387;STMT=;" & Chr$(34)
        Set conn = New ADODB.Connection
        conn.Open constr
    End Sub

    Code:
    Public Sub OpenConnection1()
    
        On Error GoTo DBerror
        db_name = "personal"
        db_server = "localhost"
        db_port = "3306"    'default port is 3306
        db_user = "root"
        db_pass = "root"
        'ConnServer ' Open with ODBC in Control Panel
        OpenServer ' Open without ODBC in Control Panel
        Rx = 0
        ShowData
        ShowGrid
        Exit Sub
    DBerror:
    
        ShowData
        ShowGrid
    
    End Sub

    And of course showdata an similar definitions.

  5. #5
    Hyperactive Member kxcntry99's Avatar
    Join Date
    Jun 2006
    Location
    Pennsylvania
    Posts
    342

    Re: VB6 --> mySQL connection

    Take a look at beacon's ado tutorial...it was worth its weight in gold when I started with vb6 and mysql
    Microsoft Office Integration:Useful Database Links:
    Connection Strings


    Im a pogramar
    Iam a programer
    I’m a programor

    I write code!

  6. #6
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: VB6 --> mySQL connection

    Avoid using code like this
    Code:
    Dim db_name, db_server, db_port, db_user, db_pass, constr As String
    In VB6, each variable MUST be explicity declared or it will be created as a Variant. In the above, only constr is actually a string. To be accurate, the line would have to be
    Code:
    Dim db_name As String, db_server As String, db_port As String, db_user As String, db_pass As String, constr As String

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2009
    Posts
    6

    Re: VB6 --> mySQL connection

    thx guys.
    So with the ADO, i tried to connect to my sql server (5.1), after configuring a DSN for it, using ODBC 3.51 driver.

    with this code -
    With rs

    .Open sqlStr, conn, adOpenKeyset, adLockPessimistic, adCmdTable


    I got the famous, not really solved on Google, error -
    odbc driver does not respond to requested properties

    any ideas ?

  8. #8
    Hyperactive Member kxcntry99's Avatar
    Join Date
    Jun 2006
    Location
    Pennsylvania
    Posts
    342

    Re: VB6 --> mySQL connection

    what does your connection string look like???
    Microsoft Office Integration:Useful Database Links:
    Connection Strings


    Im a pogramar
    Iam a programer
    I’m a programor

    I write code!

  9. #9

    Thread Starter
    New Member
    Join Date
    Jun 2009
    Posts
    6

    Re: VB6 --> mySQL connection

    Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=mydatabase; User=mydatabase;Password=mydatabase;Option=3;

    im connecting to mysql.
    anyways, when i removed the 'adCmdTable' from the end of -
    .Open sqlStr, conn, adOpenKeyset, adLockPessimistic, adCmdTable
    so it all started working ...

  10. #10
    Hyperactive Member kxcntry99's Avatar
    Join Date
    Jun 2006
    Location
    Pennsylvania
    Posts
    342

    Re: VB6 --> mySQL connection

    Glad to hear you got it working.

    if you are passing a full sql statment to the connection you might need to replace adCmdTable with adCmdText.

    If this issue is closed please mark the thread resolved
    Last edited by kxcntry99; Jun 22nd, 2009 at 09:08 AM.
    Microsoft Office Integration:Useful Database Links:
    Connection Strings


    Im a pogramar
    Iam a programer
    I’m a programor

    I write code!

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: VB6 --> mySQL connection

    adCmdTable should only be used when the "sql" string is jsut a table name. ex: tblCustomers
    adCmdText should be used when the sql string is an actual statement. ex: SELECT * FROM tblCustomers
    adCmdStoredProcedure should be used when the sql string is a sproc name. ex: prc_Customers_Get

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Click Here to Expand Forum to Full Width