Connecting to MYSQL .net connector

[SGT68]

Connecting to external data sources is a new thing i'm learning so bear with me pls.

MYSQL is a natural choice for me over SQL Server as i have been using it a lot with php and i'm used to MYSQL workbench and phpmyadmin etc etc..

I loved the way that php had native support for mysql rather than use odbc, so i'd like the same functionality when connecting my Access front end to a mysql db. Therefor i want to use ADO with a .NET connector to MYSQL rather than linking tables with ODBC dsn which to me is cumbersone.

Today i been reading up on ADO in my Wiley Access 2013 Bible and i'm getting the gist of things.. Anyone familiar with using MYSQL on Windows probably knows its got Connector .NET v6.8.3 and an ODBC Connector which ships with the MYSQL for Windows installer..

I want to use VBA to create ADO objects to connect to the .net connector, I dont want to use ODBC. I got the ActiveX Data Object 6.1 library option enabled in Access which is the first stage, then my book said i should create a connection string file by creating blank text file with notepad and save it with a .udl extension then open it, to get the Data Link Properties dialog. I do this but when i click on the Provider tab, there is no OLEDB for MySQL option even though i'm sure i got it installed, so thats got me puzzled..

I know I'm kind of straying off the subject of Access, but there must be someone out there who has done this already and can help??

Does anyone have any experience of creating VBA code using ADO to talk to the MYSQL .NET connector? Is it possible? Or do i have to use ODBC?

I read that MYSQL .net connector is compatible with Visual Studio which i think implies that its compatible with VBA? Am i right? Am i wrong?

 

[vbaInet]

Here's my 2 cents - it's been a pretty long time since I used MySQL but what you need is ADO with an OLEDB driver (or an ODBC driver) and you should be connected without needing a DSN. You'll find the connection string here:

http://www.connectionstrings.com/mysql/
And

Provider=MySQL Provider; Data Source=MySQLServerIP; User ID =MyID; Password=MyPassword; Initial Catalog=DatabaseName;

.NET is strictly for .NET platforms.

 

[vbaInet]

This should help you make a manual link to MySQL and from there you can open the linked table in design view, copy the Description and that would be your connection string. You can then use that to make connections in code.

http://dev.mysql.com/doc/connector-...examples-tools-with-access-linked-tables.html

 

[SGT68]

This should help you make a manual link to MySQL and from there you can open the linked table in design view, copy the Description and that would be your connection string. You can then use that to make connections in code.

http://dev.mysql.com/doc/connector-...examples-tools-with-access-linked-tables.html

This uses ODBC though. I would prefer to not use dsn and linking. Thank you anyway

 

[SGT68]

Here's my 2 cents - it's been a pretty long time since I used MySQL but what you need is ADO with an OLEDB driver (or an ODBC driver) and you should be connected without needing a DSN. You'll find the connection string here:

http://www.connectionstrings.com/mysql/
And

Provider=MySQL Provider; Data Source=MySQLServerIP; User ID =MyID; Password=MyPassword; Initial Catalog=DatabaseName;

.NET is strictly for .NET platforms.

Thanks, that's worth a read... Isn't Access a .NET platform?

 

[vbaInet]

Access is VBA.
.NET is VB, C#, C++, F# etc...

 

[SGT68]

Here's my 2 cents - it's been a pretty long time since I used MySQL but what you need is ADO with an OLEDB driver (or an ODBC driver) and you should be connected without needing a DSN. You'll find the connection string here:

http://www.connectionstrings.com/mysql/
And

Provider=MySQL Provider; Data Source=MySQLServerIP; User ID =MyID;Password=MyPassword; Initial Catalog=DatabaseName;

.NET is strictly for .NET platforms.

Can you help further please:

Here's my code in Access VBA:

Public Sub DBConn()

Dim adConn As New ADODB.Connection
Dim myConnectionString As String

myConnectionString = "Provider=MySQLProv;DataSource=njpn2;UserId=root;Password=whatever;"

adConn.ConnectionString = myConnectionString

adConn.Open

End Sub

I get runtime error 3706. Provider cannot be found. It may not be properly installed.

I got MyOledb installed. When i goto VBA editor -> Tools -> references and browse to Myprov.dll it says it can't add a reference to this dll..
I got ADO 6.1 referenced tho..

