References Collection via VBA (97 and XP)

Topic

New Reply

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

Reply | Quote

Replies

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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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).

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

Cheers

Stewart

Reply | Quote

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..

Reply | Quote

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?

Reply | Quote

Try

RunCommand acCmdCompileAndSaveAllModules

or

RunCommand acCmdCompileAllModules

Reply | Quote

Hans,

I’ve used

    With appAccess
        Debug.Print .IsCompiled
        .RunCommand acCmdCompileAndSaveAllModules
        Debug.Print .IsCompiled
    End With

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hans,

I’ve used

    With appAccess
        Debug.Print .IsCompiled
        .RunCommand acCmdCompileAndSaveAllModules
        Debug.Print .IsCompiled
    End With

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

Reply | Quote

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..

Reply | Quote