Access a mySQL Database from VB

[stefanRusterholz]

I'm still a newbie, so please explain stepwise ;-)

I got following problem: i need to access (on the local machine) a mysql database. I downloaded myODBC 2.50.38 from mysql.com (

http://www.mysql.com/Downloads/MyODBC/myodbc-2.50.38-nt.zip)

but i couldn't get it run.

The old code which accesses an MS Access DB instead of the mySQL DB is the following:

Dim rs As ADODB.recordset
Dim connStr As String
Dim SQLquery As String

Set rs = New ADODB.recordset
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\database.mdb"
SQLquery = "SELECT something FROM mytable"

rs.open SQLquery, connStr

As I understand VB, I just needed to change the connectionstring. But to what?

Thanks for helping!
Stefan

 

[sunaj]

Hi

You need to access the database through a DSN (Data source name). You can create a DSN in the control panel.
Try and search for mysql at

http://www.vbcode.com,

there a couple of good examples by Yusuf Incekara.

Sunaj

 

[stefanRusterholz]

Hmm, I searched a while for it in vbcode.com, but I couldn't find it. Can you please tell me where this examples are located?

Stefan

 

[sunaj]

Goto

http://www.vbcode.com,

write "mysql" in search database and press the button.

Sunaj

 

[stefanRusterholz]

I tried that but it told me it had found 0 results :-(
But luckily I found it somewhere else.

But thank you anyway!

Stefan

 

[stefanRusterholz]

Hmmm, strange. I tried it again and now it presents me some results...
perhaps it was just a misstypo before :-/

Stefan

 

[woyler]

A good alternative to a DSN is to code the connection string. I would recommend this so that when you begin installing your app on different PCs, you will not have to set up the DSN on each machine. Try this for SQL Server:

dim cnConn as adodb.connection

set cnConn = createobject("adodb.connection&quot
cnconn.connectionstring = "Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=YourServerName;User Id=SA;Initial Catalog=YourSQLDataBaseName"

cnconn.open

 

[woyler]

That annoying laughing face is not intentional. It should be: S-Q-L-O-L-E-D-B

 

[stefanRusterholz]

Thank you too.
I did it indeed with coding the connectionstring:

dim connstr as string
connstr = "driver={MySQL};server=my.server.domain;uid=myname;pwd=mypassword;database=myDB;"

dim rs as adodb.recordset
set rs = new adodb.recordset
rs.open "SELECT something FROM myTable", connStr

That's it.
With DSN I had too many problems

Stefan

 

[scoots987]

How does one(me) determine the domain name of my server? I created a database and a table and through Mysql.exe loaded 47,000 records into the database and select * from mytable works like a charm.

Now I am trying to use VB to connect to it. I like the SQLOLEDB alternative but I see that the connection string must connect to a "my.server.domain". What is that? How is Mysql setup for this and how does the VB app see it?

Is there any other software to install? Like SQLOLEDB?

Thanks
Scoots987