MISSING reference, but file is there. 1

[RottPaws]

I've got an Access97 front-end with a SQL Server back-end. I'm using ADO to connect to the SQL Server db. I created an mde file and distributed that to other people who need to use the database.

It works great on the computer I used to write all the code, but when I copy the front-end to other computers, I get the infamous 'Can't find project or library' error.

All computers are NT4 using Access97.

When I check the references, the Microsoft ActiveX Data Objects 2.6 Library is tagged MISSING:

I haven't checked all the other computers, but at least one of them has the file right where the reference says it's supposed to be.

I've tried unchecking and rechecking it. I've tried using the Browse button and then drilling down to the file. Nothing has worked.

Even if it did work on this one computer, I can't go around to every computer that needs to use this db to reset their reference . . .

I've read a number of threads and MSDN docs related to this, but none has given me the answer I need to resolve this problem.

Can anyone help? _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[BillPower]

See if this helps, I've posted this as a tip, but it was your thread that gave me the idea.

CheckUsersReferences, compares a table of reference names and file names created at design time with a table of the same created at run time:

Table Name: tblReferences
RefName RefFileName
==================
VBA VBA332.DLL
Access MSACC8.OLB
DAO dao2535.tlb

In the above table,

RefName is the reference name as identified by Access, VBA would appear in the reference list in a module as “Visual Basic for Applications”, Access would appear as “Microsoft Access x.0 Object Library” etc.

RefFileName is the actual file name.

To create your own table of references before deployment, paste this into a module on a form:

Function CreateTableOfMyReferences()
Dim i As Integer, j As Integer 'counters
Dim intCurrPos As Integer 'used by InStr to get 1st "\" in path of file name
Dim intNextPos As Integer 'used by InStr to get subsequent "\" in path of file name
Dim intLength As Integer 'length of path to file name
Dim strRefName As String
Dim strRefFullPath As String
Dim strRefFileName As String
Dim strTable As String 'name of table to hold ref name and file name
Dim strSQL As String
Dim ref As Reference

