Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Access database engine object library

    Im following something on youtube, just trying a few different things.

    Im using this code:

    Code:
    Option Explicit
    Private Sub LoadArray()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("JobT")
    
    With rs
    Debug.Print .RecordCount
    .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    End Sub
    I don't have the DAO reference enabled I have the Access database engine object library. I get the following error:

    Compile error: sub or function not defined.

    I have left the code "as is" because the multiple changes I made didn't make a difference. Hopefully someone could show me how to properly reference.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't need DAO. Variants work fine.

    Dim db, rst
    set db = currentdb

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I don't know if you meant for it to be like this but still im getting the same error:

    Code:
    Option Explicit
    Private Sub LoadArray()
    Dim db As Variant
    Dim rst As Variant
    Set db = CurrentDb
    Set rst = db.OpenRecordset("JobT")
    With rs
    Debug.Print .RecordCount
    .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    End Sub

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you don't appear to have 'Option Compare Database' at the top of the module, although I'm not sure if that makes a difference

    you should have 3 references as a minimum

    Visual Basic for Applications
    Microsoft Access xx.x Object Library - where xx.x is the version you are using (think it is 15.0 for 2013)
    OLE Automation

    When you get the error - what line is highlighted?

    And just to confirm, the code is in Access, not excel?

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Thanks for the reply,

    Yes this is in access. I do have those three references along with another four. (can provide details of these if its relevant).

    and the error is when I'm in the immediate window I type loadarray and press enter to see the record count. The error then appears but no row is highlighted.

    I have just changed option explicit to 'Option Compare Database' ( not sure if I should have both).
    Still no joy.

    Ill be back on this on Monday most likely now as I am out of office tomorrow. Still appreciate the input always.

    Thanks.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I used your routine with one of my tables.
    You are using rs and rst in different places. I made them all rst

    This works for me
    Code:
    Private Sub LoadArray()
    Dim db As Variant
    Dim rst As Variant
    Set db = CurrentDb
    Set rst = db.OpenRecordset("animal")
    '("JobT")
    With rst
    Debug.Print .RecordCount
    .Close
    End With
    Set rst = Nothing
    Set db = Nothing
    End Sub
    I did have to retype the entire line
    Code:
    Set rst = db.OpenRecordset("animal")
    Access/vba colored the line red indicating a syntax issue -retyping removed it and processed to give a correct count???

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02...

    I tried executing the code from Post #1, and it ran with no errors. I deleted all references that I was able to and was left with
    "Visual Basic for Applications" and
    "Microsoft Access 14.0 Object Library" (A2010).
    Executed the query... or tried to. It choked on the DAO.Database line. Added the reference to "Microsoft DAO 3.6 Object Library".
    Code executed with no errors.

    Unchecked the "Microsoft DAO 3.6 Object Library" reference and
    added "Microsoft Office 14.0 Access database engine Object Library". Executed the code with no errors.

    So it looks like you need to have a reference set to either
    "Microsoft DAO 3.6 Object Library" or
    "Microsoft Office 14.0 Access database engine Object Library"
    to be able to use DAO. BUT, you cannot have both references set - doing so results in a "Name conflict" error.

    Haven't checked to see what the differences between the two library's are.......

  8. #8
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I was just testing this in a fresh database as nothing would work. I found on stack overflow someone changed private sub to public and it worked. I can only guess as to why its this way (or do some reading).

    either way thanks for all the input. Even without the answer it does help to know is SHOULD be working.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-09-2016, 01:22 PM
  2. Replies: 4
    Last Post: 06-02-2016, 09:58 AM
  3. Replies: 5
    Last Post: 09-09-2014, 09:36 AM
  4. Replies: 1
    Last Post: 04-24-2014, 03:49 AM
  5. Replies: 1
    Last Post: 09-03-2011, 07:01 PM

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