but Visual Studio Express 2013 recognises it tho... is that a clue ?

Any ideas? :banghead:

ps. in my code there is no space between the P and a of Password. It just been written that way in posting

 

[SGT68]

Got the same error with the connection string you gave me:

Public Sub DBConn()

Dim adConn As New ADODB.Connection
Dim myConnectionString As String

myConnectionString = "Provider=MySQL Provider; Data Source=127.0.0.1; User ID =root;Password=whatever; Initial Catalog=njpn2;"

adConn.ConnectionString = myConnectionString

adConn.Open

End Sub

 

[vbaInet]

Download the ODBC Connector:
http://dev.mysql.com/downloads/connector/odbc/

Use the following connection string:

    adConn.ConnectionString = "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
                              "SERVER=[COLOR="blue"]localhost[/COLOR];" & _
                              "DATABASE=[COLOR="blue"]schema_name[/COLOR];" & _
                              "UID=[COLOR="blue"]root[/COLOR];" & _
                              "PWD=[COLOR="Blue"]password[/COLOR];" & _
                              "OPTION=3;"

 

[SGT68]

Download the ODBC Connector:
http://dev.mysql.com/downloads/connector/odbc/

Use the following connection string:

    adConn.ConnectionString = "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
                              "SERVER=[COLOR="blue"]localhost[/COLOR];" & _
                              "DATABASE=[COLOR="blue"]schema_name[/COLOR];" & _
                              "UID=[COLOR="blue"]root[/COLOR];" & _
                              "PWD=[COLOR="Blue"]password[/COLOR];" & _
                              "OPTION=3;"

Thanks. I tried this already and it did work. I got this connector installed. I had been curious however to find a non-odbc method, using ado and ole db.. as i stated in my original post. It seems that Access and VBA, using ADO objects wont connect to mysql database using oledb. VB and Visual studio will, but VBA and Access won't. Those examples in the links you posted don't specify Access anywhere. An experiment worth conducting, I think. Thanks for your help

 

[vbaInet]

If you find an appropriate OLE DB driver and install it you will be able to connect. .NET is more advanced and does things under the hood (so to speak).

 

[SGT68]

Here's my 2 cents - it's been a pretty long time since I used MySQL but what you need is ADO with an OLEDB driver (or an ODBC driver) and you should be connected without needing a DSN. You'll find the connection string here:

http://www.connectionstrings.com/mysql/
And

Provider=MySQL Provider; Data Source=MySQLServerIP; User ID =MyID; Password=MyPassword; Initial Catalog=DatabaseName;

.NET is strictly for .NET platforms.

I found the driver that works with the string you quoted above. I had the wrong one installed. You can even reference it with VBA EDitor->Tools->References, so you know its going to work. Its third party though, not Oracle made.

http://cherrycitysoftware.com/ccs/providers/ProvMySQL.aspx

 

[vbaInet]

Yes I found that a couple minutes ago. Give a try and see if it works.

It seems to be the OLE DB provider that's referenced in the connection strings website. I thought they would have mentioned that it's third party.

 

[SGT68]

Yes I found that a couple minutes ago. Give a try and see if it works.

It seems to be the OLE DB provider that's referenced in the connection strings website. I thought they would have mentioned that it's third party.

It does work with Access!! But interestingly NOT with Visual Studio.
So our findings are

MyOLEdb .net flavour OLE DB DRIVER for Visual Studio Express, doesn't work with Access
http://sourceforge.net/projects/myoledb/

Cherry City Software ADO OLEDB PROVIDER. Works with Access VBA
http://cherrycitysoftware.com/ccs/providers/ProvMySQL.aspx

Oracle official ODBC connector - works with Access
http://dev.mysql.com/downloads/connector/odbc/

Oracle official .net connector - doesn't work with Access, for Visual Studio .net Pro
http://dev.mysql.com/downloads/connector/net

 

[vbaInet]

Good links!

But of course a .NET connector won't work in Access because Access is VBA, and VBA is not part of the .NET family. If Cherry made a .NET OLE DB provider then it will work only in a .NET environment.

 

[SGT68]

Good links!

But of course a .NET connector won't work in Access because Access is VBA, and VBA is not part of the .NET family. If Cherry made a .NET OLE DB provider then it will work only in a .NET environment.

Yes i now understand this distinction.