Beginning databases

There are many different ways in which you can access databases. The latest, and probably best is ADO (Active X Data Objects). I prefer using DAO, as you will see in this HowTo.

When using DAO you must remember that a table is called a 'recordset' and is declared in that way. This recordset exposes several methods that we can use to manipulate a database. The Edit, AddNew and Delete methods will be used in this HowTo for the main functions.

Before you start make sure that you have a reference to Microsoft DAO Object Library. Do this by clicking Project, References. Scroll down until you see Microsoft DAO 3.x Object Library and check the box next to it. Then click ok.

Add a new form (frmTest). Add four command buttons, cmdAdd, cmdDelete, cmdGet and cmdUpdate. Add three text boxes, txtName, txtPhone and txtNotes. Copy this code into the form:

'Private declarations for the 
'   database and recordset 
Private DB As Database 
Private RS As Recordset 

Function EditRecord(sName As String) 
  'Open the Contact table  
  Set RS = DB.OpenRecordset( _
      "SELECT * FROM Contact WHERE Name _
        = '" & sName & "'", dbOpenDynaset) 

  With RS 
    'Set it in edit more  
    .Edit 

    'Update the following fields  
    .Fields("Name").Value = txtName.Text 
    .Fields("Phone").Value = txtPhone.Text 
    .Fields("Notes").Value = txtNotes.Text 

    'Update the table  
    .Update 

    'Close it  
    .Close 
  End With 

End Function 

Function GetFromDatabase(sName As String) 
  'Open the Contact table  
  Set RS = DB.OpenRecordset("SELECT * FROM Contact WHERE Name _
           = '" & sName & "'", dbOpenDynaset) 
  'Put the data into the text boxes 
  'Use the "" & RS! so we don't get an error 
  'if one of the fields is blank  
  txtName = "" & RS!Name 
  txtPhone = "" & RS!Phone 
  txtNotes = "" & RS!Notes 
  'Close the table  
  RS.Close 
End Function 

Function AddToDatabase(sName As String, sPhone As String, sNotes As String) 

  'Open the Contact table  
  Set RS = DB.OpenRecordset("SELECT * FROM Contact", dbOpenDynaset) 

  With RS 
    'Set it to Add mode  
    .AddNew 

    'Enter the field values 
    .Fields("Name").Value = sName 
    .Fields("Phone").Value = sPhone 
    .Fields("Notes").Value = sNotes 

    'Update it  
    .Update 

    'Close it  
    .Close 

  End With 

End Function 

Function DeleteFromDatabase(sName As String) 
  'Open the Contact table  
  Set RS = DB.OpenRecordset("SELECT * FROM Contact WHERE Name _
            = '" & sName & "'", dbOpenDynaset) 

  With RS 
    'Set it to delete mode  
    .Delete 

    'Close it  
    .Close 

  End With 

End Function 
 
Private Sub cmdAdd_Click() 

  'Declare variables  
  Dim sName As String 
  Dim sPhone As String 
  Dim sNotes As String 

  'Ask for the data  
  sName = InputBox$("Please enter your name:", "Database") 
  sPhone = InputBox$("Please enter your phone number:", "Database") 
  sNotes = txtNotes 

  'Call the function  
  Call AddToDatabase(sName, sPhone, sNotes) 

End Sub 
 
Private Sub cmdDelete_Click() 
  'Declare the variable  
  Dim sName As String 

  'Ask for the data  
  sName = InputBox$("Please enter your name:", "Database") 

  'Call the function  
  Call DeleteFromDatabase(sName) 

End Sub 

Private Sub cmdGet_Click() 
  'Declare the variable  
  Dim sName As String 

  'Ask for the data  
  sName = InputBox$("Please enter your name:", "Database") 

  'Call the function  
  Call GetFromDatabase(sName) 
End Sub 
 
Private Sub cmdRecord_Click() 
  'Call the function  
  Call EditRecord(txtName) 
End Sub 
 
Private Sub Form_Load() 
  'Use the OpenDatabase function to open our database  
  Set DB = OpenDatabase(App.Path & "\demo.mdb") 
End Sub 
 
Private Sub Form_Unload(Cancel As Integer) 
  'Set the variables to Nothing  
  Set RS = Nothing 
  Set DB = Nothing 
End Sub  

a href="/net/vb/article.php/1538381">Download the project (All the code)