Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: Visual Basic
Posted by: Mario Lopes
Added: Nov 14, 2018 8:52 AM
Views: 14
Tags: vba
  1. Sub GetMysSQLData()
  2. Dim conn As ADODB.Connection
  3. Dim dbRecset As ADODB.Recordset
  4. Dim sSQL As String
  5. Dim l As Long, l2 As Long
  6. Set conn = New ADODB.Connection
  7. Set conn = New ADODB.Connection
  8. strConn = "DRIVER={MySQL ODBC 5.1 Driver};"
  9. strConn = strConn & "SERVER=mariolopes.com;"
  10. strConn = strConn & "DATABASE=xxxx;"
  11. strConn = strConn & "UID=xxxxx;PWD=xxxxx"
  12. conn.ConnectionString = strConn
  13. conn.Open
  14. sSQL = "SELECT * FROM matrizes;"
  15. Set dbRecset = New ADODB.Recordset
  16. dbRecset.CursorLocation = adUseClient
  17. dbRecset.Open Source:=sSQL, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, _
  18. LockType:=adLockReadOnly, Options:=adCmdText
  19.  
  20. dbRecset.MoveFirst
  21. For l = 1 To dbRecset.Fields.Count
  22. Worksheets(1).Cells(1, l).Value = dbRecset.Fields(l - 1).Name
  23. Next l
  24. For l2 = 1 To dbRecset.RecordCount
  25. For l = 1 To dbRecset.Fields.Count
  26. Worksheets(1).Cells(l2 + 1, l).Value = dbRecset.Fields(l - 1).Value
  27. Next l
  28. dbRecset.MoveNext
  29. Next l2
  30.  
  31. dbRecset.Close
  32. conn.Close
  33.  
  34. Set dbRecset = Nothing
  35. Set conn = Nothing
  36. End Sub