VB and MYSQL connection

[jad73]

Forgive me if this is a simple question as I have never used MYSQL before. I want to connect to MYSQL through VB. What do I need to do to make this work. I have installed mysql and the myodbc 3.51 on my server. Now I am at my client machine doing some vb code. Lets say the server address is 192.0.0.1 and the username is user and the password is password. This is what I have for the connection code.

strConnect = "Driver=mySQL;Server=192.0.0.1;Option=147992;Database=amav_ops;Uid=user;Pwd=password;"
cn.Open strConnect

I get a runtime error when this is executed.. -2147467259(80004005) [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

Again I am really new to this, so any help will be appreciated, im sure its just something simple that i am missing.

Thanks,

-J

 

[zemp]

Check out this link for connection strings of all kind, including MySQL.

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

Link originally provided by johnwm.

Thanks and Good Luck!

zemp

 

[jad73]

I've tried that and still get the same error message, is there some setting that i should be doing on the server end?

 

[zemp]

Have you tried with the actual server name and not the IP address?

Thanks and Good Luck!

zemp

 

[jad73]

yup, no luck

 

[zemp]

The only thing that I see different, from your connsection string and the one at able-consulting, is that you don't have braces (curly brackets{}) around the driver and your option number is different.

oConn.Open "Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"


Try playing with those.

Unfortunatly, I have not had the oppurtunity to work with MySQL. So this is about as far as I can go with any help for you, sorry. Someone else should pick up the slack.

Thanks and Good Luck!

zemp

 

[jad73]

I actually changed to their code, still nothing though. Oh well, im sure i'll figure it out eventually Thanks anyways

 

[zyrag]

if you are using MyODBC 3.51 try this:

objConn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=ServerName;"_
& " DATABASE=DBName;"_
& "UID=username;PWD=passwd; OPTION=3"

objConn.Open

OR, test if you can connect through DSN

objConn.Open "DBName", but first setup the DSN correctly including the appropriate OPTIONS.


Make sure that the username is allowed, or has the right to connect to the specified DBName.

 

[jad73]

I dont know how to set up DSN

 

[Schroeder]

You don't need MyODBC on the server. You only need it on the clients that want to connect to the server. You will have to set up the DSN though.
In the control panel of the client machine, find ODBC Data Sources. In XP (and maybe 2K?) its under Administrative Tools. In the ODBC Data Source Administrator window, pick either the User DSN (DSN will be available to the current user) or System DSN (DSN available to all users) tab and click Add.
In the Create New Data Source window, look for your MySQL driver and click Finish.
Data Source Name is the DSN you will use in your connection string. The rest of the information is fairly self-explanatory.

I've had trouble with v3.51. Had no trouble with an older version - 2.50 I think.

 

[jad73]

i figured it out, just before you wrote. It was because i didnt have the myodbc on the clients...duh..lol Thanks, guys
and gals.

 

[zyrag]

schroeder, i'm currently using MySQL 4.0.12 and MyODBC 3.51.06 and haven't encountered a trouble using a DSN-less connection.

 

[jad73]

Im not using DSN either.

 

[Schroeder]

Its been a while but I believe my troubles were password related. It seemed that I was able to make one good connection then every time thereafter I ran into security issues. I was only using a DSN though.. never tried just using a "direct" connection.

I don't remember doing a very scientific analysis of my problems so I can't offer any real evidence. It was the first time I'd ever used both MySQL and MyODBC so it could have just been me. Suffice to say, things worked the way I thought they should with the older driver but not with the new one.