mysql이랑 연결하기

Sub callDB()

'//변수선언

Dim conn As ADODB.Connection

Dim server_name As String

Dim database_name As String

Dim user_id As String

Dim password As String

Dim dbRecset As New ADODB.Recordset

Dim sSQL As String

Dim iRow As Long, n As Long

Dim intPublisherCount As Integer

'//서버정보 입력

server_name = "서버이름"

database_name = "디비이름"

user_id = "유저아이디"

password = "유저비번"

'//connection open

Set conn = New ADODB.Connection

conn.Open "DRIVER={MySQL ODBC 5.3 ANSI Driver}" _

& ";SERVER=" & server_name & ";DATABASE=" & database_name _

& ";UID=" & user_id _

& ";PWD=" & password _

& ";OPTION=3"

Set dbRecset = New ADODB.Recordset

dbRecset.CursorLocation = adUseClient

'//sql입력

sSQL = "SELECT fdl_id, fdl_type, fdl_name, fdl_cal FROM j2t_food_list"

'//recordset open

dbRecset.Open Source:=sSQL, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText

'//1번라인에 field받아오기

For n = 1 To dbRecset.Fields.Count

    Worksheets(1).Cells(1, n).Value = dbRecset.Fields(n - 1).Name

Next n

'//db에서 첫번째 값으로 이동

dbRecset.MoveFirst

'//db데이터들 2번라인부터 받아오기

For iRow = 1 To dbRecset.RecordCount

    For n = 1 To dbRecset.Fields.Count

        Worksheets(1).Cells(iRow + 1, n).Value = dbRecset.Fields(n - 1).Value

    Next n

    dbRecset.MoveNext

Next iRow

'//recordset, connection close

dbRecset.Close

conn.Close

Set dbRecset = Nothing

Set conn = Nothing

End Sub