Questions on libraries, options, close & nothing statements using DAO

[Persist]

Questions about one brief subroutine using DAO, concerning the:


. Options statements
. Close statements
. Nothing statements
. Object libraries used

I have inserted the questions in the following code - as comments marked by ****.

This is new to me, so I’d welcome other comments about this code.

Option Compare Database
Option Explicit
‘ ******** Are these Options statements ok

Private Sub btnTestCode_Click()
On Error GoTo Err_btnTestCode_Click
 
   Dim dbsDoList As DAO.Database
   ' DAO.database user-defined type was initially not defined
   ' I fixed this with Tools > References > tick Microsoft DAO 3.6 object library
   ' Microsoft ActiveX data objects 2.1 library also ticked
‘ ***************** Should I untick ActiveX library

   Dim rsTasks As DAO.Recordset
   Dim strSQL As String

   Set dbsDoList = CurrentDb
 
   ' Open a recordset for all records from table tDoList
   ' The Fields of table tDoList include: Subject, Priority
   
   strSQL = "SELECT Subject, Priority FROM tDoList ORDER BY Priority"
   Set rsTasks = dbsDoList.OpenRecordset(strSQL, dbOpenDynaset)
 
   With rsTasks
      Do Until .EOF
      
        If ![Subject] = "xx" Then
            .Edit
            ![Priority] = 999977
            .Update
        End If
            
        .MoveNext
        
      Loop 
   End With 
 

Exit_btnTestCode_Click:

   rsTasks.Close
   dbsDoList.Close
 
   Set rsTasks = Nothing
   Set dbsDoList = Nothing

    ‘ ***** Are these 4 statements in the right place
    ‘ ***** Are these 4 statements needed.
    Exit Sub

Err_btnTestCode_Click:
    
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    ' vbCrLf = visual basic carriage return line feed
    Resume Exit_btnTestCode_Click
    
End Sub

I am running Microsoft Visual Basic for Applications 7.0” Copyright 2010

[June7]

Those two option statements, at a minimum, should be in every code module.

ActiveX library is needed if you are declaring objects from that library. Uncheck then Compile the code and see what happens.

It is considered good practice when objects are set and opened, to explicitly close and destroy when no longer needed. VBA should do this automatically when the procedure ends but explicitly doing it is assurance.

[Persist]

I did un-tick the Microsoft ActiveX data objects 2.1 library.
I did not get compile errors - and my little database seeems to be running fine, so I will leave it un-ticked
Thanks

[June7]

You will want the Microsoft ActiveX Data Objects x.x Library if you want to use ADODB objects in VBA.

[Persist]

I searched for pros and cons of DAO and ADODB.
Somewhere I saw that Microsoft started with DAO, headed towards ADODB and now have moved back towards DAO.
This is probably a great simplification, but it led me to try using DAO with no ADOBD. I may look at ADODB in the future.
Thanks for your contributions.

[ssanfu]

The rule is
If you create it, destroy it
If you open it, close it.



You created (declared) "dbsDoList ", but you did not open it. Therefore

bsDoList.Close             <-- do not need/ should not have
Set dbsDoList = Nothing.   <-- NEED

You created (declared) "rsTasks" AND you opened it. Therefore you need both

rsTasks.Close           <-- NEED
Set rsTasks = Nothing   <-- NEED

BTW, creating is using lines with the SET command

Set dbsDoList = CurrentDb                                     <-- Create
Set rsTasks = dbsDoList.OpenRecordset(strSQL, dbOpenDynaset)  <-- Create AND Open

[Persist]

I am wondering about the order of closing and setting to nothing. I have:
rsTasks.Close
Set rsTasks = Nothing

This seems to be closing then destroying it - which sounds right.

More excellent feedback - just what a novice DAO person needs.

Thanks Steve.

[ssanfu]

Yep. That is the correct order: Close then destroy.

[Persist]

Here is the full code with the suggested changes and comments to explain.
I've added about 5 more lines of code to make this subroutine the heart of a simple "Do List database"
Only one table, tTasks with fields: Task, Priority, ActionDate, TaskID
In this subroutine:
. Tasks get sorted by priority and then re-allocated priorities 100, 110, 120 etc
. Tasks = "xx" get priority 999999
It is a short, useful example of DAO code
Thanks for your help.

Private Sub btnresetPriorities_Click()
On Error GoTo Err_btnresetPriorities_Click
 
   Dim dbsDoList As DAO.Database
   ' DAO.database user-defined type not defined
   ' Fix with Tools > References > Tick Microsoft DAO 3.6 object library
   ' I un-ticked Microsoft ActiveX Data Objects 2.1 library
   ' Access 2010 32 bit

   Dim rsTasks As DAO.Recordset
   Dim strSQL As String
 
   Dim NewPriority As Integer
   NewPriority = 100 ' Set first priority
 
   Set dbsDoList = CurrentDb
 
   ' Open a recordset for all records from tTasks
   ' tTasks Fields: Task, Priority, ActionDate
   
   strSQL = "SELECT Task, Priority FROM tTasks ORDER BY Priority"
   Set rsTasks = dbsDoList.OpenRecordset(strSQL, dbOpenDynaset)
 
   With rsTasks
      Do Until .EOF
      
        If ![Task] = "xx" Then
            .Edit
            ![Priority] = 999999
            .Update
        Else
            .Edit
            ![Priority] = NewPriority
            .Update
            NewPriority = NewPriority + 10
        End If
            
        .MoveNext
        
      Loop ' Do Until Loop
   End With ' rsTasks
     
Exit_btnresetPriorities_Click:

   rsTasks.Close
   Set rsTasks = Nothing
   ' Set rsTasks = dbsDoList.OpenRecordset(strSQL, dbOpenDynaset)
   ' This line creates / declares rsTasks and opens it
   ' So at end, (1) close it and then (2) set it to nothing = destroy it
   
   ' Set dbsDoList = CurrentDb
   ' This line creates dbsDoList, so at end I set it to nothing = destroy it
   ' I did not open it, so I do not close it
   ' dbsDoList.Close
   Set dbsDoList = Nothing
   
   Exit Sub

Err_btnresetPriorities_Click:
    
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    ' vbCrLf = visual basic carriage return line feed
    Resume Exit_btnresetPriorities_Click
    
End Sub