VBA ADODB 连接 ACCESS

'ACCESS数据库地址

Dim MyData As String

MyData = "E:\WORK\WBS_2021.accdb"

 

'创建连接

Dim AdoConn As ADODB.Connection

Set AdoConn = New ADODB.Connection

With AdoConn
　　.Provider = "Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=1234;"
　　.Open MyData
End With

 

'删除和插入和更新SQL 

AdoConn.Execute (strSQL)

 

'检索SQL

Dim strSQL1 As String

strSQL1 = "SELECT * FROM TABLENAME"

'返回查询结果

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open strSQL1, AdoConn, adOpenKeyset, adLockOptimistic

'数据记录总数

rs.RecordCount

 

'循环结果项目名

For i = 0 To rs.Fields.Count - 1
　　 = rs.Fields(i).Name
Next i

'循环结果值

Do
　　DoEvents
　　For i = 0 To rs.Fields.Count - 1
　　　　 = rs(i)
　　Next i

　　rs.MoveNext
Loop While Not rs.EOF

'关闭
rs.Close
AdoConn.Close

Set rs = Nothing
Set AdoConn = Nothing