Excel Forum

Code: Select all

Sub test2_Click()

MsgBox "โปรดตรวจสอบการตั้งค่าเชื่อมต่อ", , "ตั้งค่าการเชื่อมต่อ"

Dim Servername As String
Dim Databasename As String
Dim UserID As String
Dim Password As String
Dim Query As String


Servername = Sheets("setting").Range("B2").Value
Port = Sheets("setting").Range("B3").Value
Databasename = Sheets("setting").Range("B4").Value
UserID = Sheets("setting").Range("B5").Value
Password = Sheets("setting").Range("B6").Value
Query = Sheets("setting").Range("B7").Value '

MsgBox Servername, , "Server"
MsgBox Port, , "Port"
MsgBox Databasename, , "Database"
MsgBox UserID, , "User"
MsgBox Password, , "password"
'MsgBox Query, , "ÃÒ§ҹ" '

    'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset

        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

    'Open Connection'
        objMyConn.connectionString = "Provider={MySQL ODBC 3.51 Driver}; " _
        & "Data Source=Servername; " _
        & "Initial Catalog=Databasename; " _
        & "Port=Port; " _
        & "User ID=UserID; " _
        & "Password=Password"
        
        objMyConn.Open

    'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = "Query;"
        objMyCmd.CommandType = adCmdText

    'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open

    'Copy Data to Excel'
        Person.Range("A2").CopyFromRecordset objMyRecordset
        
    Main.Activate
        
    End Sub
  

Code: Select all

Sub sqlconnect()
    
    Dim Servername As String
    Dim Databasename As String
    Dim UserID As String
    Dim Password As String
    Dim Query As String


    Servername = Sheets("setting").Range("B2").Value
    Port = Sheets("setting").Range("B3").Value
    Databasename = Sheets("setting").Range("B4").Value
    UserID = Sheets("setting").Range("B5").Value
    Password = Sheets("setting").Range("B6").Value
    Query = Sheets("setting").Range("B7").Value '
     
      
    'Define a connection and a recordset to hold extracted information
    Dim oConn As ADODB.Connection, rcSet As ADODB.Recordset
    Dim cnStr As String, n As Long, msg As String, e
    
    'connection string to connect to db4free.net
    cnStr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & Servername & _
            ";Port=3333;DATABASE=" & Databasename & _
            ";UID=" & UserID & ";Password=" & Password & ";"
    
    'Test SQL query
    Const SQL = "SELECT * FROM person "
    
    ' connect
    Set oConn = New ADODB.Connection
    'oConn.CommandTimeout = 900
    
    On Error Resume Next
    oConn.Open cnStr
    If oConn.Errors.Count > 0 Then
        For Each e In oConn.Errors
            msg = msg & vbLf & e.Description
        Next
        MsgBox msg, vbExclamation, "ERROR - Connection Failed"
        Exit Sub
    Else
        
        MsgBox "เชื่อมต่อฐานข้อมูล  " & oConn.DefaultDatabase & "  สำเร็จ ", vbInformation, "สถานะการเชื่อมต่อฐานข้อมูล"
        
    End If
    
    ' run query
    Set rcSet = oConn.Execute(SQL, n)
    If oConn.Errors.Count > 0 Then
        msg = ""
        For Each e In oConn.Errors
            msg = msg & vbLf & e.Description
        Next
        MsgBox msg, vbExclamation, "ERROR - Execute Failed"
    Else
        Sheets("Person").Range("A2").CopyFromRecordset rcSet
        
        MsgBox "จำนวนข้อมูลที่นำเข้าสำเร็จ " & n & " เรคคอด", vbInformation, "สถานะการนำเข้าข้อมูล"
        
    End If
    On Error GoTo 0
    
    oConn.Close
    
End Sub

Code: Select all

Driver={MySQL ODBC 8.0 Driver}

Code: Select all

Driver={MySQL ODBC 8.0 Unicode Driver}