try this bit of code to see if it will work with what you need, this should allow you to access the database using ADO...

Just paste it into the forms code window and add a command button to the form... and make the appropriate changes to the code to reflect your info to pass to the database..

Dim strDataBaseName As String
Dim strDBCursorType As String
Dim strDBLockType As String
Dim strDBOptions As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Private Sub Command1_Click()
On Error GoTo Command1_Click_Error
Dim b as Long
strDBCursorType = adOpenDynamic  'CursorType
strDBLockType = adLockOptimistic   'LockType
strDBOptions = adCmdText         'Options

Set cn = New ADODB.Connection
Me.MousePointer = 11

cn.Open ConnectString()
    
    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With

    Set rs = New ADODB.Recordset       'Creates record set

    strSQL = "<Your SQL Here>"
    
    rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions
    

if rs.Eof then
   Goto ExitSub    
else
    For b = 0 To rs.RecordCount -1
    '<do whatever you need to do with the data here>
    Next b
end if
    
ExitSub:

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

On Error GoTo 0
Exit Sub

Command1_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & _
    "in procedure Command1_Click of Form " & Me.Name
End Sub

Private Function ConnectString() As String
Dim strServerName as String
Dim strDatabaseName as string
Dim strUserName as string
Dim strPassword as string

    'Change to IP Address if not on local machine
    'Make sure that you give permission to log into the
    'server from this address
    'See Adding New User Accounts to MySQL
    'Make sure that you d/l and install the MySQL Connector/ODBC 3.51 Driver 

strServerName = "localhost" 
strDatabaseName = "DatabaseName"
strUserName = "UserName"
strPassword ="Password"

ConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                "SERVER=" & strServerName & _
                ";DATABASE=" & strDatabaseName & ";" & _
                "USER=" & strUserName & _
                ";PASSWORD=" & strPassword  & _
                ";OPTION=3;"

End Function

Also, check this out for other info on MySQL connection strings >>http://www.connectionstrings.com/mysql

B. Morse

 

try this bit of code to see if it will work with what you need, this should allow you to access the database using ADO...

B. Morse

Thanks for the guidance B Morse.

I just tried this and I get "Compile error: User-defined type not defined."

And it points to: "DIM rs As ADODB.Recordset"

Is there some sort of ADO connection object that needs to be loaded first?

 

Yes The ADO library must be referenced by your project.

here is how to do that (just in case )

1. In Visual Basic, from the Project menu, select References....
2. Select Microsoft ActiveX Data Objects x.x Library from the list. Verify that at least the following libraries are also selected:
   • Visual Basic for Applications
   • Visual Basic runtime objects and procedures
   • Visual Basic objects and procedures
   • OLE Automation
3. Click OK.

P.S.
Re copy and paste the code above in post #21, there was a type-O in the code and I fixed it

B. Morse

 

Yes The ADO library must be referenced by your project.
B. Morse

You did well B. Morse,

I chose the 2.8 library and the script was indeed able to compile.

I ran the script and it said it couldn't find my host (db769.perfora.net). I use that host for lots of PHP script connections. However, I notice in the script you listed, it said:

'Change to IP Address if not on local machine
'Make sure that you give permission to log into the
'server from this address
'See Adding New User Accounts to MySQL
'Make sure that you d/l and install the MySQL Connector/ODBC 3.51 Driver

I am not using a local machine, so I would change to an IP address, but when I try to ping my host, I'm told it can't be found so I don't know the IP address. Do you suppose the Host name would work?

I did d/l and install the ODBC 3.51 connector and I selected "Microsoft Remote Data Object" it in references." So I think I satisfied that requirement.

Unfortunately, I do not know how to "give permission to log into the server from this address". Not sure that is something I can do since I don't own the MySQL server.

It would be quicker to state what I do know. I suppose the good news is that once I'm properly trained, I'll be able to apply this new found knowledge in a lot of areas. Something I'm looking forward to.

Thanks B Morse,
Frank

 

Re copy and paste the code above in post #21, there was a type-O in the code and I fixed it

B. Morse

PS-- I did re paste the corrected code and I did check to be sure the other libraries were selected in VB.

Also, I have been reading through the Connection Strings URL you provided.

Here is actual error code I get now: Error-2147467259([MySQL][ODBC 3.51 Driver]Unknow MySQL server host 'db769.perfora.net'(11004)in procedure Command1_Click of Form Form1

Now starting to suspect the database server does not allow visual basic connections from a client. Is that restriction common? I am working to find an answer. If a direct VB connection is not allowed. I can still ftp the data to the server as a text file and then download the data, process it and save it back into the MySQL database. Seems like the long way around, but perhaps the only way. I will have to learn how to ftp with VB.

 

Have now discovered that access to the MySQL databases on my web service remotely is not allowed. The site says, "Direct access to your MySQL databases using a home PC (external ODBC connection) cannot be established." Pretty clear.

However, I do have access to a second server that has now granted permission to me to access the MySQL databases. Has to be via a static IP and port 3306. So, I need to check that out.

I wonder if it could be an issue to establish the connection for continuous database changes, such as tracking pulses. Maybe it would be preferable to update every half hour, etc.

 

Looks like you are making good progress with this, unfortunately I have not played around too much with MySQL, so what ever you find out on how to accomplish this could be useful info for the future....

B. Morse