操作数据库的一个简单类

'数据库连接
Public conn As ADODB.Connection
'保持属性值的局部变量
Private mvarconnstring As String '局部复制
Public Property Let connstring(ByVal vData As String)
'向属性指派值时使用，位于赋值语句的左边。
'Syntax: X.connstring = 5
    mvarconnstring = vData
End Property

Public Property Get connstring() As String
'检索属性值时使用，位于赋值语句的右边。
'Syntax: Debug.Print X.connstring
    connstring = mvarconnstring
End Property
'打开数据库连接
Public Function dbOpen()
 Set conn = New ADODB.Connection
 conn.ConnectionString = connstring
 conn.ConnectionTimeout = 30
 conn.Open
End Function
'关闭数据库连接
Public Function dbClose()
 conn.Close
' conn = Null
End Function

Public Function recordcount(ByVal sqlstring As String) As Integer
 dbOpen
 Set rs = New ADODB.Recordset
 rs.Open sqlstring, conn, adOpenStatic, adLockReadOnly, adCmdText
 recordcount = rs.recordcount
 rs.Close
 dbClose
End Function

***************************************************************************

实用实例


Public db As XMDB

Private Sub Command1_Click()
Dim user_name As String
Dim user_pwd As String
user_name = Trim(Text1.Text)
user_pwd = Trim(Text2.Text)
'MsgBox ("用户名是:" + user_name + Chr(10) + "密码是:" & user_pwd)

 Dim int_count As Integer
 int_count = db.recordcount("select * from tb_operator where oper_name='" & user_name & "' And oper_pwd='" & user_pwd & "'")
 If int_count > 0 Then
   MsgBox ("登陆成功!")
 Else
   MsgBox ("用户名或密码错误!")
 End If
 

'Set conn = New ADODB.Connection
'conn.ConnectionString = "Driver={sql server};server=server;uid=sa;pwd=;database=dh_cms"
'conn.ConnectionTimeout = 30
'conn.Open

'Set rs = New ADODB.Recordset
'rs.Open "select * from tb_operator where oper_name='" & user_name & "' And oper_pwd='" & user_pwd & "'", conn, adOpenStatic, adLockReadOnly, adCmdText
'If rs.EOF Or rs.BOF Then
  'MsgBox ("用户名或密码错误!")
'Else
  'MsgBox ("登陆成功!你的id是" & rs.Fields("oper_id").Value)
'End If
'rs.Close
'conn.Close
'Set conn = Nothing
End Sub

Private Sub Command2_Click()
 
 MsgBox (db.connstring)
End Sub

Private Sub Form_Load()
  OpenConn
  list_item_add
End Sub

Private Sub Timer1_Timer()
'MsgBox ("你好!")
End Sub

Private Sub OpenConn()
  Set db = New XMDB
  db.connstring = "Driver={sql server};server=server;uid=sa;pwd=;database=dh_cms"
End Sub

Private Sub list_item_add()
  db.dbOpen
  Set rs = New ADODB.Recordset
  Dim str_sql As String
  str_sql = "select * from tb_operator"
  rs.Open str_sql, db.conn, adOpenStatic, adLockReadOnly, adCmdText
  Do While Not rs.EOF
   List1.AddItem (rs.Fields("oper_name").Value)
   rs.MoveNext
  Loop
  rs.Close
  db.dbClose
End Sub