Solved

Missing reference error

Posted on 2011-09-26
18
350 Views
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?
Thanks,
Todd Harpham
0
Comment
Question by:shambalad
  • 7
  • 4
  • 3
  • +2
18 Comments
 
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.

mx
0
 
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.
        .
        .
    #Else
        .                ' 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.

Capture1.gif
0
 
LVL 119

Expert Comment

by:Rey Obrero
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
    Next
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36601297
@mx
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.

@shambalad
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
http://www.granite.ab.ca/access/latebinding.htm
and google 'ms access late binding' for more explanations and examples of this technique
0
 
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 ...

mx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36601356
@capricorn1
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
0
 
LVL 7

Author Comment

by:shambalad
ID: 36601436
I knew that sooner or later I was going to have to delve into conditional compilation, I guess now's the time.
0
 
LVL 57
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.

Jim.
0
 
LVL 7

Accepted Solution

by:
shambalad earned 0 total points
ID: 36711026
I found an interesting article about references by (I think) Michael Kaplan on the Trigeminal website:
http://www.trigeminal.com/usenet/usenet026.asp?1033
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.

Todd
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Author Closing Comment

by:shambalad
ID: 36898925
It's working for me.
0
 
LVL 7

Author Comment

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

Expert Comment

by:Nick67
ID: 36711306
True,
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 :)
YMMV
0
 
LVL 57
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...

Jim.
0
 
LVL 57
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.

Jim.
0
 
LVL 7

Author Comment

by:shambalad
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

ExitFunction:
120   On Error GoTo 0
130   Exit Function

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

Open in new window

0
 
LVL 7

Author Comment

by:shambalad
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.
0
 
LVL 26

Expert Comment

by:Nick67
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.
0
 
LVL 7

Author Comment

by:shambalad
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.
Todd

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now