MySQL VB ADO connection with mySQLProv

I have developed a program in VB6 connecting to an SQL server using ADO and the OLEDB provider. Now I want connect to a mySQL server. The only option next to using ODBC is by my knowledge mySQLProv. I have 2 problems with this provider:
1) When I use the connection.Open with the following connection string (or any other):
         connection.Open "Provider= mysqlprov;" & _
             "Server = Localhost" & _
             "DataBase= myDB;" & _
             "Uid = myUser;Pwd = myPW;"
 I get a popup screen "MySQL data source Name Set up" where I have to fill in the same data. Is there any way around this?

2) When I fill the data I am able to connect to the database but I am getting some problems with SQL statements that do work in mySQL directly but not via the connection:
a) tablenames longer than 19 characters generate an error
b) JOIN expressions do not seem to work?
Can anybody shed some light on this?
Thanks in advance,
Jurriaan
J_codeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
samtyfConnect With a Mentor Commented:
Why not have a try on the MySQL ODBC Connector 3.51? Download it from here

http://dev.mysql.com/downloads/connector/odbc/3.51.html

The code in VB is

Connection.Open "Driver={MySQL ODBC 3.51 Driver};Server=10.10.10.10;uid=userid;pwd=userpasswd;database=databasename"

Regards,
Sam Tan
0
 
leonstrykerCommented:
Try this:

oConn.Open "Provider=MySQLProv;" & _
           "Data Source=mySQLDB;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"

and take a look here:

http://www.coveryourasp.com/Snippet.asp?snip=16

Leon
0
 
leonstrykerCommented:
MySQL Reference Manual:

http://dev.mysql.com/doc/mysql/en/index.html

Leon
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
J_codeAuthor Commented:
Still getting the popup screen. I have tried several connection strings, when I enter a datasource the fields 'datasource' and 'Database' are filled with the data source name (see code below), otherwise fields are empty.
         connTraceLab.Open "Provider= MySQLProv.2.5;" & _
             "Server = Localhost; Data Source = myDB;" & _
            "DataBase= myDB;" & _
             "User Id = myUser;Password = myPWD;"


The reference manual doesn't really answer my questions. There is no mention of mySQLProv and the only reference to OLEDB is: "If you have problem with MyODBC and your program also works with OLEDB, you should try the OLEDB driver. "

It is important for me to know whether 'JOIN' expressions can be used with mySQLProv, if this is not possible I can quit this exercise...
0
 
J_codeAuthor Commented:
Thanks Sam, however I am not so keen on using ODBC for a multi user environment..

Anyway I found a new version of myOLEDB (mysqlprov 3.0) which solves part 2 of the problems mentioned above. JOIN and tablename length are no longer a problem!

The pop up screen still appears when connecting however. It seems I am not able to fill the mySQL host from the connection string (Username and password are filled now and as dataSource '[Default]' is entered). Any suggestions?

Jurriaan

0
 
J_codeAuthor Commented:
Problem solved!
The following connection string worked:
         conn.Open "Provider= MySQLProv.3.0;" & _
             "Data Source = myDB; " & _
             "Database = myDB;" & _
             "User Id = myUser;Password = myPW; option = 3"
Thanks for helping.
Jurriaan
0
 
J_codeAuthor Commented:
It turns out that the myOLEDB is not so useful for me after all. Transactions and single row locking are not supported and apparently the myOLEDB is no longer in development (see
http://www.experts-exchange.com/Databases/Mysql/Q_20732567.html)

So I will go with Sams' suggestion and try the myODBC. One question though. Do you have to install myODBC.exe on each client or can you just use a dll?

J.
0
 
samtyfCommented:
Hi Jurriaan

So far, i'm installing the myODBC in each client machine. But i'm not sure it can or cannot to just use a dll without the myODBC Installation. :)

Regards,
Sam Tan
0
 
J_codeAuthor Commented:
OK, since I am using the myODBC I'll award Sam with the points.
0
 
DanRollinsCommented:
To do that, click the "Accept" button next to his post.  Thanks. -- Dan
0
 
leonstrykerCommented:
DanRollins,

I am assuming you are taking care of this.

Leon
0
All Courses

From novice to tech pro — start learning today.