Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    References Collection via VBA (97 and XP)

    Hi all,

    It appears to me that the references collection for an access database is specific to the mdb/mde etc, but the references collection is only available from the application object eg
    For Each refItem In References

    It seems that is not possible to access the references collection for a database that has not been opened. Is this correct?

    I'd like to be able to work through a list of databases checking for broken references and removing references that are added by default when converting to XP from 97 that we consider unnecessary.

    At the moment it looks like I may have to open each database in a seperate instance of access to be able to access the references collection for that mdb. Is there an easier way?

    Thanks

    Stewart

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: References Collection via VBA (97 and XP)

    As far as I know, there is no way of inspecting the references set for a database without opening it in Access. As you mention, References belongs to the Access application object, not to (for example) the DAO Database object.

    Note: unless you have taken extreme care to prefix all objects with the library they are defined in, it is impossible to handle missing references in code. Usually, any missing reference causes standard VBA functions such as Left or Date to fail...

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: References Collection via VBA (97 and XP)

    As Hans has said, there is no easier way. It is possible to test the references in VBA code, but it gets complicated and it must be the very first code run before XP has a chance to perform its magic of not recognizing broken references at runtime. The code to test and fix references must be in its own separate module, in the database fully referenced (no shortcuts like Application.Whatever), and you need to call it from an autoexec macro. Of course, by that time, you might as well have opened the database and fixed it manually. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References Collection via VBA (97 and XP)

    Just as a follow up, I have a solution for what I am trying to do. Thanks to Hans and Charlotte for their input.
    I'm looping through a list of databases and fixing specific references that I know and / or care about.
    The code uses the approach shown as follows ( I just pass a database parameter and also remove a couple of other references if they exist for real).


    <pre>Function appAccessRefTest()
    Dim appAccess As Access.Application
    Dim db As DAO.Database
    Dim refItem As Reference

    Set appAccess = CreateObject("Access.Application")

    appAccess.OpenCurrentDatabase ("h:Equipment.mdb")
    For Each refItem In appAccess.References
    Debug.Print refItem.Name & " - " & refItem.FullPath
    If refItem.Name = "DAO" Then
    If refItem.Guid = "{00025E04-0000-0000-C000-000000000046}" Then
    'this is the DAO25/35 compatibility layer from 97
    ' remove it
    appAccess.References.Remove refItem
    ' replace with DAO 3.6
    appAccess.References.AddFromFile ( _
    "C:Program FilesCommon FilesMicrosoft SharedDAOdao360.dll")
    End If
    End If
    Next refItem

    appAccess.CloseCurrentDatabase
    appAccess.Quit
    Set appAccess = Nothing
    End Function</pre>




    Cheers

    Stewart

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: References Collection via VBA (97 and XP)

    I'm glad you found something that works for you. One caveat, though, is that your database may be left in an uncompiled state after that and that will affect its performance..
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References Collection via VBA (97 and XP)

    Charlotte said "One caveat, though, is that your database may be left in an uncompiled state after that and that will affect its performance"

    I had not considered that Charlotte, and I guess the next question is then.....

    can I programmatically compile the database that I opened?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: References Collection via VBA (97 and XP)

    Try

    RunCommand acCmdCompileAndSaveAllModules

    or

    RunCommand acCmdCompileAllModules

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References Collection via VBA (97 and XP)

    Hans,

    I've used

    <pre> With appAccess
    Debug.Print .IsCompiled
    .RunCommand acCmdCompileAndSaveAllModules
    Debug.Print .IsCompiled
    End With
    </pre>


    and was surprised that the RunCommand method is applicable to the application object. I checked the help and there it was, looks like I need to have a better look at the object model.

    Just as an aside, I got curious at thsi point and started looking for the differences between a DoCMD.RunCommand and an Application.RunCommand. The help provided the following...
    "To run the RunCommand action in Visual Basic, use the RunCommand method of the Application object. (This is equivalent to the RunCommand method of the DoCmd object.)"

    It was interesting that when I tested with a module that contained a syntax error it didn't generate an error, it just left the database in an uncompiled state, not that it matters as I can just flag for manual investigation if the IsCompiled property remains false.

    Thanks for the tip.

    Cheers

    Stewart

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: References Collection via VBA (97 and XP)

    RunCommand is the successor of the DoMenuItem method of the DoCmd object. In Access 95 and before, the way to invoke menu items in code was through DoCmd.DoMenuItem. Although DoMenuItem is still available in Access 97 and later (various wizards still generate code using DoMenuItem), the recommended method is now RunCommand. It is a method of the Application object, but for compatibility reasons, it is also implemented as a method of DoCmd. Since you don't need to specify Application, the following are equivalent:

    RunCommand acCmdCopy
    Application.RunCommand acCmdCopy

    and you can also use

    DoCmd.RunCommand acCmdCopy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •