Automatically Check for and Add/Correct References? 1

[kjv1611]

This isn't something that's terribly important right now, but just something I'm curious about.

I not terribly long ago upgraded from Office 2003 to Office 2007 at work. The majority of coworkers in my department all still have Office 2003.

For one of the databases I am working on, I am rebuilding it practically from scratch in Access 2007, as everyone else should be moving to 2007 at some point in the foreseeable future.

The problem comes in when I want to "demo" the current database by converting it to 2003, and asking one coworker to open it on their PC. When this happens, they can open the database, but as soon as they click a button that calls any code including any references, it just errors out.

I looked at the References on their PC, and b/c I converted to Access 2003, Access just said the references were MISSING:... and then it'd say "Microsoft Excel 12" or "Microsoft Office 12" and so on.

Is there any way to upon opening the database make sure the correct references are enabled, and if not, then enable the references?

It seems I've read about this before in these forums, but I thought I'd ask to be sure, and search around the web as I get the time.

If nothing else, I figure some interesting discussion could come out of it. At least it seems interesting to me, that is.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

A common way for deployment in heterogenous msoffice versions is to either develop with the most older version or to use late binding (ie have the strict minimum in the References)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

That's true. I had the application already built in 2003, but had some changes that I had to make for 2007 anyway. That plus the fact that everyone in the office will be using 2007 sooner or later, I thought if I were going to change it, I might as well focus on 2007, as by the time I get it "finished" for sure, at least some if not all of our department will be moved to 2007.

The 2003 copy already works fine and well in Access 2003. In my new "version", I'm making it much more user friendly, and of course changing much of the background code. And I'm including things which were not handled at all in the last version.

Frankly it was one of those deals I thought of doing for myself, and then mentioned to the others. I never expected it to really get any use, but now it's used for every instance when it can be used at all. And from that, I've gathered a little feedback on things that would be easier and such.

Well, that's somewhat off the main topic here, but hopefully it'll give a little background/reason for why I'm doing what I'm doing.

I basically don't see any real reason to design for 2003 or prior, b/c eventually everyone here WILL be in Office 2007.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

I found this source which may work. However, it's written for Excel not Access, so I'll have to see if it will run the same, or whether I need to change something. Either way, it's a start:

http://it.toolbox.com/wiki/index.php/Add_references_programmatically_with_VB_or_VBA

--

"If to err is human, then I must be some kind of human!" -Me

 

[XB9R]

I had the same problem with a 2007 access database developed by a co-worker that used some new 2007 reference. I'm still on the 2003 version.

I got around the issue by downloading & installing the Microsoft Office 2007 Viewer:

http://office.microsoft.com/search/...lang=en&hurl=E3FCA11F8565B6621F0119134154CD23

Just make sure you 'activate' the "Microsoft Office 12" reference on the 2003 machines.


Dave

 

[idbr]

You could try adding the required references by GUID rather than by name or location. The advantage of this approach is that newerr libraries retain the same GUId, so you don't have to worry about later version getting lost.

here's a sample proc:

Public Sub Add_Refs()

'-----------------------------------------------------------------------------------------
' adds required object library references.
' this is a per workbook property, but added just in case something breaks the references,
' e.g. upgrade...
'-----------------------------------------------------------------------------------------

' if these references already exist error 32813 will be thrown,
' but we don't want to terminate the process
On Error Resume Next

'------------------------------------------------------------------------------------
' add references - note add from file and GUID to account for non-standard file paths
' or files not found
'------------------------------------------------------------------------------------

' IWshRuntimeLibrary = Windows Script Host Object Model
' required to run file manipulation operations on the filesystem
Application.VBE.ActiveVBProject.References.AddFromFile "C:\WINDOWS\system32\wshom.ocx"
Application.VBE.ActiveVBProject.References.AddFromGuid "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B}", 1, 0

' Windows Common Control Set
' used to gain access to standard Windows file dialog
Application.VBE.ActiveVBProject.References.AddFromFile ("C:\Windows\System32\MSCOMCTL.ocx")
Application.VBE.ActiveVBProject.References.AddFromGuid "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}", 2, 0

' Office 12 object library
' used to gain access to standard Windows file dialog
Application.VBE.ActiveVBProject.References.AddFromFile ("C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL")
Application.VBE.ActiveVBProject.References.AddFromGuid "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}", 2, 4

' Office 11 object library
' used to gain access to standard Windows file dialog
Application.VBE.ActiveVBProject.References.AddFromFile ("C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL")
Application.VBE.ActiveVBProject.References.AddFromGuid "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}", 2, 4

'------------------------------------------------------------------
' check the error to make sure we haven't come across something odd
'------------------------------------------------------------------

If err.Number <> 32813 Then

    ' this error is thrown as a result of non-trusting access to the VBA project
    If err.Number = 1004 Then

        MsgBox "In order to complete this action, please trust access to the Visual Basic Project:" & vbCrLf & vbCrLf & _
                "Select Tools > Options from the main menu." & vbCrLf & vbCrLf & _
                "Select the ""Security"" tab." & vbCrLf & vbCrLf & _
                "Click the ""Macro Security"" button." & vbCrLf & vbCrLf & _
                "Select the 'Trusted Publishers' tab." & vbCrLf & vbCrLf & _
                "Put a tick in the box labelled ""Trust access to the Visual Basic Project"".", vbExclamation, "Aggregator"
                
    End If
    
End If

End Sub

You can get the GUIDs by enumerating the collection:

Public Sub Enumerate_Refs()

Dim ref As Reference

For Each ref In Application.VBE.ActiveVBProject.References

Debug.Print ref.Name & " " & ref.GUID
    
Next ref

End Sub

Hope that helps, Iain

 

[idbr]

Oops, forgot to mention - you'll need to add a reference to the Microsoft Visual Basic For Applications Extensibility liberary to enumerate the references collection.

 

[kjv1611]

idbr,
Thanks for the info and sample code! That looks like it would do what I'd want in order to verify it working correctly on each machine, regardless of Office version.

XB9R,
The Office 2007 compatibility pack doesn't work for Access files - it specifies the ones it does work for:

Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats

--

"If to err is human, then I must be some kind of human!" -Me

 

[XB9R]

It worked for me with Access ...I got the error

.. missing or broken reference to the file MSO.DLL version 2.4

After installing the 'Microsoft Office 12.0 Object Library' is available as a reference in Access.

... still it might not work for you ...

Dave