Missing reference error

I have an application (2007/accdb) which uses the MS OLE File Property Reader for some of its functions. The DSO Dll file is included as a reference (see illustration). The C:\DsoFile folder is the default location that the MSI installs the reader to.
 Application referencesCurrently not all of the users have this utility installed. When the application is started, a check is made for the existence of the C:\DsoFile folder. If it isn't found, a flag is set.
All code related to the OLE File Property Reader is isolated in its own module. If the forementioned flag indicates the reader is not installed, none of the functions in the OLE File Property Reader module are called.
My hope was that so long as none of the code referring to the reader was executed on a PC where the reader wasn't installed, I wouldn't get an error. This is not turning out to be the case. if the computer doesn't have the reader installed, I am getting an error when the app first starts up on the following:
strWorkStation = Environ("ComputerName")
Specifically, the command 'Environ' isn't found.
As soon as I remove the  'missing' reference to the DSO OLE File Property Reader, everything starts up OK.
I recognize that all I need to do to work around this, is add the reference programmatically, should I need it. But I just wanted to confirm that my assumption about not getting a runtime error for a missing library if the library objects are not referenced was completely wrong.
Or is there another workaround?
Todd Harpham
Who is Participating?

Improve company productivity with a Business Account.Sign Up

shambaladConnect With a Mentor Author Commented:
I found an interesting article about references by (I think) Michael Kaplan on the Trigeminal website:
The subject is: How to guarantee that references will work in your applications
Quote from article -
"The issue centers around DISAMBIGUATION -- the process of taking an ambiguous symbol and figuring out what it means. The goal HERE is to make sure that VBA (and ideally the expression service) does no disambiguation whatsoever until you are sure that all dependencies in your application are present and properly registered."

Further down in the article, he states:
"With MDEs, the problem is a little less nasty than the above, as you can usually get away with running code until you hit a bad line..""

And therein lies the solution. I converted the accdb file to an accde file and I am no longer getting any errors from users who don't have the dll installed. Obviously, I'd get a hard halt if I actually tried to create any of the objects associated with the missing dll, but that's easy enough for me to manage.

Thanks to all for your input.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Seems you would need to set up Conditional Compilation, that way that code is not attempted to be compiled, although not sure this will work.  You make the test, then set a variable that indicated not to compile that code.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
From Help:

Understanding Conditional Compilation

You can use conditional compilation to run blocks of code selectively, for example, debugging statements comparing the speed of different approaches to the same programming task, or localizing an application for different languages.

You declare a conditional compiler constant in code with the #Const directive, and you denote blocks of code to be conditionally compiled with the #If...Then...#Else directive. The following example runs debug code or production code, based on the value of the conDebug variable.

' Declare public compilation constant in Declarations section.
#Const conDebug = 1

Sub SelectiveExecution()
    #If conDebug = 1 Then
        .                ' Run code with debugging statements.
        .                ' Run normal code.
    #End If
End Sub

conditional compiler constant
A Visual Basic identifier that is defined using the #Const compiler directive or defined in the host application and used by other compiler directives to determine when or if certain blocks of Visual Basic code are compiled.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Rey Obrero (Capricorn1)Commented:

try running this codes, remove broken ref
Sub RemBrokenRef()
    Dim varRef As Access.Reference
    Dim refCnt As Integer,blnBroke as boolean
    Dim j As Integer
     On Error Resume Next

    'Count the number of references in the database
    refCnt = Access.References.Count
    'Loop through each reference in the database
    'and determine if the reference is broken.
    'If it is broken, remove the Reference 
    For j= refCnt To 1 Step -1
      Set varRef = Access.References(j)
      With varRef
        blnBroke = .IsBroken
        If blnBroke = True Or Err <> 0 Then
           Access.References.Remove varRef
        End If
       End With
End Sub

Open in new window

It is not so simple.
I was hoping it could be, but unless you compile the app on each machine, the conditional compiling only happens on the dev machine.
Maybe, when it smashes into a busted reference, it would recompile--but I wouldn't count on it.

