Hi,
I'm developing a database (.mdb) file using Access 2007 in Vista. This database includes a reference to "Microsoft Excel 12.0 Object Library".
I need this database also to work when it is opened using Access 2003 in XP. In order for this to work I need to change the reference to "Microsoft Excel 11.0 Object Library". Currently I do this manually every time I migrate the database.
I'm attempting to do this via code. I have found some code via searches but have run into some problems.
One piece of code I found iterates through the collection of references. It determines if the reference is broken and then removes the reference before re-adding the reference:
However, in my situation, since the reference is "Missing" (as per viewing Tools-->References) the code is not working.
When it iterates over the item in the References collection that refers to the Excel 12 library object, the following "values" can be seen during debuging (by placing my cursor over them):
loRef.FullPath = <Method 'FullPath' of pbject 'Reference' failed>
loRef.Name = <Error in loading DLL>
Is there anyway that I can remove this "Missing" reference?
The reason I need to do so (or at least believe I need to do so) is that when I just run the following line of code:
I get the following error:
Run-time error '32813'
Name conflicts with existing module, project, or object library
Thanks,
- Bruce
I'm developing a database (.mdb) file using Access 2007 in Vista. This database includes a reference to "Microsoft Excel 12.0 Object Library".
I need this database also to work when it is opened using Access 2003 in XP. In order for this to work I need to change the reference to "Microsoft Excel 11.0 Object Library". Currently I do this manually every time I migrate the database.
I'm attempting to do this via code. I have found some code via searches but have run into some problems.
One piece of code I found iterates through the collection of references. It determines if the reference is broken and then removes the reference before re-adding the reference:
Code:
Dim loRef As Access.Reference
Dim intCount As Integer
Dim intX As Integer
Dim blnBroke As Boolean
Dim strPath As String
On Error Resume Next
'Count the number of references in the database
intCount = Access.References.Count
'Loop through each reference in the database
'and determine if the reference is broken.
'If it is broken, remove the Reference and add it back.
For intX = intCount To 1 Step -1
Set loRef = Access.References(intX)
'display reference path
Debug.Print intX & ". " & loRef.Name & ": " & Access.References(intX).FullPath
With loRef
blnBroke = .IsBroken
If blnBroke = True Or Err <> 0 Then
strPath = .FullPath
With Access.References
.Remove loRef
.AddFromFile strPath
End With
End If
End With
Next
However, in my situation, since the reference is "Missing" (as per viewing Tools-->References) the code is not working.
When it iterates over the item in the References collection that refers to the Excel 12 library object, the following "values" can be seen during debuging (by placing my cursor over them):
loRef.FullPath = <Method 'FullPath' of pbject 'Reference' failed>
loRef.Name = <Error in loading DLL>
Is there anyway that I can remove this "Missing" reference?
The reason I need to do so (or at least believe I need to do so) is that when I just run the following line of code:
Code:
'PATH_EXCEL_11 is a constant with the correct path/name
Access.References.AddFromFile PATH_EXCEL_11
I get the following error:
Run-time error '32813'
Name conflicts with existing module, project, or object library
Thanks,
- Bruce