0

I have gone through a few tutorials already and my connection keeps failing, I have tried a lot of different ways of connecting.

I have a connection to mySQL through the mySQL workbench. I am using the IP address and the Port number and then my credentials to login. This works well and I am able to do the queries I need.

I am now trying to access this database through Excel, preferably through VBA. I tried to create a new connection but nothing I do seems to work. I am not sure what to put into my strConn string.

I am currently using:

Options Explicit
Private Sub CommandButton2_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strConn As String

    Set cn = New ADODB.Connection
    strConn = "DRIVER={MySQL ODBC 5.3.7 Driver};" & _ 
              "SERVER=XXX.XXX.X.X;" & _ 
              "PORT=3306" & _ 
              "DATABASE=cahier_de_lab;" & _ 
              "UID=xxx;" & _ 
              "PWD=xxx;" & _ 
              "Option=3"

    cn.Open strConn

    ' Find out if the attempt to connect worked.
    If cn.State = adStateOpen Then
          MsgBox "Welcome to Pubs!"
    Else
          MsgBox "Sorry. No Pubs today."
    End If

    ' Close the connection.
    cn.Close

End Sub

Thanks for your help!

Parfait
  • 104,375
  • 17
  • 94
  • 125
JahKnows
  • 2,618
  • 3
  • 22
  • 37
  • 1
    The driver looks wrong to me. – Keith John Hutchison Feb 10 '17 at 21:47
  • It is ... https://www.google.com.au/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=vba+mysql+driver+connection+string – Keith John Hutchison Feb 10 '17 at 21:48
  • You may need to set up an odbc connection as well. – Keith John Hutchison Feb 10 '17 at 21:49
  • strConn = "DRIVER={MySQL ODBC 5.3.7 Driver};" & _ "SERVER=XXX.XXX.X.X;" & _ "PORT=3306" & _ "DATABASE=cahier_de_lab;" & _ "UID=xxx;" & _ "PWD=xxx;" & _ "Option=3" Even when I use this it gives me the same error. I installed the driver for MySQL ODBC 5.3.7 Driver 32-bit. The database is saved 32-bit. – JahKnows Feb 10 '17 at 21:59
  • 4
    `If cnn.State` please correct to `cn.State` (see also `cnn.Close`). It's probably a typo in posting the question, but in any case correct it please so that people can focus on the problem. And make sure you set `Option Explicit`. – A.S.H Feb 10 '17 at 22:05
  • Have you set up an odbc connection? – Keith John Hutchison Feb 10 '17 at 22:32
  • https://www.connectionstrings.com/mysql/ – Ken White Feb 10 '17 at 23:51
  • Add [error handling](http://stackoverflow.com/questions/6028288/properly-handling-errors-in-vba-excel) in your subroutine: `On Error: ... Msgbox Err.Number & " - " & Err.Description` and edit your post with error message you receive. – Parfait Feb 10 '17 at 23:55
  • I have set up a proper odbc connection. I am having trouble connecting to it. The error code says "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied". Access is not denied because I can connect to the database using MySQL Workbench. Any ideas? – JahKnows Feb 16 '17 at 20:05
  • 1
    MySQL Workbench does not use ODBC. Your next step is to find a way to test the ODBC connection is valid. – Keith John Hutchison Feb 17 '17 at 00:16
  • Did you validate the ODBC connection? – Keith John Hutchison Mar 15 '17 at 06:04

1 Answers1

0

Export from Excel to SQL Server.

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=Excel-PC\SQLEXPRESS"
'cnn.ConnectionString = "DRIVER=SQL Server;SERVER=Excel-PC\SQLEXPRESS;DATABASE=Northwind;Trusted_Connection=Yes"


'Create a new Command object
Set cmd = New adodb.Command

'Open the Connection to the database
cnn.Open

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL


'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

OR . . . .

Import from SQL Server into Excel . . . . .


Sub Create_Connectionstring()

Dim objDL As MSDASC.DataLinks
Dim cnt As ADODB.Connection
Dim stConnect As String 'Instantiate the objects.

Set objDL = New MSDASC.DataLinks
Set cnt = New ADODB.Connection

On Error GoTo Error_Handling 'Show the Data-link wizard
stConnect = objDL.PromptNew 'Test the connection.
cnt.Open stConnect 'Print the string to the VBE Immediate Window.
Debug.Print stConnect 'Release the objects from memory.
exitHere:
    cnt.Close
    Set cnt = Nothing
    Set objDL = Nothing
Exit Sub

Error_Handling: 'If the user cancel the operation.
If Err.Number = 91 Then
    Resume exitHere
End If
End Sub
ASH
  • 20,759
  • 19
  • 87
  • 200