[RESOLVED] MYsql vb connection problem please help

[wizkid]

Hi,
I just installed mysql in my system. And i have created few tables. i just want to use the tables in my vb program
. so i am trying to connect mysql in my vb program
so i am trying different ways to try to connect mysql.

but i am not able to connect. do i need to change any settings?
This is the code i am using..

Private sub conn()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim constr As String

Dim db_name As String
Dim db_server As String
Dim db_port As String
Dim db_user As String
Dim db_pass As String

    db_name = "test"
    db_server = "localhost"
    db_port = "3306"    'default port is 3306
    db_user = "root"
    db_pass = "ttt"

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)
  
cn.Open constr

Exit Sub
failed:
  If Err.Number > 0 Then MsgBox Err & " : " & Err.Description, vbOKOnly + vbInformation, "sub"
End Sub

i tried this way as well..

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

but it is straight way jumping into the error catch failed and giving me error number err.Number -2147467259

can anyone help me please.
thanks

[techgnome]

The error number is useless... it's a generic database error. What does the error message say?

-tg

[si_the_geek]

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

[wizkid]

Hi, sorry for the late reply.
I have attached the image of the error. can u please check it.
"Data source name not found and no default driver specified". thanks

[techgnome]

sounds like you don't have the proper db drivers installed.
According to connectionstrings, http://www.connectionstrings.com/mysql#p30 , the second connection string there should work. If it isn't then you don't have the mySQL Driver installed.

There is also a 5.1 version of the driver... so make sure you know which one you are actually using (it changes the driver portion of the connectionstring.)

-tg

[wizkid]

Hi,
I am using Mysql server 5.1.AND I am using mysql control center 0.9.8 beta.
so i have changed driver to 5.1

constr = "Provider=MSDASQL.1;Password=;Persist Security Info=True;User ID=;Extended Properties=" & Chr$(34) & "DRIVER={MySQL ODBC 5.1 Driver};DESC=;DATABASE=" & db_name & ";SERVER=" & db_server & ";UID=" & db_user & ";PASSWORD=" & db_pass & ";PORT=" & db_port & ";OPTION=16387;STMT=;" & Chr$(34)

but same error

[techgnome]

1) The version of the server has nothing to do with the version of the driver... did you install the mySQL Connector at all? Installing the server doesn't do it automatically... it's a seperate download and install.
2) Don't use that connectionstring... use the other one:
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& " DATABASE=PERSONAL;" _
& "UID=root;PWD=root; OPTION=3"

-tg

[wizkid]

i just installed drivers from http://dev.mysql.com/downloads/connector/odbc/5.0.html
working great. Thanks for the help.