VBGood网站全文搜索 Google

搜索VBGood全站网页(全文搜索)
首页 - 经验之谈 - 制作一个可重复使用的数据库class
发表评论(0)作者:Sam Huggill, 平台:VB6.0+Win98, 阅读:10474, 日期:2001-04-30
Make a reusable database class

By Sam Huggill

Introduction

This database class might need you to change one db path setting (which could be improved by using a public variable). Otherwise it contains prettry much the same functions as the previous article apart from the addition of the repair and compact functions.

Add a new class module and rename it cDataFunc. Copy the following code into it:

  
Option Explicit

注释:Public members of this module
Public iID As Integer
Private db As Database
Private RS As Recordset

Public Sub OpenDB(dbName As String)
   
  注释:Open the database
  Set db = OpenDatabase(dbName)

End Sub

Function GetNextRecord(sRecordSet As String, sField As String, iPos As Integer) As String

  注释:Open the recordset and select the next record
  Set RS = db.OpenRecordset("SELECT * FROM " & sRecordSet & " WHERE _  
    " & sField & " =" & iPos, dbOpenDynaset)

  注释:Return the field value
  iID = "" & RS.Fields(sField).Value

  注释:Close the recordset
  RS.Close

End Function

Function UpDate(sRecordSet As String, sField As String, _  
  sValue As String, sFieldToUpdate As String, sValueToUpdate As String)

  注释:Open the Contact table
  Set RS = db.OpenRecordset("SELECT * FROM " & sRecordSet _  
    & " WHERE " & sField & " = " & sValue, dbOpenDynaset)

  With RS

      注释:Set it in edit more
      .Edit

      注释:Update the following fields
      .Fields(sFieldToUpdate).Value = sValueToUpdate

      注释:Update the table
      .UpDate

      注释:Close it
      .Close
  End With

End Function

Function AddToDatabase(sRecordSet As String,_
  sFieldToAdd As String, sValueToUpdate As String)

  注释:Open the Contact table
  Set RS = db.OpenRecordset("SELECT * FROM" & sRecordSet _
    & " Contact", dbOpenDynaset)

  With RS

    注释:Set it to Add mode
    .AddNew

    注释:Enter the field values
    .Fields(sFieldToUpdate).Value = sValueToUpdate

    注释:Update it
    .UpDate

    注释:Close it
    .Close
  End With

End Function

Function DeleteFromDatabase(sRecordSet As String, _
  sField As String, sValue As String)

  注释:Open the Contact table
  Set RS = db.OpenRecordset("SELECT * FROM"_
    & sRecordSet & " WHERE" & sField & " = " & sValue & "注释:", dbOpenDynaset)

  With RS

    注释:Set it to delete mode
    .Delete

    注释:Close it
    .Close
  End With

End Function

Function RepairDatabase(sDBName As String)
  db.Close

  DBEngine.RepairDatabase sDBName

  OpenDB (App.Path & "\demo.mdb")
End Function

Sub CompactDatabase(sDBName As String, sBackup As String)

  db.Close

  注释:Check to see if the tempory backup exists
  If Len(Dir$(sBackup)) > 0 Then 注释:Backup exists

    Kill sBackup 注释:Delete it

  End If

  注释:Compact the database to the tempory one
  DBEngine.CompactDatabase sDBName, sBackup

  注释:Delete the current one
  Kill sDBName

  注释:Compact the backup to the actual database
  DBEngine.CompactDatabase sBackup, sDBName

  注释:And remove the backup database
  Kill sBackup

  OpenDB (App.Path & "\demo.mdb")

End Sub

Sub BackupDatabase(sDBPath As String, sOutput As String)

  If Len(Dir$(sDBPath)) > 0 Then 注释:Backup exists
    Kill sDBPath 注释:Delete it
  End If

  注释:Output to new database
  DBEngine.CompactDatabase sOutput, sDBName

End Sub

Add a new form (frmTest). To the form add six command buttons (cmdAdd, cmdDelete, cmdCompact, cmdUpdate, cmdNext and cmdRepair.) Add three text boxes and a label (txtName, txtPhone, txtNotes and lblID). Copy the following code into the form注释:s General Declarations procedure:

注释:Private declarations for the database and recordset
Private db As Database
Private RS As Recordset
Private m_cData As cDataFunc

Private Sub cmdAdd_Click()
  注释:Set the global variables
  sName = txtName
  sPhone = txtPhone
  sNotes = txtNotes

  注释:Call the add function
  m_cData.AddToDatabase "Contact", "Name", sName
End Sub

Private Sub cmdCompact_Click()
  m_cData.CompactDatabase App.Path & "\demo.mdb", App.Path & "\backup.mdb"
End Sub

Private Sub cmdDelete_Click()
  注释:Set the global variables
  sName = txtNamer
  sPhone = txtPhone
  sNotes = txtNotes

  注释:Call the delete function
  m_cData.DeleteFromDatabase "Contact", "Name", sName
End Sub

Private Sub cmdNext_Click()
  注释:Call the next record function
  m_cData.GetNextRecord "Contact", "Name", iID
End Sub

Private Sub cmdRepair_Click()
  m_cData.RepairDatabase App.Path & "\demo.mdb"
End Sub

Private Sub cmdUpdate_Click()
  注释:Set the global variables
  sName = txtName
  sPhone = txtPhone
  sNotes = txtNotes
  iID = lblID

  注释:Call the Edit function
  Call m_cData.Update("Contact", "PersonID", lblID)
End Sub

Private Sub Form_Load()
  Set m_cData = New cDataFunc
  m_cData.OpenDB (App.Path & "\demo.mdb")
End Sub

Private Sub Form_Unload(Cancel As Integer)
  Set m_cData = Nothing
End Sub