vba + ado +sql 连接数据库的常用操作方式
vba + ado +sql 连接Access、MySQL、Oracle
Private Sub Connection_DBA()
'********************************************************************
'测试连接Access、MySQL、Oracle
'********************************************************************
Dim cnADO As Object
Dim rsADO As Object
Dim i&, j&
Dim arr
Dim strPath$
Set cnADO = CreateObject("ADODB.Connection")
Set rsADO = CreateObject("ADODB.Recordset")
'ACCESS
'strPath = "\\xxx.xxx.xxx.xxx\test\Budget.accdb"
'cnADO.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath
'ORACLE
'cnADO.Open "Provider=OraOLEDB.Oracle.1; user id=xxx; password=xxxx; data source = localhost:1521/orcl;"
'MySQL
cnADO.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=xxx.xxx.xxx.xxx;Port=3306;DB=xxx;UID=xxx;PWD=xxxx;OPTION=3;"
cnADO.Open
strSQL = "select * from new_table;"
Set rsADO = cnADO.Execute(strSQL)
For i = 0 To rsADO.Fields.Count - 1
Cells(1, i + 1) = rsADO.Fields(i).Name
Next i
Range("A2").CopyFromRecordset rsADO
Set cnADO = Nothing
Set rsADO = Nothing
End Sub