Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / January 2008

Tip: Looking for answers? Try searching our database.

Setting a reference to a libray in code

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Syvers - 30 Jan 2008 14:36 GMT
Hi

I am trying to reset references to Excel and Word from Access 2007 i.e. code
developed on 2007 with office 2007 but when dstributed to users the reference
is broken because they are on say office 2000.

I have read the link provided in previous posts on this subject but it
doesnt seem to be of any help to me.

What i do is look through the references to see if any are broken (i already
know the excel one is though by looking at tools > references) so

For each ref in References
    if ref.isbroken then
        'fix the reference
    end if
next

I was then going to fix the broken ones but when i get to a broken ref it
cannot access the name property and i cannot remove the ref either, but if i
just try to add the reference using AddFromPath i get an error telling me the
reference is allready in use. Well this is no use because it is missing and i
cant remove it because i cannot extract its name to work out which one is
broken!!

The messages I get when trying to set a reference object to excel (Set
missingRef = References!Excel) is error 2147319779 (8002801d) Method item of
object references failed.

That leaves me thinking ok the reference is not available so how can i
create one so i try to addfromfile (References.AddFromFile("c:\Program
Files\MicrosoftOffice\Office\Excel9.olb") and i get RunTime error 32813 -
Name conflicts with existing module, project or library.

I have managed to remove it manually by unticking the missing excel 12
reference then adding the excel9 reference in code but that is no use either,
i need to be able to remove the broken reference in code so i can replace it
(according to the help files!!!).

Can anyone help?

Thanks

Paul
Douglas J. Steele - 30 Jan 2008 15:01 GMT
When a reference is broken, you can't access most of its properties.
However, there's no reason why you can't delete it. Inside your If
statement, use the Remove method of the References collection:

 For each ref in References
   if ref.isbroken then
     References.Remove ref
   end if
 next ref

That having been said, I'd recommend strongly against trying to reset the
references such as you are. Use Late Binding instead, so that you don't need
to set a reference, and it'll work with any version of Word or Excel unless,
of course, you're using features that only exist in a particular version.
Even in that case, though, resetting the reference won't work if the
referenced version doesn't have the feature.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi
>
[quoted text clipped - 13 lines]
>     end if
> next
Syvers - 30 Jan 2008 15:20 GMT
Hi Douglas

I should have mentioned i tried that method References.Remove Ref but it
does not recognise the ref as existing, i.e. i cant even get ref.name, seems
like a complete catch 22. Cant add because it exists, cant remove because it
cant be found!!

Anyway, have been chatting to a colleague about late binding and although we
are nearly at the end of our application development we think it best to
review all references to word and excel and use the CreateObject(".....")
method aka late binding.

Thanks for your help and advice

Paul

> When a reference is broken, you can't access most of its properties.
> However, there's no reason why you can't delete it. Inside your If
[quoted text clipped - 30 lines]
> >     end if
> > next
Marshall Barton - 30 Jan 2008 17:55 GMT
>I should have mentioned i tried that method References.Remove Ref but it
>does not recognise the ref as existing, i.e. i cant even get ref.name, seems
[quoted text clipped - 5 lines]
>review all references to word and excel and use the CreateObject(".....")
>method aka late binding.

You might find this article enlightening and further
encouragement to pursue late binding:
http://www.trigeminal.com/usenet/usenet026.asp?1033

Signature

Marsh
MVP [MS Access]

Douglas J. Steele - 30 Jan 2008 18:26 GMT
Unlike, say, the Delete method to delete a table from the TableDefs
collection, you don't need the name of the reference to use the Remove
method. The Remove method expects an actual reference to the object, not its
name. Did you try the code I suggested?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi Douglas
>
[quoted text clipped - 51 lines]
>> >     end if
>> > next
Syvers - 31 Jan 2008 10:53 GMT
Hi Douglas, yeah i had already tried that code but got an error message along
the lines of the ref object not existing. Have updated code now anyway to use
late binding, i think it is probably for the best and there wasnt as much use
as i thought so quite simple to update.

> Unlike, say, the Delete method to delete a table from the TableDefs
> collection, you don't need the name of the reference to use the Remove
[quoted text clipped - 56 lines]
> >> >     end if
> >> > next
Syvers - 31 Jan 2008 11:15 GMT
P.S. The link on your MVP page which is headed up Easily Change Application
Configurations Using INI Files is pointing to the same page as Effectively
Use Many-to-Many Relationships in Microsoft Access. I mention it because i
was interested in reading the INI files document.

Thanks again for your help.

> Unlike, say, the Delete method to delete a table from the TableDefs
> collection, you don't need the name of the reference to use the Remove
[quoted text clipped - 56 lines]
> >> >     end if
> >> > next
Douglas J. Steele - 31 Jan 2008 20:35 GMT
Hmm. Looking at
http://my.advisor.com/Articles.nsf/vWriterID?OpenView&RestrictToCategory=STEED
they don't seem to list the article (and others seem to be missing as well).

I'm on the road right now: will look into it when I get home.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> P.S. The link on your MVP page which is headed up Easily Change
> Application
> Configurations Using INI Files is pointing to the same page as Effectively
> Use Many-to-Many Relationships in Microsoft Access. I mention it because i
> was interested in reading the INI files document.
Paul Shapiro - 31 Jan 2008 00:17 GMT
I used to use code similar to what you had in the beginning of this thread
to replace broken references with downlevel references. But I don't think
it's working now. There are a few properties you can read for a broken
reference. Here's the code that used to work. If the ref.isBroken line is
throwing an error, you could catch that error and thereby know that the
reference is broken. The trick then is to know the correct guid's and
version numbers to try for adding the downgraded version references.

Alternatively, you can distribute the application in the oldest version in
use. Office references are generally upgraded automatically to the newer
version. I use Virtual PC to keep one or more virtual machines with the os
and office versions that I need to distribute. You can manually adjust the
references in the VM, and then distribute a "clean" version for users with
older software.

   For Each ref In Access.References
       strGUID = ref.Guid
       'Unfortunately, this seems to fail on the next line when a reference
is broken.
       If ref.IsBroken Then
           'Print the available details for broken reference
           Debug.Print "Reference: " & strGUID _
            & " (Broken) v." & ref.Major & "." & ref.Minor
           Select Case strGUID
           Case "{00062FFF-0000-0000-C000-000000000046}"
               'Outlook 9.3 (Office 2007) - try using Outlook 9.2 (Office
2003)
               References.Remove ref
               References.AddFromGuid strGUID, 9, 2
           Case "{00020905-0000-0000-C000-000000000046}"
               'Word 8.4 (Office 2007) - try using Word 8.3 (Office 2003)
               References.Remove ref
               References.AddFromGuid strGUID, 8, 3
           Case "{00020813-0000-0000-C000-000000000046}"
               'Excel 1.6 (Office 2007) - try using Excel 1.5 (Office 2003)
               References.Remove ref
               References.AddFromGuid strGUID, 1, 5
           'ElseIf Len(Dir(ref.FullPath)) > 0 Then 'Cannot read FullPath
when ref is broken
           '    References.AddFromGuid strGUID, ref.Major, ref.Minor
           Case Else
               strMsg = "Reference " & strGUID & " cannot be found." & _
                vbCrLf & vbCrLf & "Please contact the programmer."
               Beep
               MsgBox strMsg, vbCritical, "Cannot run the program"
           End Select
       End If
   Next ref

> Hi Douglas
>
[quoted text clipped - 51 lines]
>> >     end if
>> > next
Syvers - 31 Jan 2008 11:08 GMT
Unifortunately althought Ref.IsBroken is showing as True i cant use
References.Remove Ref it shows an error.

I should point out though that this does work in my ADP on 2007 i.e. i can
remove them if i choose and then add them back which i did for test purposes.
As soon as the reference is missing though it fails you cant remove it
because it is missing and cant add it because it conflicts with one that
already exists, yet its broken!!!

Anyway, we have gone for late binding solution now as it seems the best
option, will put an arror message out if excel or word doesnt exist on the
users machine.

Thanks very much for your help.

Paul

> I used to use code similar to what you had in the beginning of this thread
> to replace broken references with downlevel references. But I don't think
[quoted text clipped - 100 lines]
> >> >     end if
> >> > next
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.