mde is missing a reference

[Niebotel]

After creating with my packaging wizard an installation with an frontend mde end a backend mdb and the runtime environment I get the follwing problem when |I start my installed application on the users PC:
Function isn't available in expressions in query expression
This problem is described by microsoft for ACC 97 but not for 2002. Besides the solution they give is not possible for mde files.
Is there another cause or is there an other solution? Can I include all dll's in my installation? Shouls I put them in the prgram map or on the original place?
Please help!
Greetings Willem de Nie

By the way the references in the MDB look like this (I do not know how to check whether I need them all is has been growing over some years

Visual basic for applications
MicrosoftACCESS 10.0 object library
Microsoft DAO 3.6
OLE automation
MSVisual basic for applications Extensibility
MS ActiveXData objects 2.1
MS Windows common controls 26.0 sp6
MS ADO ext 2.8for DLL and security
MSoffice xp web components
Snelstart gateway (I know what this is)
MS Calendar control 10.0

 

[jrbarnett]

Put some code in your database and set up a command button to run it:

Dim ref As Reference

For Each ref In Application.References
  Msgbox Application.REferences(ref).FullPath & " " & Application.References(intReferenceCount).IsBroken
Next Ref

This will give you the filename and whether it is missing/broken - a true/false description.

If you need to get the descriptive title of a reference, use this function.
It is lifted straight from my MDB Doc source code:

Private Function GetGuidDescription(strGuid As String, strMajorVersion As String, strMinorVersion As String) As String
    'MDBDOC: This function returns the string description of a specific GUID and version number.
    ' Function: GetGuidDescription
    ' Scope: Public
    ' Parameters: strGuid - GUID of the reference, strMajorVersion - Major version number, strMinorVersion - Minor version number
    ' Return value: String - the description of the Guid (from the Windows Registry)
    ' Date: 13 October 2007
    ' This requires the API functions wrapped in modRegistry to work.
    ' Called from: mdbdProcessDatabase

    ' It is used in the functionality to retrieve the description of a reference.
    ' This is stored in the Registry as:
    ' HKEY_CLASSES_ROOT\TypeLib\<guid>\<majorversion>.<minorversion>

    GetGuidDescription = QueryValue(HKEY_CLASSES_ROOT, "TypeLib\" & strGuid & "\" & strMajorVersion & "." & strMinorVersion, "")
End Function

You just pass in the Application GUID and major/minor versions - eg

MsgBox GetGuidDescription(Application.References(intReferenceCount).Guid, Application.References(ref).Major,                   Application.References(ref).Minor)

These together will let you work out which references are working and which are broken.

From my experience with the Access XP runtime though, its most likely to be the MS Office XP Web components.

John

 

[Niebotel]

Thanks for your quick response. I was out one day so I could not react earlier.
I will include this coding and test. But already an question? Why do I not get any problems in my mdb amd mde in the nornal access environment but only when I use the runtime (on another machine). And what to to if I finf out een reference is missing? How to solve without manually interfere, The installation packahe should solve it.
Greetings Willem de Nie

 

[Remou]

It is often best to use late binding:

Dim objXL As Object

Rather than:

Dim objXL As Excel.Application

This means that if a user has a different version of, say, the Excel library, the code will still work. You will, however, have to convert the built in constants to values.

 

[jrbarnett]

The runtime version of Access doesn't include the Office XP web components reference. I haven't figured out why either, but it works.

As Remou says, Late binding can get around these problems in a mixed version environment.

John

 

[Niebotel]

Sorry do not understand. My references are defined within the VBA of ACCESS by selecting then in a screen. Not by definung them in VBA code. Can you explain what you mean?

 

[jrbarnett]

When you install Access, MS Office (or Access Runtime) some files get installed on disk and registered on the computer in its Registry that enables applications to use it.

When you are using the Tools References screen in the VBA editor, what you are doing is adding a link in your database to that particular component.
This lets you use the Intellisense features of the VBA editor (the drop down lists of subs/functions in a particular reference, parameter lists when calling functions/subroutines etc).
This is all well and good, provided that the target machines have exactly the same versions of the software installed as that you developed and tested on.

Without it, Access won't be able to find the reference (it uses the GUID, MajorVersion and MinorVersion property of the file to locate the equivalent one on each PC).

Hence Remou's suggestion - you don't set a reference at all. Instead, you declare a variable of type Object, then set it to the type of variable that you need.
This has the big advantage of being version independent (for example, Access 97 instead of Access XP on a different computer, or Access Runtime instead of Access full version) at the expense of being able to use the intellisense features to help you when coding/debugging.

John

 

[Niebotel]

I understand what you mean, but I do not understand yet how to do so.
For example I now use: Microsoft DAO 3.6
as reference; what kind of coding should I add? To prevent all kind of compilation errors?
I assume this should be basic knowledge for VBA programmer, but I do not have that knowledge. But i wlould like to learn.
Thanks on forehand for your help.

Greetings Willem de Nie

 

[Niebotel]

I'm still looking for (an easy) solution.
Is an solution to include all needed dll's in my installation package? Can I store them in my application map? So the originals on the other machine are not overwritten? Or is this not possible?
Greeting Willem (NL), curious to get an answer.

 

[jrbarnett]

Did my code tell you which references were broken?

John

 

[Niebotel]

Not yet, but I'm working on it. But If I know what reference is missing I still do not know jet how to solve that. I want to solve it from my development environment in the installation wizard. I do not want end-users to add manuallu files and to register them.. I will let you know when I have finished the extra coding.
The other solution (binding) is to complicated for me at this moment.
Gr Willem

 

[Niebotel]

I added the code:
Dim ref As Reference

For Each ref In Application.References
Msgbox Application.REferences(ref).FullPath & " " & Application.References(intReferenceCount).IsBroken
Next Ref

But I get a Type mismatch on it.

Am I very stupid?

 

[Remou]

Am I very stupid?" No.

Try:

Dim ref As Reference

For Each ref In Application.References
    If ref.IsBroken Then
      MsgBox ref.FullPath
    End If
Next ref

 

[Niebotel]

I included the code; however it does not show anything.
I then added:
Dim ref As Reference

For Each ref In Application.References
If ref.IsBroken Then
MsgBox ref.FullPath
Else
MsgBox "OK " + ref.FullPath

End If
Next ref
And then all the references are displayed (on both machines)
So I take a break; I will take a walk in the rain!
If you have suggestions I appreciate them!
Can it have something to do with the registration of teh references? I remenber I had this problem a long time ago and then I solved it by registration of a dll in the registry. (I do not know anymore which one)
Gr Willem

 

[jrbarnett]

Put Remou's code in its own sub:

Create a module and paste the following code in:

Public Sub ShowBrokenReferences
Dim ref As Reference

For Each ref In Application.References
    If ref.IsBroken Then
      MsgBox ref.FullPath
    End If
Next ref
End Sub

On a computer that works, type ShowBrokenReferences
You shouldn't get any output (ie nothing is broken).

Run it on one of the problematic machines - it will give you one or more message boxes with the full path to the reference file it is expecting to find.
Can you post that here.

John

 

[Niebotel]

Hello, I added the code for checking te references: And guesse, No reference is missing!
So Ill start all over again. How can I discover what the reason is for a error message in a mde?
Help will be appriciated!
Willem

 

[Remou]

Can you try putting the mdb, rather than the mde, on the problem machines and compiling?

 

[Niebotel]

Yes I can, but what is the next step? Because this is not a basic solution for installing.
I also found out that some dll's on the development machine and the end-users machine are different is size and version. Is is possible to include all the needed dll's in the installation package? And put them for instance in the map where the mde is situated rather then in the normal places?
This what I found out:
dao360.dll has two different versions and
MSACC>OLB is not situated in Microsoft Office/office10 on the production machine
You wpuld expect to get error messages vy checking thereferences, but it did not.

 

[Niebotel]

I investigated all references on my development machine and on my production machine.
Development machine: Windows XP. Different versions of office have been installed in the near past, but on this moment on devlopment is Office XP developer and office 2003 (Office 2007 was installed and has been removed)
On the production machine: Vista and ACCESS 2003 (Office 2007 has been removed
List of used references
On both nachines the mdb works but the mde on runtime base gives errors!

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VEB6.DLL
Development: Versie 6.05.1024
Produktie: Idem
C:\Program Files\Microsoft Office\OFFICE10\MSACC.OBL
Development: Versie 10.0.2627.1
Produktie: OFFICE10 is leeg
C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.dll
Development: Versie 3.60.9512.0
Produktie: 3.60.9704.0
C:\Windows\System32\STDOLE2.TLB
Development: 3.50.5014 17 kB
Produktie: geen versie 16,5kB

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
Development: Versie 6.05.1024 44kB
Produktie: 44kB no version number
C:\Program Files\Common Files\System\ado\msado21.tlb
Development: versie 2.81.1117
Produktie 56kB
C:\Windows\System32\MSCOMCt2.ocx
Development: 6.1.97.82
Produktie: identical
C:\Program Files\Common Files\System\ado\msadox.dll
Development: 2.81.1128.0
Produktie: 6.0.6001.18000
C:\Program Files\Niebotel\SnelStartGateway.dll
Development: 8.11.00
Produktie: Identical
How to solve these differences??

 

[amilojko]

You shouldn't use FullPath for references, that will always cause an error when it's missing.
Because if the reference is missing Access won't know the full path and it will bomb. Just use isbroken.
This can't be and MDE issue.
Compile the mdb first before creating MDE. It may give you an idea