使用ADP进行mdb数据库开发的实现方案
优点:
1. 不依赖链接表
2. 纯粹使用ADO
3. 可轻松将mdb数据库应用升迁到SQLServer数据库应用
4. 可使用记录集作为报表的数据来源
5. 应用程序中无数据,数据安全容易得到保障
缺点:
1. 编程量相对较大
2. 开发不直观
实现原理:
1. ADO数据库连接的提供者一定是 “Microsoft.Access.OLEDB.10.0”,这样,记录集才能绑定到窗体和报表
2. ADO数据库连接的数据提供者是 “Microsoft.Jet.OLEDB.4.0” 或者 “Microsoft.ACE.OLEDB.12.0” 或者 “Microsoft.ACE.OLEDB.14.0”,即 mdb 数据库的提供者
3. 新建 ADP 时,取消数据库连接,即保证 ADP 处于断开状态, ADP 中无连接字符串。
对ADO进行了一次封装,作为全局的连接对象,放在 modADO 模块中
Option Compare Database
Option Explicit
' 作者:朱亦文(zhuyiwen)
' 时间:2013-07-11
Public Const DBFileName = "Data.mdb"
Public Const DBProvider = "Microsoft.Access.OLEDB.10.0"
Public Const DBDataProvider = "Microsoft.Jet.OLEDB.4.0"
Public Const DBPersistSecurityInfo = "False"
Public Const DBCursorLocation = adUseServer
Private m_Conn As ADODB.Connection
Private m_DBErrorNum As Integer
Private m_DBErrorMessage As String
Public Property Get DBErrorNumber() As Integer
DBErrorNumber = m_DBErrorNum
End Property
Public Property Get DBErrorMessage() As String
DBErrorMessage = m_DBErrorMessage
End Property
Public Property Get DBConnectionString() As String
DBConnectionString = _
"Provider=" & DBProvider & ";" & _
"Persist Security Info=" & DBPersistSecurityInfo & ";" & _
"Data Source=" & CurrentProject.Path & "\" & DBFileName & ";" & _
"Data Provider=" & DBDataProvider
End Property
Public Property Get DBConnection() As ADODB.Connection
On Error GoTo err_GetDBConnection
If m_Conn Is Nothing Then
Set m_Conn = New ADODB.Connection
m_Conn.CursorLocation = DBCursorLocation
End If
If m_Conn.State <> adStateOpen Then
m_Conn.Open DBConnectionString
End If
Set DBConnection = m_Conn
Exit Property
err_GetDBConnection:
m_DBErrorNum = Err.Number
m_DBErrorMessage = Err.Description
Err.Clear
End Property
Public Function DBClearError() As Boolean
m_DBErrorNum = 0
m_DBErrorMessage = ""
DBClearError = True
End Function
Public Function DBCloseConnection() As Boolean
On Error GoTo err_DBCloseConnection
If m_Conn Is Nothing Then
DBCloseConnection = True
End If
If m_Conn.State <> adStateClosed Then
m_Conn.Close
End If
Set m_Conn = Nothing
DBCloseConnection = True
Exit Function
err_DBCloseConnection:
m_DBErrorNum = Err.Number
m_DBErrorMessage = Err.Description
Err.Clear
End Function
|
这样的好处,DBConnection 就相当于CurrentProject.Connection,便于程序移植。
附 件:
ADO报表示例下载