troubleshooting Question

Connect to MySQL Database from VBA

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessMicrosoft SQL Server 2008
4 Comments1 Solution745 ViewsLast Modified:
Hi
n
I have managed to connect to a SQL 2008 online databadse with the code in the code section
below. I need to write a similar function for a MySQL database residing on the same website.
I have written the following code which still doesn't work. Not sure why.
Sub oConnectToMySQL()

    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
   

    'con.Open "Driver={MySQL ODBC 5.1 Driver};Server=196.220.41.175,3307;Database=mysql5;User=murbro; Password=chestnut;Option=3;"
        con.Open "Provider={MySQL ODBC 5.1 Driver};Data Source=196.220.41.175,3307;Network Library=DBMSSOCN;Initial Catalog=sqlserver1;User ID=murbro;Password=chestnut;"
    'mysql51c.[yourdomain.com] / Port: 3307 / Mysql IP: 196.220.41.175 phpmyadmin
   
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
   
    rst.Open "SELECT * FROM MyTable", con
   
End Sub
Sub ConnectToSQLServer()

    On Error GoTo EH
    'Assuming the SQL Express database is configured for remote connections you can. You generally do this by opening a connection to the database and then issuing your query:
    'Ref Microsoft AxtiveX Data Objects 6.0 Library
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=196.220.43.247,1444;Network Library=DBMSSOCN;Initial Catalog=sqlserver1;User ID=murbro;Password=chestnut;"
     
    'sql7.[yourdomain.com],1444 (196.220.43.247) (Web Manager) (Port 1444)
    
    '/ Note that connection string from here: http://www.connectionstrings.com/sql-server-2008#p2
    
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    rst.Open "SELECT * FROM MyTable", con
    
    'These are the basics, but this would leave you with a fully filled Recordset.
    
    'Note that unless your online host has assigned a name for this, you'd have to use the full IP address as "Server" portion

Exit Sub

EH:
    MsgBox Err.Description
    
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros