Results 1 to 8 of 8
  1. #1
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41

    How to Replace DAO with ???

    I am auto selecting records based on this command button code. It depends on the fExecuteQuery (DAO code) to work properly. I need to replace the DAO classes / references with one that is used by Access 2019 without the DAO 3.6 DLL installed. I do not have clue where to start. Can someone please send me links that will educate me on how to switch out the DAO code.



    Code:
    Dim strSQL As String
    
    
    strSQL = "UPDATE Students SET Students.UpdateWorkshop = -1 "
    strSQL = strSQL & "WHERE Students.[ID] IN "
    strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
    'strSQL = strSQL & "WHERE (nz(S.UpdateWorkshop, 0) = 0) AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
    strSQL = strSQL & "WHERE ((S.Workshop) is null) AND (S.Flightpath)= 'LCB' " ' can't find List of BAD Majorcodes
    strSQL = strSQL & "AND (S.Session = " & Val([Forms]![WorkshopsSession]![SessionCombo] & "") & ")"
    strSQL = strSQL & "ORDER BY (S.major));"
    
    
      fExecuteQuery strSQL, dbFailOnError
       
    ' reset textbox to blank
    Me.Textbox1 = Null
    DoCmd.Requery
    Code:
    unction fExecuteQuery(strQuery As String, _
        Optional intOptions As DAO.RecordsetOptionEnum = dbFailOnError, _
        Optional blnReturnAuto As Boolean = False, _
        Optional pdb As DAO.Database) As Long
    
    
    Dim db As Database
    Dim prm As DAO.Parameter
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
    
    
    If Not pdb Is Nothing Then
       Set db = pdb
    Else
       Set db = CurrentDb
    End If
    
    
    Select Case Left(strQuery, 7)
       Case "INSERT ", "UPDATE ", "DELETE "
          Set qdf = db.CreateQueryDef("", strQuery)
       Case Else
          Set qdf = db.QueryDefs(strQuery)
    End Select
    
    
    For Each prm In qdf.Parameters
       prm.Value = Eval(prm.Name)
    Next
    
    
    qdf.Execute intOptions
    
    
    If blnReturnAuto Then
       Set rst = db.OpenRecordset("SELECT @@Identity")
       fExecuteQuery = rst(0)
       rst.Close
    End If
    
    
    Set prm = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    
    
    End Function

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would you not have DAO available? It is Access default.

    Since it's just a single action SQL and not even in a loop, don't declare and set db and QueryDef objects.
    Why even bother with this Function? Instead of calling function, just execute the SQL.

    CurrentDb.Execute strSQL, dbFailOnError

    Ooops, I guess that is still DAO.

    Alternative may be to open ADO recordset object and use .Edit and .Update methods in a loop.

    Or open form to desired record(s) and code physically moves to each record and modifies data.

    Or build a query object that performs update and code opens that object.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm with June7. I've got 2019 on a VM and DAO works fine on it. What references are checked in the VBA editor, Tools/References? If memory serves,

    Microsoft Access 16.0 Object Library

    is the one with DAO in it, and is installed by default.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  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
    Actually the old DAO reference has been superseded by Microsoft Office 16.0 Access database engine Object Library which is ACEDAO.dll
    However as Paul said it is installed as one of the 4 default references in all new databases.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	29 
Size:	14.6 KB 
ID:	41981
    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
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41

    DAO issue

    I am aware of this .... thank you I am curious as to why if this supersed DAO 3.6 why did not the function run under it?

    Quote Originally Posted by isladogs View Post
    Actually the old DAO reference has been superseded by Microsoft Office 16.0 Access database engine Object Library which is ACEDAO.dll
    However as Paul said it is installed as one of the 4 default references in all new databases.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	29 
Size:	14.6 KB 
ID:	41981

  6. #6
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41
    Thanks to all that has responded to this request. I really appreciate it.

    I am developing a DB (in Access 2016 in Office 365) for an Access 2019 user. The Access 2019 user is getting the error message "Error 3075: Function not available in expressions" for Right([Workshops]![Name],1. This expression was removed several iterations back.

    I could not recreate the error. Using remote access, I observed the problem first hand. I do not think this is a VBA References "compatibility?" problem, but I thought to try installing DAO 3.6 as a long shot.

    As she is not an admin of her computer, the issue has been turned over to her tech support. As ACEDAO is installed, they did not see a need for DAO 3.6. I agree this is not a VBA Reference issue. Funny thing, they can recreate the "Function not available in expressions" error on test computers. FYI: The DAO 3.6 DLL fails to install for them.

    FYI: To eliminate one variable (which I do not think has anything to do with this), I upgraded to Office 2019. Of course, I had to add the DAO 3.6 DLL to this new version of Access to get the function to run.

    Again, Thanks to all of you.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The "function not available" error is often caused by a reference problem:

    http://accessmvp.com/DJSteele/Access...nceErrors.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41
    I am closing this thread because the DAO fails to install. I am going to explore rewriting the function to work in that environment. thank you to everyone..

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

Similar Threads

  1. Replies: 13
    Last Post: 03-22-2019, 04:09 PM
  2. replace #Num! with 0
    By chriswrcg in forum Queries
    Replies: 3
    Last Post: 10-31-2018, 12:41 PM
  3. Replace _ when #_#
    By wellsw in forum Queries
    Replies: 5
    Last Post: 11-22-2013, 06:32 PM
  4. replace . with /
    By msasan1367 in forum Access
    Replies: 1
    Last Post: 08-05-2013, 06:21 AM
  5. Replies: 3
    Last Post: 06-07-2012, 07:05 AM

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