Results 1 to 7 of 7
  1. #1
    DittoBird's Avatar
    DittoBird is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2015
    Location
    Newfoundland & Labrador, Canada
    Posts
    59

    Is This Basic VBA Code Using DAO From A2003/2007 Still Valid in A2016 and Other Versions?

    Hi everyone,



    I am back using MS Access after 5+ years away from it (well, I did use it at home for some hobby related things). I used to use mainly A2003 (and 97 and 2), some A2007. I'm now in a position using A2016. Is DAO something that is now discouraged? I know that it is no longer a separate reference like 3.51 and 3.6 and seems to be included in the Microsoft Access 16.0 Object library or the Microsoft 16.0 Access database engine Object library.

    Here's a simple, basic sub that runs through the results of a temporary querydef the way I would normally write it. The Jet/Ace SQL has no where clause, I'm just testing this method. Is there a better way to enumerate/row process a recordset or is this approach still acceptable?

    Edit - terribly sorry, but the forum doesn't seem to accept my indents.

    ------ Start Code ------

    Option Compare Database
    Option Explicit


    Public Sub sTest()


    On Error GoTo Err_Proc


    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As Recordset

    Set db = Access.CurrentDb
    Set qdf = db.CreateQueryDef("", "select * from tblTest")

    With qdf
    Set rs = .OpenRecordset(dbOpenSnapshot)
    With rs
    If .EOF Or .BOF Then
    MsgBox "No records!", vbExclamation
    Else
    .MoveFirst
    Do While .EOF = False
    MsgBox .Fields!TestEntity
    .MoveNext
    Loop
    End If
    .Close
    End With
    .Close
    End With

    Exit_Proc:
    On Error Resume Next
    rs.Close
    qdf.Close
    db.Close
    On Error GoTo 0

    Set qdf = Nothing
    Set db = Nothing

    Exit Sub
    Err_Proc:
    Select Case Err.Number
    Case Else
    MsgBox "Access Error " & Err.Number & " " & Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    Resume Exit_Proc
    End Select
    End Sub


    ------ End Code ------

    Thank you in advance,
    --
    Tim

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    there is NO need to use code for DAO nor ADO. its all contained in access.
    nor is there a reason to cycle thru records 1 at a time using code.

    all this can be done, faster, with a query. no code needed.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Is DAO something that is now discouraged?
    dao is the default for access, you can use ado if you wish - it was the access standard back around 2000 but reverted to dao a year or so later. Note if you use ado recordsets to populate forms you will need to write your own routines for sorting and filtering since the dao method that forms use is different to the method used in ado.

    terribly sorry, but the forum doesn't seem to accept my indents.
    to preserve indents having pasted your code, highlight it and click the code button (#)

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just to confirm previous comments.
    DAO is still the main method for working with recordsets. ADO does have its uses but is more work to code.
    However, in this case I see no reason to loop through a recordset either

    Just run a query: SELECT * FROM tblTest or SELECT TestEntity FROM TblTest adding WHERE clauses as needed
    It will indeed be much faster
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    DittoBird's Avatar
    DittoBird is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2015
    Location
    Newfoundland & Labrador, Canada
    Posts
    59
    Hi ranman, thanks so much for your response. I totally get that; set processing wins over row processing, every time. I just wanted to know if the above procedure is still valid from a VBA point of view.

    All the best,
    --
    Tim

  6. #6
    DittoBird's Avatar
    DittoBird is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2015
    Location
    Newfoundland & Labrador, Canada
    Posts
    59
    Thank you, Colin. Yes, in this simple case, using a recordset is pointless. I needed to confirm about DAO.

    Thanks so much,
    --
    Tim

  7. #7
    DittoBird's Avatar
    DittoBird is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2015
    Location
    Newfoundland & Labrador, Canada
    Posts
    59
    Perfect, Ajax, thanks. And I very much appreciate the tip on preserving code... let me see:

    Code:
    Exit Sub
    Err_Proc:
        Select Case Err.Number
            Case Else
                MsgBox "Access Error " & Err.Number & " " & Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
                Resume Exit_Proc
        End Select
    Faboo!

    I remember being very excited about ADO before A2000 came out. MS had been promising DSN-less connections to Oracle. My approach for the 20 years I worked with Oracle in a maintenance environment was to create pass through queries (via an ODBC DSN) via VBA based on user choices. Possibly due to the way the vendor defined their table columns, I found native Oracle SQL worked faster than via ODBC linked tables. In this new job we are considering maintenance management systems and I'm hoping, whatever we agree to, that Oracle is the engine. Besides, Oracle pronounces "SQL" correctly! LOL Just joking.

    All the best,
    --
    Tim

Please reply to this thread with any new information or opinions.

Similar Threads

  1. MS Access Versions Pre-2007
    By f15e in forum Access
    Replies: 6
    Last Post: 12-09-2016, 12:10 AM
  2. Replies: 4
    Last Post: 09-06-2011, 05:11 AM
  3. Replies: 14
    Last Post: 05-27-2011, 02:35 PM
  4. Replies: 10
    Last Post: 05-19-2010, 04:24 PM
  5. Office versions with Access 2007
    By Coolpapabell in forum Access
    Replies: 0
    Last Post: 09-29-2009, 09:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums