|
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)
DoCmd.Quit
End If
Exit_RefSet:
Exit Function
Err_RefSet:
If Err.Number = 53 Then
MsgBox "Error #" & Err.Number & ": " & Err.Description & ": " & ToRefPathFN
Else
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:
|
|
|
|