I think you need to check into late binding.
It does away with the need for the reference.
Have a look at Tony's explanation here
and google 'ms access late binding' for more explanations and examples of this technique
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yeah ... I've never used CC.  Seems you need the Constants.  Removing the reference as suggest could work, but I think that will leave the app in an uncompiled state.

Late Binding is probably the answer ...

Your code looks good, but won't work
Touching a busted reference in A2003 doesn't work.
Been there, done that.
Phoned MS even.
What purpose .IsBroken serves is a mystery given that you CANNOT use it to test and remove busted references.

I wasted almost a week messing with that :(
I even tried removing the references without testing them.
No chancie
If it's busted, you can't do ANYTHING with it in VBA

You MUST make sure your references aren't broken
A2003 at least.  Maybe A2007+ has changed--but I doubt it
shambaladAuthor Commented:
I knew that sooner or later I was going to have to delve into conditional compilation, I guess now's the time.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  Another way to avoid this is to use late binding.  That way, you don't force VBA to walkdown the reference chain at compile time.  

  Only when you execute something will it look for the reference.

shambaladAuthor Commented:
It's working for me.
shambaladAuthor Commented:
What I particularly like about this approach is that it just about eliminates the need to do any late binding.
But now every time you make changes, you have to re-create the accde, which can be time consuming.
You also have to make very certain you don't lose the last copy of your accdb.
And I assume you have an FE/BE structure in place already, or your data would be locked inside an uneditable accde.

I'd prefer the conditional compilation / late binding that Tony laid out.
Less risk of hosing myself with that :)
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 Well he could not have been early binding then, other wise you can't create a de as it needs to be compiled to do that.  Must be a straight DLL call.

  But then I'm left wondering why he got an error in the first place if he wasn't executing the call.

  Something doesn't quite add up...

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 Sorry, take that back...I got it now.  Reference was valid on his machine (and would compile), but not on users, so you don't pop an error till you hit the line.

  You should be able to do the same thing then by making sure to distrubte a db that is compiled.

shambaladAuthor Commented:
Note too, that the IsBroken property can serve a purpose here. Even if the reference is missing, I can 'touch' both the .IsBroken and the .GUID properties without Access returning an error. Since I know the .GUID property for the reference I am looking for, I can check for that, and positively identify if that is a broken reference or not.
Here is the routine I am running to determine if the Dso dll is installed; and it is not creating an error on PCs where the reference is missing:


Public Function DsoInstalled() As Boolean
      Const strProcedure As String = "CheckDso"
      Dim ref As Reference
10    On Error GoTo ErrorHandler
      ' DSOFile GUID: {58968145-CF00-4341-995F-2EE093F6ABA3}
20    For Each ref In Application.References
30       With ref
40          If .GUID = "{58968145-CF00-4341-995F-2EE093F6ABA3}" Then
50             If Not .IsBroken Then
60                DsoInstalled = True
70             End If      'If Not .IsBroken
80             Exit For
90          End If         'If .GUID = "{58968145-CF00-4341-995F-2EE093F6ABA3}"
100      End With          'With ref
110   Next ref             'For Each ref In Application.References

120   On Error GoTo 0
130   Exit Function

140   HandleError mstrModule, strProcedure, Err, Err.Description, Erl
150   Resume ExitFunction
End Function

Open in new window

shambaladAuthor Commented:
Creating the accde from the accdb literally takes a second. After which, I rename it to an accdr.
I almost have to chuckle where you assume I have an FE/BE structure in place already. Actually the accdr the user executes doesn't have any tables or queries in it. I have created a new model for my databases. The objects are small and bullet fast. Once I fill out the nuances of this design, I should probably write up a paper on it so you guys can review it (maybe suggest how to improve it).
But that's way beyond the scope of the current question.
<I almost have to chuckle where you assume I have an FE/BE structure in place already>
I help a OP once who inherited an accde that DIDN'T have an FE/BE, and had gotten corrupted.
Needless to say he didn't have fun.

So I asked.
shambaladAuthor Commented:
No problem Rick. I need to remind myself that there was once a time when I didn't know how to use a mouse. I appreciate and listen to all suggestions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.