public myAdoCn As ADODB.Connection Set myAdoCn = New ADODB.Connection myAdoCn.ConnectionString = "Driver={SQL Server};Server=服务器名;UID=用户名;PWD=密码;Database=数据库名;" myAdoCn.Open
'******************************************************************* '作用:执行数据的打开关闭操作(返回对象)默认打开当前目录下的数据库 'Ado_Conn()返回Connection对象 'Set_Conn()设置连接字符串 '******************************************************************* Option Explicit Private m_Cn As New ADODB.Connection 'Private m_Rs As New ADODB.RecordsetPrivate Sub Class_Initialize() Set m_Cn = New ADODB.Connection m_Cn = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=yw;Data Source=chenlin;Connect Timeout=210"
m_Cn.Open End SubProperty Get GetConnection() As ADODB.Connection Set GetConnection = m_Cn End PropertyProperty Let SetConnection(ConString As String) If m_Cn.State <> adStateClosed Then m_Cn.Close m_Cn.ConnectionString = ConString m_Cn.Open End PropertyPrivate Sub Class_Terminate() If m_Cn.State <> adStateClosed Then m_Cn.Close Set m_Cn = Nothing End Sub 在调用窗口中申明实体:Option Explicit Dim Clsado As New Cls_DBase
dim rs as new adodb.recordset dim sql as string sql="select workid,workername,sex,departmenet from workerfiles where id ='" & text1 & "' " rs.open sql,m_Cn if not rs.eof then text2=rs(1) text3=rs(2) text4=rs(3) else msgbox "无此编号!" end if rs.close
'----------------------------------------------------------------- 'modle: Option Explicit Public Conn As New ADODB.Connection Public Rs As New ADODB.Recordset '' Public Sub dbConnect() On Error GoTo errHandle If Conn Is Nothing Then Set Conn = New ADODB.Connection End If If Rs Is Nothing Then Set Rs = New ADODB.Recordset
End If 'nmmis2003 is ODBC Conn.ConnectionString = "DSN=nmmis2003" Conn.CursorLocation = adUseClient 'recorder count is right 'Conn.CursorLocation = adUseServer 'recorder count=-1
Conn.Open Exit Sub errHandle: MsgBox Err.Description
End SubPublic Function dbInsert(strSQL As String) As Boolean On Error GoTo errHandle Conn.Execute (strSQL) dbInsert = True Exit Function errHandle: dbInsert = False Debug.Print Err.DescriptionEnd FunctionPublic Function dbSelect(strSQL As String) As ADODB.Recordset On Error GoTo errHandle If Trim(strSQL) <> "" Then Set Rs = Conn.Execute(strSQL) If Not (Rs Is Nothing) Then Set dbSelect = Rs Else Set dbSelect = Nothing End If Else Set dbSelect = Nothing End If
Exit Function
errHandle: Set dbSelect = Nothing MsgBox Err.Description
End FunctionPublic Function dbUpdate(strSQL As String) As Boolean On Error GoTo errHandle Conn.Execute (strSQL) dbUpdate = True Exit Function errHandle: dbUpdate = False MsgBox Err.Description End Function '------------------------------------------------------------------- 'form: Private Sub Command1_Click() Dim strT As String strT = "select * from class"Set Rs = dbSelect(strT) If Not (Rs.BOF Or Rs.EOF) Then MsgBox CStr(Rs.Fields(0)) MsgBox CStr(Rs.Fields(1)) MsgBox CStr(Rs.Fields(2)) End If End SubPrivate Sub Form_Load() Call dbConnect
Set myAdoCn = New ADODB.Connection
myAdoCn.ConnectionString = "Driver={SQL Server};Server=服务器名;UID=用户名;PWD=密码;Database=数据库名;"
myAdoCn.Open
'作用:执行数据的打开关闭操作(返回对象)默认打开当前目录下的数据库
'Ado_Conn()返回Connection对象
'Set_Conn()设置连接字符串
'*******************************************************************
Option Explicit
Private m_Cn As New ADODB.Connection
'Private m_Rs As New ADODB.RecordsetPrivate Sub Class_Initialize()
Set m_Cn = New ADODB.Connection
m_Cn = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=yw;Data Source=chenlin;Connect Timeout=210"
m_Cn.Open
End SubProperty Get GetConnection() As ADODB.Connection
Set GetConnection = m_Cn
End PropertyProperty Let SetConnection(ConString As String)
If m_Cn.State <> adStateClosed Then m_Cn.Close
m_Cn.ConnectionString = ConString
m_Cn.Open
End PropertyPrivate Sub Class_Terminate()
If m_Cn.State <> adStateClosed Then m_Cn.Close
Set m_Cn = Nothing
End Sub
在调用窗口中申明实体:Option Explicit
Dim Clsado As New Cls_DBase
dim sql as string
sql="select workid,workername,sex,departmenet from workerfiles where id ='" & text1 & "' "
rs.open sql,m_Cn
if not rs.eof then
text2=rs(1)
text3=rs(2)
text4=rs(3)
else
msgbox "无此编号!"
end if
rs.close
'modle:
Option Explicit
Public Conn As New ADODB.Connection
Public Rs As New ADODB.Recordset
''
Public Sub dbConnect()
On Error GoTo errHandle
If Conn Is Nothing Then
Set Conn = New ADODB.Connection
End If
If Rs Is Nothing Then
Set Rs = New ADODB.Recordset
End If
'nmmis2003 is ODBC
Conn.ConnectionString = "DSN=nmmis2003"
Conn.CursorLocation = adUseClient 'recorder count is right
'Conn.CursorLocation = adUseServer 'recorder count=-1
Conn.Open
Exit Sub
errHandle:
MsgBox Err.Description
End SubPublic Function dbInsert(strSQL As String) As Boolean
On Error GoTo errHandle
Conn.Execute (strSQL)
dbInsert = True
Exit Function
errHandle:
dbInsert = False
Debug.Print Err.DescriptionEnd FunctionPublic Function dbSelect(strSQL As String) As ADODB.Recordset
On Error GoTo errHandle
If Trim(strSQL) <> "" Then
Set Rs = Conn.Execute(strSQL)
If Not (Rs Is Nothing) Then
Set dbSelect = Rs
Else
Set dbSelect = Nothing
End If
Else
Set dbSelect = Nothing
End If
Exit Function
errHandle:
Set dbSelect = Nothing
MsgBox Err.Description
End FunctionPublic Function dbUpdate(strSQL As String) As Boolean
On Error GoTo errHandle
Conn.Execute (strSQL)
dbUpdate = True
Exit Function
errHandle:
dbUpdate = False
MsgBox Err.Description
End Function
'-------------------------------------------------------------------
'form:
Private Sub Command1_Click()
Dim strT As String
strT = "select * from class"Set Rs = dbSelect(strT)
If Not (Rs.BOF Or Rs.EOF) Then
MsgBox CStr(Rs.Fields(0))
MsgBox CStr(Rs.Fields(1))
MsgBox CStr(Rs.Fields(2))
End If
End SubPrivate Sub Form_Load()
Call dbConnect
End Sub