Home Page
Tips And Tricks
Helpful Links
e-mail me

Picture of building from outside Tips And Tricks

I seem to run into things from time to time that I want to share with the world, usually things that I had to find out the hard way. Sometimes it will just be something that I had to dig for, and want to make more readily accessible. This page is intended to make life a little easier for other programmers. If it meets your need, then that's what it's here for.

Change MS Access Reference Programmatically (using code)
The following is a function to change a reference in MS Access programmatically. This is working code that was written to run in MS Access 2000. We call it from our batch files through a macro when we publish a new version. We are usually changing the reference from an ADP to an ADE, after we finish a compile.
Function SetNewRef(FromRef, ToRef, Optional NewPath)
 On Error GoTo Err_RefSet

 Dim ref As Reference
 Dim refName As String
 Dim refPath As String
 Dim ToRefPathFN As String
 For Each ref In Access.References
  Debug.Print ref.Name & " " & ref.FullPath & " " & ref.Major & "." & ref.Minor
  If InStr(ref.FullPath, FromRef) > 0 Then
   refName = ref.Name
   refPath = ref.FullPath
   ToRefPathFN = NewPath & ToRef
   Exit For
  End If
 Next ref

 If Len(Dir(ToRefPathFN)) > 0 Then 'don't try to unhook unless file exists
  ' Remove the reference if it is present   -   <=======
  On Error Resume Next
  If Err.Number = 0 Then
   References.Remove ref
  ElseIf Err.Number <> 9 Then 'Subscript out of range meaning not reference not found
   MsgBox Err.Description
   Exit Function
  End If
  ' Use your own error handling label here
  On Error GoTo Err_RefSet
  'The code to programmatically add the reference is:
  Set ref = References.AddFromFile(ToRefPathFN)
 End If

 Exit Function

 If Err.Number = 53 Then
  MsgBox "Error #" & Err.Number & ": " & Err.Description & ": " & ToRefPathFN
  MsgBox "Error #" & Err.Number & ": " & Err.Description
 End If
 Resume Exit_RefSet
End Function

For example, a call might look like:
SetNewRef("ProcLibCS.ADP","ProcLibCS.ADE", "H:\SomeFolder\")

If you found this information to be useful, and would like to donate via PayPal to help offset the cost of maintaining this web site, use the following button:

|Home Page| |Custom Software| |Policies| |About| |Tips And Tricks| |Helpful Links|