Connecting to MySQL server error...

[whodaman]

I cannot seem to connect with my Server. I suspect it is the wrong connection string -> DRIVER={MySQL ODBC 3.51 Driver}. I am using MySQL 3.23.41 on the webhoster. Is this the right connection string to use?

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _ 
            & "SERVER=127.0.0.1;" _ 
            & "DATABASE=test;" _ 
            & "UID=testuser;" _ 
            & "PWD=12345;" _ 
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 
    
    conn.Open   'Error Here!!!
    
    conn.BeginTrans
    
    rs.Open "select data from ppData where yearMonthID = 1 and x = 0 and y = 0;", conn, adOpenStatic, adLockOptimistic, adCmdText
    rs.Delete
    rs.Close
    
    conn.CommitTrans
    
    conn.Close
    Set conn = Nothing
    Set rs = Nothing

I get the error "Data source name not found and no default driver specified".

Thanks ahead of time.

 

[zyrag]

try to minimize your ODBC option as presented below and see if it can open you a connection.

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=User;PWD=Passwd; OPTION=3"
conn.Open

Check also the compatibility issues between MySQL 3.23.xx when using MyODBC 2.5 against using MyODBC 3.51.06. I believe MyODBC is optimized for MySQL 3.23.xx rather than with MyODBC 3.51.06

 

[zyrag]

OR try establishing a connection using DSN and see if you can open a connection to the server.

i found it less headache also if your system uses VB6 SP5 and MDAC 2.6 installed with MyODBC 3.51.06 to connect to MySQL Server 4.x.

 

[whodaman]

Zyrag, I tried using the shorter string as you gave me. It still gave me the same error. I'm really not too sure. Your second post is slightly over my head. Are you saying i'm using too old a version of MySQL server for the "MySQL ODBC 3.51 Driver"?

Thanks you for your help

 

[SonOfEmidec1100]

Ive never used MySQL but for connection string probs I always look here first;

http://www.able-consulting.com/ADO_Conn.htm

Maybe it can help you

 

[zyrag]

whodaman,
not exactly... i mean, feature to feature specifically and little on ODBC options in the context of working with VB. But, establishing connection should work normally. Have you tried configuring a DSN connection with the ODBC configurator in your Control Panel? Test with it and see if you can succesfully connect to the server.

 

[whodaman]

Hi all, thank you for all your help. I just realized that I need to install the MyODBC driver. I had no clue I had to install it.

I was hopeing that VB could simply connect to this without this driver. This would make life easier if you are distrubuting software. It would be one less installation step.

Thanks again for everyone's help.