views:

1874

answers:

2

My Application is built to a scan MS Access database in VB.NET.

When the Access application is distributed to end users, they may have different versions of COM components. Is it possible to Add/Remove references programmatically to resolve broken references due to differing versions?

Please share me code or link for reference.

+3  A: 

Here is some sample code:

Create Reference from File

  Sub AddWS()
  'Create a reference to Windows Script Host, '
  'where you will find FileSystemObject ' 
  'Reference name: "IWshRuntimeLibrary" '
  'Reference Name in references list: "Windows Script Host Object Model" '
  ReferenceFromFile "C:\WINDOWS\System32\wshom.ocx"
  End Sub

  Function ReferenceFromFile(strFileName As String) As Boolean
  Dim ref As Reference

         On Error GoTo Error_ReferenceFromFile
         References.AddFromFile (strFileName)
         ReferenceFromFile = True

  Exit_ReferenceFromFile:
         Exit Function

   Error_ReferenceFromFile:
         ReferenceFromFile = False
         Resume Exit_ReferenceFromFile
   End Function

Delete Reference

  Sub DeleteRef(RefName)
  Dim ref As Reference

      'You need a reference to remove '
      Set ref = References(RefName)
      References.Remove ref

  End Sub 


You can use the references collection to find if a reference exists.

Reference Exists

  Function RefExists(RefName)
  Dim ref As Object

     RefExists = False

     For Each ref In References
         If ref.Name = RefName Then
             RefExists = True
         End If
     Next

  End Function

From: http://wiki.lessthandot.com/index.php/Add,_Remove,_Check_References

You may also wish to read http://www.mvps.org/access/modules/mdl0022.htm

Remou
I am getting error while implementing this logic. as follwos:"Object reference not set to an instance of an object"Please suggest how to do.....Thank you...
Suman
Which line is giving this error?
Remou
On "Dim ref As References" getting error as above and in second type of declaration i.e. as below Dim ref as new References error is "Retrieving the COM class factory for component with CLSID {EB106214-9C89-11CF-A2B3-00A0C90542FF} failed due to the following error: 80040154."
Suman
Now I am able to check reference for Access. But I want to check all the references related to Office. Please let me know, how to do it? the above is done through Access object just after opening the DB.
Suman
+1  A: 

The best solution is to limit references in your Access MDB to internal Access components. This would be the Access reference, the VBA reference, and the DAO reference. All other outside libraries should be used through late binding. If you're using the File System Object, for instance, instead of this (with a reference to the Windows Script Host Object Model):

  Dim objFSO As New FileSystemObject

  If objFSO.FolderExists("\\d9m09521\WB\") Then
    ...
  End If

you would remove the reference and convert it to this:

  Dim objFSO As Object

  Set objFSO = CreateObject("Scripting.FileSystemObject")
  If objFSO.FolderExists("\\d9m09521\WB\") Then
    ...
  End If

If you're concerned about the performance hit of initializing the FSO each time you use it, you can cache a reference to it. I usually use a static variable inside a function to return an object like this:

Public Function FSO() As Object
  Static objFSO As Object

  If objFSO Is Nothing Then
     Set objFSO = CreateObject("Scripting.FileSystemObject")
  End If
  FSO = objFSO
End Function

Now, you might want to get fancy and also be able to tear down the instantiated object, in which case you'd do something like this:

Public Function FSO(Optional bolCloseObject As Boolean = False) As Object
  Static objFSO As Object

  If bolCloseObject Then
     Set objFSO = Nothing
     Exit Function
  End If
  If objFSO Is Nothing Then
     Set objFSO = CreateObject("Scripting.FileSystemObject")
  End If
  FSO = objFSO
End Function

In any event, the whole point is that late binding resolves the location of the outside libraries at runtime and thus won't break, except if the outside library is not installed or not properly registered. With late binding, you can trap for both of those conditions, but with early binding, your whole Access app simply breaks.

--
David W. Fenton
David Fenton Associates

David-W-Fenton