[Resolved] Open Database

[slice]

I want to open mysql database hosted on server from vb program on desktop.How to accomplish it ?
Thank You.

[BrailleSchool]

www.connectionstrings.com will be able to help you there. or you can search the forum for MySQL and youll get many responses to possible resolution

[slice]

Error i am getting on command
cn.Open

Data Source Name not found and no default driver specified.

Option Explicit
Private cn As ADODB.Connection
Private rs As ADODB.Recordset
 
 
Private Sub Form_Load()
Me.MousePointer = 11 'this makes the mouse pointer the hourglass
 
    Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
    cn.ConnectionString = "Driver={mySQL};Server=data.domain.com;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;"
    cn.Open
    Set rs = New ADODB.Recordset 'as we did with the connection
    rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes
       
rs.MoveFirst 'moves to the first record
Do Until rs.EOF = True 'this is the Loop to add items to the combo box
Combo1.AddItem rs.Fields("field1") 'this adds items from field1 into the combo box
rs.MoveNext 'moves next record
Loop
rs.MoveFirst
fillfields 'i’ll explain this later on.
 
Me.MousePointer = 0 'sets the mouse pointer to the normal arrow
 
End Sub
Public Sub fillfields()
    If Not (rs.BOF = True Or rs.EOF = True) Then 'Checks if we are at the first or last record. This is use a lot.
        Text1.Text = rs.Fields("Field2") 'text1 = field2 and display that data
      Text2.Text = rs.Fields("Field3") 'as above
  Combo1.Text = rs.Fields("Field1") 'as above
             Else
             MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
    End If
End Sub

I have used reference to Microsoft ActiveX Object 2.5 Library
and Connection String used from above mentioned site.
Thank You.

[slice]

I am getting error
[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on xxx.xx.Xxx.xxx' (10065)
When trying to set DSN from Adminstrative Tools in CP.

I am using correct host ip where my site is hosted.(share hosted).

[BrailleSchool]

you would do something like this:

Dim SQL As String
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=[IP_ADDRESS_OF_SERVER_HERE;" _
& "DATABASE=[DATABASE_NAME];" _
& "UID=[DATABASE_USERNAME];" _
& "PWD=[DATABASE_PASSWORD];" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
 
conn.Open conn.ConnectionString
 
'THIS IS AN EXAMPLE SQL STATEMENT
SQL = "SELECT * FROM My_Table WHERE Column_name = '" & txtBox.Text & "' "
 
rs.Open SQL, conn, adOpenStatic, adLockReadOnly
 
'DO WHAT YOU NEED TO AFTER YOU HAVE MADE A REQUEST TO 
'MYSQL SERVER USING AN SQL STATEMENT
 
If rs.State = adStateOpen Then
   rs.Close
End If
Set rs = Nothing
 
conn.Close
Set conn = Nothing

You will also need to go to www.MySQL.com and download the drivers for "MySQL ODBC 3.51 Driver" and install them on your machine. If you dont have the drivers, you wont get anywhere. its like trying to go through a locked door with no key.

[dee-u]

You need the ODBC driver for MySQL, download it from their site...

[BrailleSchool]

just like i said in my post above
wow, i beat ya to it dee-u

[dee-u]

I'm a minute too late!

[BrailleSchool]

Error i am getting on command
cn.Open

Data Source Name not found and no default driver specified.

Option Explicit
Private cn As ADODB.Connection
Private rs As ADODB.Recordset
 
 
Private Sub Form_Load()
Me.MousePointer = 11 'this makes the mouse pointer the hourglass
 
    Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
    cn.ConnectionString = "Driver={mySQL};Server=data.domain.com;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;"
    cn.Open
    Set rs = New ADODB.Recordset 'as we did with the connection
    rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes
       
rs.MoveFirst 'moves to the first record
Do Until rs.EOF = True 'this is the Loop to add items to the combo box
Combo1.AddItem rs.Fields("field1") 'this adds items from field1 into the combo box
rs.MoveNext 'moves next record
Loop
rs.MoveFirst
fillfields 'i’ll explain this later on.
 
Me.MousePointer = 0 'sets the mouse pointer to the normal arrow
 
End Sub
Public Sub fillfields()
    If Not (rs.BOF = True Or rs.EOF = True) Then 'Checks if we are at the first or last record. This is use a lot.
        Text1.Text = rs.Fields("Field2") 'text1 = field2 and display that data
      Text2.Text = rs.Fields("Field3") 'as above
  Combo1.Text = rs.Fields("Field1") 'as above
             Else
             MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
    End If
End Sub

I have used reference to Microsoft ActiveX Object 2.5 Library
and Connection String used from above mentioned site.
Thank You.

You opened the connection but you didnt close it when you were done with it. this is always a bad idea to keep the connection open when not in use

[BrailleSchool]

I'm a minute too late!

awwe, dee-u. hands you a kleenex

[slice]

I am getting error
[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on xxx.xx.Xxx.xxx' (10065)
When trying to set DSN from Adminstrative Tools in CP.

I am using correct host ip where my site is hosted.(share hosted).

[si_the_geek]

Yep, you said that before.

Have you tried what was suggested in post #5 ? (especially the bit at the bottom)

[BrailleSchool]

I am getting error
[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on xxx.xx.Xxx.xxx' (10065)
When trying to set DSN from Adminstrative Tools in CP.

I am using correct host ip where my site is hosted.(share hosted).

this is the thing, you wont need to use odbcad32.exe to set up anything to your mysql because the connection string in your app would take care of all of that.