这是一段连接、打开、查询、显示的代码(借用了MSTOP 陈建华的代码): Option Explicit'引用2:Microsoft ActiveX Data Objects 2.7 Library '引用3:Microsoft ADO Ext.2.7 for DDL ado Security Dim m_Conn As ADODB.Connection Dim m_Rs As ADODB.Recordset' '创建一个连接(连接到Access) '函数名:CreateMdbConn '参数: DbConnection ADODB连接,MdbPath Access数据库路径,Provider JET数据库引擎名(默认:"Microsoft.Jet.OLEDB.4.0"),UserID 登录用户名,UserWord 登录密码 '返回值:TRUE 连接成功.FALSE 连接失败. '例: CreateMdbConn P_Cnn,"C:\DEMO.MDB" Public Function CreateMdbConn(DbConnection As ADODB.Connection, MdbPath As String, _ Optional Provider = "Microsoft.Jet.OLEDB.4.0", _ Optional UserID As String = "admin", _ Optional UserWord As String = "") As Boolean Dim ConStr As String
On Error Resume Next
If DbConnection.State = adStateOpen And Not IsEmpty(adStateOpen) Then DbConnection.Close End If '/------------------------------------------------------------------ ConStr = "Provider=" & Provider & ";" & _ "Data Source=" & MdbPath & ";" & _ "Mode=Share Deny Read|Share Deny Write;Persist Security Info=False;" & _ "Jet OLEDB:Database Password=" & UserWord & ";" ' & _ '"User ID=" & UserID & ";" DbConnection.ConnectionString = ConStr DbConnection.Open If Err.Number <> 0 Then Err.Clear CreateMdbConn = False Else CreateMdbConn = True End If End Function ' '打开一个记录集 '函数名:OpenRs '参数: DbCnn ADODB连接,StrSql SQL查询语句,SetNothing 非连接方式(TRUE默认).连接方式(FALSE) '返回值:记录集 '例: OpenRs P_CNN,"SELECT ACHGOODS.* FROM ACHGOODS WHERE GDSID='001' Public Function OpenRs(ByRef DbCnn As ADODB.Connection, StrSql As String, Optional SetConnect As Boolean = True, Optional CacheSize As Long = 60) As ADODB.Recordset Dim Rs As New ADODB.Recordset If SetConnect Then '使用非连接 Rs.CursorLocation = adUseClient '使用客户端游标 Rs.LockType = adLockBatchOptimistic '开放式批更新 Rs.CursorType = adOpenKeyset '键集游标 Else '使用连接(主要用于更新 二进制,OLE 字段) Rs.CursorLocation = adUseClient Rs.CursorType = adOpenKeyset Rs.LockType = adLockOptimistic '记录锁定 End If Rs.CacheSize = CacheSize Rs.Open StrSql, DbCnn '执行SQL If SetConnect Then Set Rs.ActiveConnection = Nothing '设置非连接 Set OpenRs = Rs.Clone Rs.Close Set Rs = Nothing End Function Private Sub Command1_Click() Dim MyMdbFile As String Dim cSql As StringMyMdbFile = App.Path & "\data.mdb"cSql = "Select * From Table" Set m_Conn = New ADODB.Connection If CreateMdbConn(m_Conn, MyMdbFile, , "ME", "1234567") = True Then Set m_Rs = New ADODB.Recordset Set m_Rs = OpenRs(m_Conn, cSql, True) Set DataGrid1.DataSource = m_Rs Me.Refresh Set m_Rs = Nothing End If End Sub
Option Explicit'引用2:Microsoft ActiveX Data Objects 2.7 Library
'引用3:Microsoft ADO Ext.2.7 for DDL ado Security
Dim m_Conn As ADODB.Connection
Dim m_Rs As ADODB.Recordset'
'创建一个连接(连接到Access)
'函数名:CreateMdbConn
'参数: DbConnection ADODB连接,MdbPath Access数据库路径,Provider JET数据库引擎名(默认:"Microsoft.Jet.OLEDB.4.0"),UserID 登录用户名,UserWord 登录密码
'返回值:TRUE 连接成功.FALSE 连接失败.
'例: CreateMdbConn P_Cnn,"C:\DEMO.MDB"
Public Function CreateMdbConn(DbConnection As ADODB.Connection, MdbPath As String, _
Optional Provider = "Microsoft.Jet.OLEDB.4.0", _
Optional UserID As String = "admin", _
Optional UserWord As String = "") As Boolean
Dim ConStr As String
On Error Resume Next
If DbConnection.State = adStateOpen And Not IsEmpty(adStateOpen) Then
DbConnection.Close
End If
'/------------------------------------------------------------------
ConStr = "Provider=" & Provider & ";" & _
"Data Source=" & MdbPath & ";" & _
"Mode=Share Deny Read|Share Deny Write;Persist Security Info=False;" & _
"Jet OLEDB:Database Password=" & UserWord & ";" ' & _
'"User ID=" & UserID & ";"
DbConnection.ConnectionString = ConStr
DbConnection.Open
If Err.Number <> 0 Then
Err.Clear
CreateMdbConn = False
Else
CreateMdbConn = True
End If
End Function
'
'打开一个记录集
'函数名:OpenRs
'参数: DbCnn ADODB连接,StrSql SQL查询语句,SetNothing 非连接方式(TRUE默认).连接方式(FALSE)
'返回值:记录集
'例: OpenRs P_CNN,"SELECT ACHGOODS.* FROM ACHGOODS WHERE GDSID='001'
Public Function OpenRs(ByRef DbCnn As ADODB.Connection, StrSql As String, Optional SetConnect As Boolean = True, Optional CacheSize As Long = 60) As ADODB.Recordset
Dim Rs As New ADODB.Recordset
If SetConnect Then '使用非连接
Rs.CursorLocation = adUseClient '使用客户端游标
Rs.LockType = adLockBatchOptimistic '开放式批更新
Rs.CursorType = adOpenKeyset '键集游标
Else '使用连接(主要用于更新 二进制,OLE 字段)
Rs.CursorLocation = adUseClient
Rs.CursorType = adOpenKeyset
Rs.LockType = adLockOptimistic '记录锁定
End If
Rs.CacheSize = CacheSize
Rs.Open StrSql, DbCnn '执行SQL
If SetConnect Then Set Rs.ActiveConnection = Nothing '设置非连接
Set OpenRs = Rs.Clone
Rs.Close
Set Rs = Nothing
End Function
Private Sub Command1_Click()
Dim MyMdbFile As String
Dim cSql As StringMyMdbFile = App.Path & "\data.mdb"cSql = "Select * From Table"
Set m_Conn = New ADODB.Connection
If CreateMdbConn(m_Conn, MyMdbFile, , "ME", "1234567") = True Then
Set m_Rs = New ADODB.Recordset
Set m_Rs = OpenRs(m_Conn, cSql, True)
Set DataGrid1.DataSource = m_Rs
Me.Refresh
Set m_Rs = Nothing
End If
End Sub