'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '把代码放在模块里,第一次用的时候需要在窗体上添加ADO控件,运行一次后可以把ADO删除 '无需在用到ADO控件了(我也不明白为什么可以这样) '此模块包括连接SQL数据库和执行SQL语句 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public strServerName As String '数据库服务器名 Public strDBName As String '数据库名 Public strUserName As String '登录用户名 Public strPass As String Public rs As ADODB.Recordset Public cn As ADODB.ConnectionPublic Function ConnectionDB(strServerName As String, strDBName As String, strUserName As String, strPass As String) As Long 'strServerName---数据库服务器名 'strDBName---数据库名 'strUserName---登录用户名 'strPass---用户密码 Dim blnConnState As Boolean On Error GoTo errHandle Set cn = New ADODB.Connection cn.ConnectionTimeout = 5 cn.CursorLocation = adUseClient cn.Provider = "SQLOLEDB.1;Persist Security Info=False;Initial Catalog=" & strDBName & ";User Id=" & strUserName & ";Password=" & strPass & ";Data Source=" & strServerName cn.Open blnConnState = GetState(cn.State) If blnConnState Then ConnectionDB = 1 '连接成功 End If
Exit Function errHandle: Select Case Err.Number Case -2147467259 ConnectionDB = 0 '连接失败 Case -2147217843 ConnectionDB = 3 '非法用户 Case Else ConnectionDB = 100 End Select End Function'GetState( )函数检查与数据库的连接状态,成功返回真值 Public Function GetState(intState As Integer) As Boolean Select Case intState Case adStateClosed GetState = False Case adStateOpen GetState = True End Select End Function'****************************************************** '--exesql()用于执行用户给定的select语句,实现更新,返回对应的记录集 '****************************************************** Public Function exesql(ByVal sqltxt As String) As ADODB.Recordset Dim rs As ADODB.Recordset sqltxt = Trim$(sqltxt) Set rs = New ADODB.Recordset '定义rst为recordset对象 Set rs.ActiveConnection = cn '指示Recordset 对象当前所属的 Connection 对象 rs.LockType = adLockOptimistic '指示编辑过程中对记录使用的锁定类型 _ 开放式记录锁定(逐条)。提供者使用开放式锁定,只在调用 Update 方法时锁定记录? rs.CursorType = adOpenKeyset '打开指定 Recordset 的 Connection 对象。 rs.Open sqltxt Set exesql = rs End Function
Public rs As ADODB.Recordset Public cn As ADODB.Connection你用这个当然可以了,不过要引用ado的
'把代码放在模块里,第一次用的时候需要在窗体上添加ADO控件,运行一次后可以把ADO删除
'无需在用到ADO控件了(我也不明白为什么可以这样)
'此模块包括连接SQL数据库和执行SQL语句
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public strServerName As String '数据库服务器名
Public strDBName As String '数据库名
Public strUserName As String '登录用户名
Public strPass As String
Public rs As ADODB.Recordset
Public cn As ADODB.ConnectionPublic Function ConnectionDB(strServerName As String, strDBName As String, strUserName As String, strPass As String) As Long
'strServerName---数据库服务器名
'strDBName---数据库名
'strUserName---登录用户名
'strPass---用户密码
Dim blnConnState As Boolean
On Error GoTo errHandle
Set cn = New ADODB.Connection
cn.ConnectionTimeout = 5
cn.CursorLocation = adUseClient
cn.Provider = "SQLOLEDB.1;Persist Security Info=False;Initial Catalog=" & strDBName & ";User Id=" & strUserName & ";Password=" & strPass & ";Data Source=" & strServerName
cn.Open
blnConnState = GetState(cn.State)
If blnConnState Then
ConnectionDB = 1 '连接成功
End If
Exit Function
errHandle:
Select Case Err.Number
Case -2147467259
ConnectionDB = 0 '连接失败
Case -2147217843
ConnectionDB = 3 '非法用户
Case Else
ConnectionDB = 100
End Select
End Function'GetState( )函数检查与数据库的连接状态,成功返回真值
Public Function GetState(intState As Integer) As Boolean
Select Case intState
Case adStateClosed
GetState = False
Case adStateOpen
GetState = True
End Select
End Function'******************************************************
'--exesql()用于执行用户给定的select语句,实现更新,返回对应的记录集
'******************************************************
Public Function exesql(ByVal sqltxt As String) As ADODB.Recordset
Dim rs As ADODB.Recordset
sqltxt = Trim$(sqltxt)
Set rs = New ADODB.Recordset '定义rst为recordset对象
Set rs.ActiveConnection = cn '指示Recordset 对象当前所属的 Connection 对象
rs.LockType = adLockOptimistic '指示编辑过程中对记录使用的锁定类型 _
开放式记录锁定(逐条)。提供者使用开放式锁定,只在调用 Update 方法时锁定记录?
rs.CursorType = adOpenKeyset '打开指定 Recordset 的 Connection 对象。
rs.Open sqltxt
Set exesql = rs
End Function
Public cn As ADODB.Connection你用这个当然可以了,不过要引用ado的