方法一:
Sub runthis()
Dim conn As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim sql As String
connstr = "ODBC;DSN=development;Server=10.2.1.1;Port=3306;Database=Store;Uid=root;Pwd="
sql = "SELECT * FROM employee "
Sheets("test").Activate
With ActiveSheet.QueryTables.Add(Connection:=connstr, _
Destination:=Range("B1"), sql:=sql)
.Refresh
End With
Set rec = Nothing
Set conn = Nothing
Exit Sub
errorHandler:
MsgBox "RunThis 错误:" & Err.Description
End Sub
方法二:
Sub runthis()
Dim conn As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim sql As String
connstr = "Driver={MySQL ODBC 3.51 Driver};Server=10.2.1.1;Port=3306;Option=131072;Stmt=;Database=store;Uid=root;Pwd="
conn.Open connstr
rec.Open "SELECT * FROM employee ", conn
sql = "SELECT * FROM emp_M_BasicInfo "
Sheets("test").Activate
Sheets("test").Cells.Clear
'显示字段名
For lCount = 0 To rec.Fields.Count - 1
Cells(1, 1 + lCount).Value = rec.Fields(lCount).Name
Next lCount
'显示数据
linenum = 1
If Not rec.EOF Then
Cells(linenum + 1, 1).CopyFromRecordset rec
rec.NextRecordset
linenum = linenum + 1
End If
Set rec = Nothing
Set conn = Nothing
Exit Sub
errorHandler:
MsgBox "RunThis 错误:" & Err.Description
End Sub