DAO vs ADO
The battle of the Libraries
This VB5/VB6 project was put together to demonstrate the differences between DAO and ADO, in terms of the code used, and the performance of each method. If you use Access 97 databases, then you will probably be best using DAO, as the methods used with ADO are not always compatible with this format.
The completed project can be downloaded from Here (VB6 SP5) and Here (VB5 SP3) and is updated regularly, as I find new items, or correct errors. The code for each part of the project is published on these pages. Those of you using VB5 should be aware that the additional controls used may not be compatible with VB5 and you will need to change them to the version you have on your machine, as running both VB5 and VB6 on the same computer automatically puts the most up-to-date version in the System directory for use by both VB5 and VB6.
References to:
Microsoft DAO v 3.6 Object Library
Microsoft ActiveX Data Objects 2.7 Library
Microsoft ADO ext 2.7 for DDL and Security
Microsoft Jet and Replication Objects 2.6 Library
Components needed:
Microsoft Common Dialog Control 6.0 (SP3)
Microsoft FlexGrid Control 6.0 (SP3)
Microsoft Windows Common Controls 6.0 (TreeView)
When you start the project, you are presented with the main form frmMain:
The code for the Form_Startup is as follows:
'All DAO references are made AS Object
'This allows the use of both DAO 3.51 AND DAO 3.6 in the same project.
'Add a reference to v 3.6 only
Option Explicit
Dim strReplicaPath As String
Dim strTableName As String
Dim strIndexName As String
Dim iFieldCount As Integer
Private Declare Function apiSysDir Lib "kernel32" Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long
Private Sub Form_Load()
Me.Top = (Screen.Height - Me.Height) \ 2
Me.Left = (Screen.Width - Me.Width) \ 2
End Sub
The code for the timer class (clsTimer) used to time all the operations is:
Option
Explicit
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private StartTime As Long
Private Sub Class_Initialize()
StartTime = timeGetTime()
End Sub
Public Property Get Interval() As Long
'return time interval in milliseconds
Interval = timeGetTime() - StartTime
End Property
Public Sub Reset()
' Reset starting time.
StartTime = timeGetTime()
End Sub
The function to locate the System Directory is
Public Function SysDir() As String
'function using api to get \System directory
Dim Bufstr As String
Bufstr = Space$(255)
If apiSysDir(Bufstr, Len(Bufstr)) > 0 Then
SysDir = Left$(Bufstr, InStr(Bufstr, Chr$(0)) - 1)
If Right$(SysDir, 1) <> "\" Then
SysDir = SysDir + "\"
End If
Else
SysDir = ""
End If
End Function
As I have used graphical style checkboxes instead of command buttons, some code is required to make them behave the same as command buttons and also allow the MouseMove event to colour the buttons
'Function called on Click event of each checkbox, so simulate normal button operation
Public Function ChangeCmdProperties(cmdName As Control) As Boolean
If cmdName.Value = vbChecked Then
cmdName.Value = vbUnchecked
ReleaseCapture
cmdName.ForeColor = vbButtonText 'reset to normal colour
cmdName.BackColor = vbButtonFace
ChangeCmdProperties = True
Else
ChangeCmdProperties = False
End If
End Function
'Sub called on each checkbox MouseMove event to change button colours
Public Sub MouseMoveSub(cmdName As Control, X As Single, Y As Single)
If (X < 0) Or (Y < 0) Or (X > cmdName.Width) Or (Y > cmdName.Height) Then
ReleaseCapture
cmdName.ForeColor = vbButtonText 'reset to normal colour
cmdName.BackColor = vbButtonFace
ElseIf GetCapture() <> cmdName.hwnd Then
SetCapture cmdName.hwnd
If InStr(1, cmdName.Name, "DAO", vbTextCompare) > 0 Then
cmdName.ForeColor = vbBlue
cmdName.BackColor = vbBlack
Else
cmdName.ForeColor = vbGreen
cmdName.BackColor = vbBlack
End If
End If
End Sub
There is a module (modDAOvsADO) containing Public variables for use with the .Seek method
Option Explicit
Public pcnnSrc As New ADODB.Connection
Public prstSrc As New ADODB.Recordset
Public pcatSrc As New ADOX.Catalog
Public ptblSrc As New ADOX.Table
Public pcolSrc As New ADOX.Column
Public pixSrc As New ADOX.Index
Public ppropSrc As Property
Public pdb As DAO.Database
Public prs As DAO.Recordset
Public pDAOtblSrc As DAO.TableDef
Public pDAOixSrc As DAO.Index
Public pDAOfdSrc As DAO.Field
In the VB5 version there is an additional module, which contains the code for substituting the VB6 string functions which are not available in VB5. This was copied from the MS website
The code for each of the buttons is presented on a different page: You can either select a button to look at the code for that part of the project, or follow the DAO tutorial or ADO tutorial which takes you through each stage in turn, starting with Creating a database from scratch, compacting it, copying it, change it to a design master, replicate, synchronise and view its schema.
My conclusions from the findings of this project regarding the use of DAO or ADO