Option Explicit Dim Conn As New ADODB.ConnectionPrivate Const DLL_PROCESS_DETACH As Long = 0 Private Const DLL_PROCESS_ATTACH As Long = 1 Private Const DLL_THREAD_ATTACH As Long = 2 Private Const DLL_THREAD_DETACH As Long = 3Private Function DLLMain(ByVal hModule As Long, ByVal dwReason As Long, ByVal lpReserved As Long) As Long Select Case dwReason Case DLL_PROCESS_ATTACH Case DLL_PROCESS_DETACH Set Conn = Nothing Case DLL_THREAD_ATTACH Case DLL_THREAD_DETACH End Select DLLMain = 1 End FunctionSub Main()End Sub Public Function OpenConnection(ByVal ConnStr As String) As Long On Error GoTo err If Conn.State = 1 Then Conn.Close Conn.ConnectionString = StrConv(ConnStr, vbUnicode) Conn.Open Exit Function err: OpenConnection = err.Number End FunctionPublic Function GetSqlFirstColStr(ByVal strSql As Long) As String On Error GoTo err Dim oRs As New ADODB.Recordset Dim retStr As String With oRs .ActiveConnection = Conn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly .Open StrConv(strSql, vbUnicode) If Not .EOF Then retStr = .Fields(0) & "" .Close End With Set oRs = Nothing err: If err.Number <> 0 Then retStr = err.Description GetSqlFirstColStr = StrConv(retStr, vbFromUnicode) End FunctionPublic Function ExecuteSQL(ByVal strSql As Long) As Long On Error Resume Next Conn.Execute StrConv(strSql, vbUnicode) ExecuteSQL = err.Number End FunctionPublic Function GetSqlFirstColNum(ByVal strSql As Long) As Long On Error GoTo err Dim oRs As New ADODB.Recordset With oRs .ActiveConnection = Conn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly .Open StrConv(strSql, vbUnicode) If Not .EOF Then GetSqlFirstColNum = Val(.Fields(0) & "") .Close End With Set oRs = Nothing Exit Function err: End Function
小C的帖子一定要跟,给你个最简单的,封装了连接和数据集 Public Function GetRS(ByVal sSql As String) As ADODB.Recordset Dim sConnect As String Dim cnGet As ADODB.Connection linkcn = " Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=数据库;Data Source=." sConnect = linkcn ' 打开连接 Set cnGet = New ADODB.Connection
Dim rsGet As ADODB.Recordset Set rsGet = New ADODB.Recordset rsGet.CursorLocation = adUseClient rsGet.Open sSql, cnGet, adOpenStatic, adLockReadOnly Set GetRS = rsGet Set rsGet = Nothing Set cnGet = Nothing End Function'调用 Dim rs As New ADODB.Recordset, sSql As String sSql = "select phone_no from 表 where line_no='" + lineNo + "'" Set rs = GetRS(sSql)
我这样封装的,不一定科学,班门弄斧一哈:'注意调用此函数的函数或过程要负责关闭该记录集并设为nothing Public Function Sql2Rst4Edit(strSql As String) As ADODB.Recordset Dim RST As New ADODB.Recordset With RST .ActiveConnection = CurrentProject.Connection .Source = strSql .CursorLocation = adUseClient .CursorType = adOpenForwardOnly .LockType = adLockOptimistic .Open Options:=adCmdText End With Set Sql2Rst4Edit = RST Set RST = Nothing End Function类似地还有:Sql2Rst4Read,Table2Rst4Edit,Table2Rst4Read等等因为每次都是调用这几个写好的函数,以至于我到现在都对ADO记录集初始化的一些常见语句都很陌生:)
直接写成类,封成DLL了. 有需要的可以找我.
其实还是看使用频率问题了,一般使用频率较高的一些函数,不论是否很简单,都写成标准模块,用起来很方便,不过有时候会造成对代码生疏,比如这个 public sub showerr(byval pErr as string) msgbox perr & err.Description,vbokonly,"错误" end sub 用时间长了,单写msgbox语句有时候都写错,哈。
Microsoft ADO Ext. 2.8 for DDL and Security封装的最好。
Dim Conn As New ADODB.ConnectionPrivate Const DLL_PROCESS_DETACH As Long = 0
Private Const DLL_PROCESS_ATTACH As Long = 1
Private Const DLL_THREAD_ATTACH As Long = 2
Private Const DLL_THREAD_DETACH As Long = 3Private Function DLLMain(ByVal hModule As Long, ByVal dwReason As Long, ByVal lpReserved As Long) As Long
Select Case dwReason
Case DLL_PROCESS_ATTACH
Case DLL_PROCESS_DETACH
Set Conn = Nothing
Case DLL_THREAD_ATTACH
Case DLL_THREAD_DETACH
End Select DLLMain = 1
End FunctionSub Main()End Sub
Public Function OpenConnection(ByVal ConnStr As String) As Long
On Error GoTo err
If Conn.State = 1 Then Conn.Close
Conn.ConnectionString = StrConv(ConnStr, vbUnicode)
Conn.Open
Exit Function
err:
OpenConnection = err.Number
End FunctionPublic Function GetSqlFirstColStr(ByVal strSql As Long) As String
On Error GoTo err
Dim oRs As New ADODB.Recordset
Dim retStr As String
With oRs
.ActiveConnection = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open StrConv(strSql, vbUnicode)
If Not .EOF Then retStr = .Fields(0) & ""
.Close
End With
Set oRs = Nothing
err:
If err.Number <> 0 Then retStr = err.Description
GetSqlFirstColStr = StrConv(retStr, vbFromUnicode)
End FunctionPublic Function ExecuteSQL(ByVal strSql As Long) As Long
On Error Resume Next
Conn.Execute StrConv(strSql, vbUnicode)
ExecuteSQL = err.Number
End FunctionPublic Function GetSqlFirstColNum(ByVal strSql As Long) As Long
On Error GoTo err
Dim oRs As New ADODB.Recordset
With oRs
.ActiveConnection = Conn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open StrConv(strSql, vbUnicode)
If Not .EOF Then GetSqlFirstColNum = Val(.Fields(0) & "")
.Close
End With
Set oRs = Nothing
Exit Function
err:
End Function
Public Function GetRS(ByVal sSql As String) As ADODB.Recordset
Dim sConnect As String
Dim cnGet As ADODB.Connection
linkcn = " Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=数据库;Data Source=."
sConnect = linkcn
' 打开连接
Set cnGet = New ADODB.Connection
cnGet.ConnectionTimeout = 300
cnGet.CommandTimeout = 300
cnGet.Open sConnect
Dim rsGet As ADODB.Recordset
Set rsGet = New ADODB.Recordset
rsGet.CursorLocation = adUseClient
rsGet.Open sSql, cnGet, adOpenStatic, adLockReadOnly
Set GetRS = rsGet
Set rsGet = Nothing
Set cnGet = Nothing
End Function'调用
Dim rs As New ADODB.Recordset, sSql As String
sSql = "select phone_no from 表 where line_no='" + lineNo + "'"
Set rs = GetRS(sSql)
linkcn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\aa.mdb;Persist Security Info=False;"
Public Function Sql2Rst4Edit(strSql As String) As ADODB.Recordset
Dim RST As New ADODB.Recordset
With RST
.ActiveConnection = CurrentProject.Connection
.Source = strSql
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open Options:=adCmdText
End With
Set Sql2Rst4Edit = RST
Set RST = Nothing
End Function类似地还有:Sql2Rst4Read,Table2Rst4Edit,Table2Rst4Read等等因为每次都是调用这几个写好的函数,以至于我到现在都对ADO记录集初始化的一些常见语句都很陌生:)
有需要的可以找我.
public sub showerr(byval pErr as string)
msgbox perr & err.Description,vbokonly,"错误"
end sub
用时间长了,单写msgbox语句有时候都写错,哈。