Problem with establish connection with MySQL DB by VBA

G

Gadziu

New Member
Joined
Aug 23, 2014
Messages
7
Hi,
I've done everything what is written in Internet, but still I get error. But from begining. I want to connect with MySQL DB from VBA level. I've installed ODBC connector and have made user DNS in it and have procced test, which complete successful. In Ms Visual Basic in Tools->References-> I checked Microsoft Activex Object 2.8.
My ODBC settings
1.png

2.png


By now my code looks like this
Code: 
Dim oConn As ADODB.Connection

Private Sub ConnectDB()

    Set oConn = New ADODB.Connection

    oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
    "SERVER=sql.serwer;" & _
    "DATABASE=mydatabase;" & _
    "USER=muusername;" & _
    "PASSWORD=mypassword;" & _
    "Option=3"
End Sub

This is the error
3.png


Any ideas?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
have you looked at your references in the VBA project
 
Upvote 0
I use

Code: 
Private Sub Workbook_Open()

    Dim ID
    'Exit Sub
    'On Error Resume Next
    'Reference ADO Object Library using Major / Minor GUID
    Set ID = ThisWorkbook.VBProject.References
    'ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5 'Microsoft ActiveX Data Objects 2.5
    ID.AddFromGuid "{00000206-0000-0010-8000-00AA006D2EA4}", 2, 5        'Microsoft ActiveX Data Objects 2.6
    ID.AddFromGuid "{00025E01-0000-0000-C000-000000000046}", 2, 5        'Microsoft DAO 3.6
    'ID.AddFromGuid "{8E27C92E-1264-101C-8A2F-040224009C02}", 7, 0        'MSCAL.OCX 2007
    ID.AddFromGuid "{3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}", 4, 0        'HTML
    ID.AddFromGuid "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}", 1, 1        'IE

then

Code: 
Dim adoConn As New ADODB.Connection
Dim adoRs   As New ADODB.Recordset
Dim CNT     As New ADODB.Connection
Dim rnStart As Range
Dim RS      As New ADODB.Recordset
Dim sConn   As String
as part of my SQL connections (its not perfect)

then

Code: 
    Set rnStart = Sheets("REFERENCE LOOKUP").Range("A24")

    sConn = "Provider=SQLOLEDB; Data Source =****** ; Initial Catalog = ******; User Id = *****; Password=*******"
    Var1 = ""
    Var1 = Var1 & "Select SHIFTCYCLE.SHIFTDATE " & vbCrLf


 sSql = Var1

    On Error GoTo Err

    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    adoConn.CommandTimeout = 60
    Set adoRs = New ADODB.Recordset
    adoRs.Open Source:=sSql, ActiveConnection:=adoConn
    'On Error Resume Next
    If Not (adoRs.BOF Or adoRs.EOF) Then
        Do While Not adoRs.EOF
            rnStart.CopyFromRecordset adoRs
            adoRs.MoveNext
        Loop
        sOutput = Left(sOutput, Len(sOutput) - 1)
    Else
        sOutput = "NO MATCHED DATA"
        Sheets("REFERENCE LOOKUP").Range("A24") = sOutput
    End If

    adoRs.Close
    adoConn.Close
    Set adoRs = Nothing
    Set adoConn = Nothing
 
Upvote 0
Gadziu said:
No, all settings to connection are written correct. I've just remove the orginal data.
Click to expand...


Really? Here's the code you posted:

Rich (BB code): 
Dim oConn As ADODB.Connection

Private Sub ConnectDB()

    Set oConn = New ADODB.Connection

    oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
    "SERVER=sql.serwer;" & _
    "DATABASE=mydatabase;" & _
    "USER=muusername;" & _
    "PASSWORD=mypassword;" & _
    "Option=3"
End Sub
 
Upvote 0
Gadziu said:
at this line I got error.
5.png
Click to expand...

not all items are necessary, i was just demonstrating what i use to ensure my connections work, on rare occasions i get a failure on a new book, that when restarted performs flawlessly
 
Upvote 0
Andrew Poulsom said:
Really? Here's the code you posted:

Rich (BB code): 
Dim oConn As ADODB.Connection

Private Sub ConnectDB()

    Set oConn = New ADODB.Connection

    oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
    "SERVER=sql.serwer;" & _
    "DATABASE=mydatabase;" & _
    "USER=muusername;" & _
    "PASSWORD=mypassword;" & _
    "Option=3"
End Sub
Click to expand...

Belive me. It's ok. I'm from Poland and the sever adress is longer then "sql.serwer", and polish word for server is serwer.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Connecting to Maria DB
Replies
1
Views
372
Peter_SSs
Peter_SSs
C
  • Question
ole db CopyFromRecordset skipping cell values
Replies
0
Views
311
Clif McIrvin
C
D
  • Question
VBA Excel - MySQL filter two fields error on Update - Run-time error '-2147467259 (80004005)'
Replies
2
Views
2K
dwsteyl
D
T
  • Question
Access MySQL Server with VBA
Replies
0
Views
479
tico_ocit
T
T
  • Question
Error: ODBC driver does not support the requested properties
Replies
8
Views
10K
Fazza
F

Forum statistics

Threads
1,214,918
Messages
6,122,257
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top