http://pragmateek.com/using-an-access-mysql-postgresql-or-sqlite-database-from-vba/ << Good


http://www.teachexcel.com/excel-help/excel-how-to.php?i=164832


http://yyman.tistory.com/entry/MySQL-Excel-VBA%EC%97%90-MySQL-%EC%97%B0%EB%8F%99%ED%95%98%EA%B8%B0-1%ED%8E%B8-odbc-%EC%84%A4%EC%B9%98-%EB%B0%8F-%ED%99%95%EC%9D%B8?viewbar


http://stackoverflow.com/questions/14575422/open-connection-mysql-vba-excel-2007


http://www.heritage-tech.net/908/inserting-data-into-mysql-from-excel-using-vba/


http://mysql.veryoo.com/2014/03/excel-vba-code-in-mysql-database-using.html


http://stackoverflow.com/questions/13425642/import-sql-data-into-excel-using-vba


http://dumbpcs.blogspot.kr/2009/05/excel-vba-codes-to-connect-to-mysql-db.html


http://stackoverflow.com/questions/19448205/excel-vba-connecting-to-remote-mysql-database



excel query mysql databases connecting to mysql from excel vba


 ' Table action variables
  Dim i As Long ' counter
  Dim sqlstr As String ' SQL to perform various actions
  Dim table1 As String, table2 As String
  Dim field1 As String, field2 As String
  Dim rs As ADODB.Recordset
  Dim vtype As Variant

  '----------------------------------------------------------------------
  ' Establish connection to the database
  server_name = "127.0.0.1" ' Enter your server name here - if running from a local       computer use 127.0.0.1
  database_name = "smss" ' Enter your database name here
  user_id = "root" ' enter your user ID here
  password = "" ' Enter your password here

  Set conn = New ADODB.Connection
  conn.Open "DRIVER={MySQL ODBC 5.2a Driver}" _
    & ";SERVER=" & server_name _
    & ";DATABASE=" & database_name _
    & ";UID=" & user_id _
    & ";PWD=" & password _

  ' Extract MySQL table data to first worksheet in the workbook
  GoTo skipextract
  Set rs = New ADODB.Recordset
  sqlstr = "SELECT * FROM inbox" ' extracts all data
  rs.Open sqlstr, conn, adOpenStatic
  With Sheet1(1).Cells ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
  End With
  skipextract:

End Sub



Option Explicit
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim username As String
Dim passwd As String
Dim serverIP As String
Dim db As String

Public Function connectMysql(username As String, passwd As String, serverIP As String, db As String, conn As ADODB.Connection, rs As ADODB.Recordset)
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & serverIP & ";UID=" & username & ";PWD=" & passwd & ";DATABASE=" & db & ";" _ & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841
conn.Open
End Function



Public Function OpenConnection() As ADODB.connection
    //This function requires the "Microsoft ActiveX Data Objects" Library (Choose v2.8 from references for compatibility across Office versions)

    Dim source As String, location As String, user As String, password As String
    source = "MySQL"
    location = "192.168.1.60"
    user = "root"
    password = ""
    database = "database name"
    mysql_driver = "MySQL ODBC 5.2 ANSI Driver"

    //Build the connection string
    Dim connectionString As String

    connectionString = "Driver={" & mysql_driver & "};Server=" & location & ";Database=" & database & ";UID=" & user & ";PWD=" & password

    //Create and open a new connection to the selected source
    Set OpenConnection = New ADODB.connection
    OpenConnection.CursorLocation = adUseClient
    Call OpenConnection.Open(connectionString)
End Function




Sub QueryDB()
    Dim dbName As ADODB.Connection
    Dim dbResults As ADODB.Recordset
    Set dbName = openDBConn("YOURDATABASE", "YourTable")
    Set dbResults = dbName.Execute("SELECT * FROM YOURDATABASE")
    While Not dbResults.EOF
        'Do Something'
        dbResults.MoveNext
    Wend
End Sub

Function openDBConn(dataSource As String, table As String) As ADODB.Connection
    Dim newDBConn As ADODB.Connection
    Set newDBConn = New ADODB.Connection
    newDBConn.CommandTimeout = 60
    Dim strConn As String
    strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=" & dataSource & ";INITIAL CATALOG=" & table & ";INTEGRATED SECURITY=SSPI"
    newDBConn.Open strConn
    Set openDBConn = newDBConn
End Function
이 게시물을...