strTable = "tblReferences" 'Rename table if name conflicts
DoCmd.SetWarnings False
For Each ref In References
intCurrPos = 1
strRefFullPath = ref.FullPath
intLength = Len(strRefFullPath)
For i = 1 To intLength Step intCurrPos
intNextPos = InStr(intCurrPos + 1, strRefFullPath, "\&quot
If intNextPos = 0 Then
strRefFileName = Mid(strRefFullPath, intCurrPos + 1, intLength)
If j = 0 Then 'if this is th 1st reference make a table with the 1st ref
strSQL = "SELECT '" & ref.Name & "' AS RefName, '" & strRefFileName & _
"' AS RefFileName INTO " & strTable & ";"
Else 'table already created, add next ref
strSQL = "INSERT INTO " & strTable & " ( RefName, RefFileName ) " & _
"SELECT '" & ref.Name & " '" & " AS RefName, '" & _
strRefFileName & " ' AS RefFileName;"
End If
DoCmd.RunSQL (strSQL)
j = j + 1
Exit For
End If
intCurrPos = intNextPos
Next i
Next
DoCmd.SetWarnings True
End Function

Paste this behind a button on the form:

CreateTableOfMyReferences

CreateTableOfMyReferences will make a table listing all libraries, ocx’s etc used in your app.

========================================

Briefly, when the user of the deployed app logs on the following procedure, CheckUsersReferences, makes a table of references locally on the user’s PC, compares this table to the table above, if there are any missing or broken references, attempts to find the ref, if it’s found attempts to reference it. If the file exists (hasn’t been deleted for some reason), this should be a trouble free exercise.

To use this function paste the following into a global module:

Declare Function GetSystemDirectory Lib "kernel32.dll" Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long

Paste the following into the declarations section of the form that loads at startup:

Dim strRef As String
Dim strFoundRef As String
Dim strTable As String
Dim strTempTable As String
Dim strTempTableCount As String

Paste the following into the module of the form that loads at startup:

Function CheckUsersReferences()
Dim ref As Reference
Dim strSQL As String
Dim i As Integer, x As Integer
strTable = "tblReferences" 'Rename table if name conflicts
strTempTable = "tblTempReferences" 'Rename table if name conflicts
strTempTableCount = "tblTempReferencesCount" 'Rename table if name conflicts
DoCmd.SetWarnings False
For Each ref In References
If i = 0 Then
If ref.IsBroken = False Then
strSQL = "SELECT '" & ref.Name & "' AS RefName INTO " & strTempTable & ";"
End If
Else
If ref.IsBroken = False Then
strSQL = "INSERT INTO " & strTempTable & " ( RefName ) SELECT '" & _
ref.Name & "' AS RefName;"
End If
End If
DoCmd.RunSQL (strSQL)
i = i + 1
Next
strSQL = "SELECT " & strTable & ".RefFileName INTO " & strTempTableCount & " FROM " & _
strTable & " LEFT JOIN " & strTempTable & " ON " & strTable & ".RefName = " & _
strTempTable & ".RefName WHERE (((" & strTempTable & ".RefName) Is Null));"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
i = DCount("*", strTempTableCount)
If i = 0 Then
GoTo ExitCheckMyReferences
Else
For x = 1 To i
strRef = DFirst("[RefFileName]", strTempTableCount)
strSQL = "DELETE " & strTempTableCount & ".RefFileName FROM " & strTempTableCount & _
" WHERE (((" & strTempTableCount & ".RefFileName)=" & "'" & strRef & "'));"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
If SearchForReferencesFileLocation = False Then
GoTo ExitCheckMyReferences
End If
ReferenceThisControl
Next x
End If
ExitCheckMyReferences:
DoCmd.DeleteObject acTable, strTempTable
DoCmd.DeleteObject acTable, strTempTableCount
End Function

Function SearchForReferencesFileLocation()
Dim i As Integer
Dim sysdir As String
Dim slength As Long
sysdir = Space(255)
slength = GetSystemDirectory(sysdir, 255)
sysdir = Left(sysdir, 3)
SearchForReferencesFileLocation = False
MsgBox "File " & strRef & " is not referenced correctly." & vbCrLf & _
"MS Access will now attempt to find " & strRef & vbCrLf & _
"This might take a few minutes.", vbInformation 'display your own message
With Application.FileSearch
.NewSearch
.LookIn = sysdir 'look in system directory, add your own proc to search multiple directories
.SearchSubFolders = True
.FileName = strRef
If .Execute() > 0 Then
strFoundRef = .FoundFiles(1)
SearchForReferencesFileLocation = True
Else
MsgBox "File " & strRef & " reference was not found." & vbCrLf & _
"Please take a note of " & strRef & ", then inform the " & _
"System Administrator" 'display your own message
SearchForReferencesFileLocation = False
End If
End With
End Function

Function ReferenceFromFile(strFileName As String) As Boolean
Dim ref As Reference
On Error GoTo Error_ReferenceFromFile
Set ref = References.AddFromFile(strFileName)
ReferenceFromFile = True
Exit Function

Error_ReferenceFromFile:
MsgBox Err & ": " & Err.Description 'display your own message
ReferenceFromFile = False
End Function

Function ReferenceThisControl()
If ReferenceFromFile(strFoundRef) = True Then
MsgBox "File " & strRef & " reference set successfully." & vbCrLf & _
"Next time you log on, MS Access will remember this setting" 'display your own message
Else
MsgBox "File " & strRef & " reference was not set successfully." & vbCrLf & _
"Please take a note of " & strRef & ", then inform the " & _
"System Administrator" 'display your own message
End If
End Function

Paste the following into the on load event of the form that loads at startup:

CheckUsersReferences

Though this looks like a lenghty procedure, as long as the deployed app’s references match the design time references, loading time is unnoticably affected and loading continues as normal after the check. Where there is a reference problem, user participation has been kept to a minimum, with just a message telling the user that a search is about to be done.

To test this Function, create a new database, add a form, follow the instructions above, in a module add a reference to it. Run CreateTableOfMyReferences, delete the reference, close and open the database. The deleted reference should be automatically attached. Close the database, open it again, it should open instantly, because all the references agree.

Let me know if you encounter any problems with the above.

 

[RottPaws]

Thanks for the response and all the code! This is outstanding!

Sorry for the delay in responding, but I got redirected last week on other things.

For future reference for anyone else who stumbles onto this thread in the future, my problem turned out to be a matter of MDAC versions. I had 2.6 on my computer and most of the other computers had only 2.1 or 2.5.

It seems that msado15.dll is the filename for several versions. So even though the file was on the computer and even in the location, it was still marked as MISSING because it was the wrong version.

I got my users to update to the same MDAC version that I have on my PC and now it's working. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!