Public db As Connection Set db = New Connection db.CursorLocation = adUseClient db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\yourBase"
使用ADO.' '创建一个连接(连接到ACCESS) '函数名:CreateMdbConn '参数: DbConnection ADODB连接,MdbPath ACCESS数据库路径,Provider JET引擎版本,UserID 登录用户名,UserWord 登录密码 '返回值:TRUE 连接成功.FALSE 连接失败. '例: CreateMdbConn p_cnn,"C:\DEMO.MDB","sa","123" Public Function CreateMdbConn(ByRef 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 & ";" & _ "Jet OLEDB:Database Password=" & UserWord & ";" & _ "User ID=" & UserID & ";"
DbConnection.ConnectionString = ConStr DbConnection.Open DoEvents If Err.Number = 0 Then DbStyle = "JET" CreateMdbConn = True Else Err.Clear DbStyle = "" CreateMdbConn = False End If End Function ' '打开一个记录集 '函数名:RsOpen '参数: DbCnn ADODB连接,StrSql SQL查询语句,SetNothing 非连接方式(TRUE默认).连接方式(FALSE) '返回值:记录集 '例: RsOpen P_CNN,"SELECT ACHGOODS.* FROM ACHGOODS WHERE GDSID='001' Function RsOpen(ByRef DbCnn As ADODB.Connection, _ StrSql As String, _ Optional SetConnect As Boolean = True) As ADODB.Recordset
Dim Rs As New ADODB.Recordset
On Error Resume Next
If SetConnect Then '使用非连接 Rs.CursorLocation = adUseClient '使用客户端游标 Rs.LockType = adLockBatchOptimistic '开放式批更新 Rs.CursorType = adOpenKeyset '键集游标 Else '使用连接(主要用于更新二进制字段) Rs.CursorLocation = adUseClient Rs.CursorType = adOpenKeyset Rs.LockType = adLockOptimistic '记录锁定 End If Rs.Open StrSql, DbCnn '执行SQL If SetConnect Then Set Rs.ActiveConnection = Nothing '设置非连接
If Err.Number = 0 Then Set RsOpen = Rs.Clone Else Set RsOpen = Nothing End If Rs.Close Set Rs = Nothing End Function
用如用data控件,打上vb sp5,否则用adodc控件。
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\yourBase"
'创建一个连接(连接到ACCESS)
'函数名:CreateMdbConn
'参数: DbConnection ADODB连接,MdbPath ACCESS数据库路径,Provider JET引擎版本,UserID 登录用户名,UserWord 登录密码
'返回值:TRUE 连接成功.FALSE 连接失败.
'例: CreateMdbConn p_cnn,"C:\DEMO.MDB","sa","123"
Public Function CreateMdbConn(ByRef 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 & ";" & _
"Jet OLEDB:Database Password=" & UserWord & ";" & _
"User ID=" & UserID & ";"
DbConnection.ConnectionString = ConStr
DbConnection.Open
DoEvents If Err.Number = 0 Then
DbStyle = "JET"
CreateMdbConn = True
Else
Err.Clear
DbStyle = ""
CreateMdbConn = False
End If
End Function
'
'打开一个记录集
'函数名:RsOpen
'参数: DbCnn ADODB连接,StrSql SQL查询语句,SetNothing 非连接方式(TRUE默认).连接方式(FALSE)
'返回值:记录集
'例: RsOpen P_CNN,"SELECT ACHGOODS.* FROM ACHGOODS WHERE GDSID='001'
Function RsOpen(ByRef DbCnn As ADODB.Connection, _
StrSql As String, _
Optional SetConnect As Boolean = True) As ADODB.Recordset
Dim Rs As New ADODB.Recordset
On Error Resume Next
If SetConnect Then '使用非连接
Rs.CursorLocation = adUseClient '使用客户端游标
Rs.LockType = adLockBatchOptimistic '开放式批更新
Rs.CursorType = adOpenKeyset '键集游标
Else '使用连接(主要用于更新二进制字段)
Rs.CursorLocation = adUseClient
Rs.CursorType = adOpenKeyset
Rs.LockType = adLockOptimistic '记录锁定
End If Rs.Open StrSql, DbCnn '执行SQL
If SetConnect Then Set Rs.ActiveConnection = Nothing '设置非连接
If Err.Number = 0 Then
Set RsOpen = Rs.Clone
Else
Set RsOpen = Nothing
End If Rs.Close
Set Rs = Nothing
End Function