Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Missing reference error

Posted on 2011-09-26
Medium Priority
Last Modified: 2012-05-12
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
Question by:shambalad
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
LVL 75
ID: 36601150
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.

LVL 75
ID: 36601170
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.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36601281

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 26

Expert Comment

ID: 36601297
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
LVL 75
ID: 36601327
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 ...

LVL 26

Expert Comment

ID: 36601356
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

Author Comment

ID: 36601436
I knew that sooner or later I was going to have to delve into conditional compilation, I guess now's the time.
LVL 58
ID: 36601560

  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.


Accepted Solution

shambalad earned 0 total points
ID: 36711026
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.


Author Closing Comment

ID: 36898925
It's working for me.

Author Comment

ID: 36711226
What I particularly like about this approach is that it just about eliminates the need to do any late binding.
LVL 26

Expert Comment

ID: 36711306
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 :)
LVL 58
ID: 36711335

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

LVL 58
ID: 36711358

 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.


Author Comment

ID: 36711499
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


Author Comment

ID: 36711510
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.
LVL 26

Expert Comment

ID: 36711584
<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.

Author Comment

ID: 36711803
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.


Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question