©1997 by Charles Carroll. All Rights reserved.

A step-by-step Visual Basic tutorial (revised 04/08/98 05:19 PM)

A new Visual Basic programmer who needs to write programs that communicate with databases will need to use a Data Control or DAO (Data Access Objects). This program demonstrates not only how to use these two tools, but how to write your programs that use these tools so they will run fast and provide the user with some feedback as to their progress.

Picture of Dbspeed Form

Create this form and then we will go through the code behind each button, and what techniques the various buttons emphasize. Then by the time you have finished this small application you should have an excellent sense for what DAO is and how it works.

Step 1: Connecting to the Data

Before filling in any of the command buttons, we should set up a program that connects to a database with the database control and allows us to switch between a grid view and a record-at-a-time view.

private sub CmdRefresh_Click
1Data1.Refresh
End Sub
private sub Check1_click
1If Check1.Caption = “Display Grid” Then
2     Check1.Caption = “Display 1 Record only”
3     Label1.visible=false     
4     Label2.visible=false   
5     Label3.visible=false
6     Text1.visible=false
7     Text2.visible=false
8     Text3.visible=false
9     DBGrid1.Visible = True
10     DBGrid.Align = 1
11Else    
12     Check1.Caption = “Display Grid”   
13     DBGrid1.Visible = False
14     Label1.visible=true
15     Label2.visible=true
16     Label3.visible=true
17     Text1.visible=true
18     Text2.visible=true
19     Text3.visible=true  
20End If      
End Sub

Step 2: Using DAO to add records

For starters, we are going to make a button that adds 500 records to the database using a standard Database programming technique called DAO (Data Access Objects). This button is a textbook example in that it works, but isn’t quite fast enough for real use. In many ways, this button has flaws, but it works and will give us a starting point for writing a faster replacement.

private sub CmdAddBad_click
1cmdAddBad.Enabled = False
2Dim Dog As Database, cat As Recordset
3Set Dog = DBEngine.Workspaces(0).OpenDatabase _     
3(“C:\program files\microsoft visual basic\biblio.mdb”)     
4Set cat = Dog.OpenRecordset(“Authors”)
5‘ BeginTrans
6For x = 1 To 500   
7    cat.AddNew    
8   cat(“Author”) = “Test Author”
9   cat(“Year Born”) = 1901    
10   cat.Update    
11Next x
12‘ CommitTrans
13cat.Close
14Dog.Close     
15cmdAddBad.Enabled = True      
End Sub

Step 3: A Expanded, Speedier version using DAO to add records

Now we will use our cmdAddStat button to code a much improved version. Improvements include:

  • Speedier because of BeginTrans and CommitTrans.
  • A status bar gives feedback at all stages of the operation.
  • A report of total time is provided when it finishes.
private sub CmdAddStat_click
1cmdAddStat.Enabled = False
2started = Timer()
3Dim Dog As Database, cat As Recordset     
4Set Dog = DBEngine.Workspaces(0).OpenDatabase _     
4(“C:\program files\microsoft visual basic\biblio.mdb”, True)
5Set cat = Dog.OpenRecordset(“Authors”)
6StatBar = “Authors is opened”   
7‘ BeginTrans     
8For x = 1 To 3000
9     StatBar = “Adding Person #” + Str$(x) + ” of 3000″    
10     StatBar.Refresh    
11     cat.AddNew
12     cat(“Author”) = “Test Author”
13     cat(“Year Born”) = 1901
14     cat.Update     
15Next x      
16‘ CommitTrans
17cat.Close   
18Dog.Close        
19elapsed = Timer() – started      
20elapsedmins = Int(elapsed / 60)   
21elapsedSecs = elapsed Mod 60        
22elapsedwords$ = elapsedmins & ” minutes, and ” & _        
22   elapsedSecs & ” seconds”
23StatBar = “Added ” & Str$(x – 1) & ” authors in ” & elapsedwords$    
24cmdAddStat.Enabled = True     
End Sub

Step 4: Now remove the “test” records

Now we will use our cmdDeleteBad button to code a program that will remove all the records we we placed into the database. We will start with a version that works but is too slow. Once we get it working we will discuss a totally different approach to the same task. This button will teach some useful DAO techniques, including:

  • using the .EOF property and .movenext method in conjunction with a DO UNTIL loop to process every record in a database
  • the .delete method in a recordset object to delete a record
private sub CmdDeleteBad_click
1CmdDeleteBad.Enabled = False
2started = Timer()
3Dim Dog As Database, cat As Recordset     
4Set Dog = DBEngine.Workspaces(0).OpenDatabase _     
4(“C:\program files\microsoft visual basic\biblio.mdb”)
5Set cat = Dog.OpenRecordset(“Authors”)
6StatBar = “Authors is opened”   
7delcount = 0     
8loopcount = 1
9Do Until cat.EOF    
10    If cat(“Author”) = “Test Author” Then    
11        cat.Delete
12        delcount = delcount + 1
13        StatBar = “Deleting Record#” & Str$(loopcount)
14    Else     
15        StatBar = “Not Deleting Record#” & Str$(loopcount)      
16    End If
17    StatBar.Refresh   
18    cat.MoveNext        
19    loopcount = loopcount + 1      
20Loop   
21cat.Close
22Dog.Close        
23elapsed = Timer() – started
24elapsedmins = Int(elapsed / 60)    
25elapsedSecs = elapsed Mod 60     
26elapsedwords$ = elapsedmins & ” minutes, and ” & _        
26   elapsedSecs & ” seconds”        
27StatBar = “Done. Deleted ” & Str$(delcount) & _    
27   ” Authors of ” & Str$(loopcount) & ” Total in ” & elapsedwords$         
28cmdDeleteBad.Enabled = True          
End Sub

Step 5: Now a different way to remove the “test” records

Now we will use our cmdDeleteBest button to code a program that will remove all the records we we placed into the database quickly and efficiently using SQL. This approaches has several advantages over the approach we used on the previous button, for instance:

  • Speed, speed, speed!!!
  • Ability to delete records where multi-table joins determine the deletion rules
  • avoids loops, thus avoiding many complex programming issues
  • less code-intensive and easier to read at a glance
private sub CmdDeleteBest_click
1CmdDeleteBest.Enabled = False
2started = Timer()
3Dim Dog As Database, cat As Recordset     
4Set Dog = DBEngine.Workspaces(0).OpenDatabase     
4(“C:\program files\microsoft visual basic\biblio.mdb”)
5Dog.Execute (“DELETE from Authors WHERE [Author]=’Test Author'”)
6elapsed = Timer() – started   
7StatBar = “Done. Deleted ” & dog.recordsaffected & _     
7   ” Authors in ” & str$(Int(elapsed/60)) & ” minutes, and ” & _
7   str$(elapsed mod 60) & ” seconds”    
8cmdDeleteBest.Enabled = True    
End Sub

Go to home for more great resources