0

I'm having an issue where when I remove a reference from Access on close it does not stick. I have the function assigned to a hidden window that is always open and it works when I manually close the window, but when I close the database the change to remove the reference doesn't stick. We are having issues since part of our team is on Office 2013 so we have had to make a dynamic reference point in the VBA Code. The code to add the reference works fine, but removing it is the issue.

Here's the code for adding it Public Function RunThis()

Dim ref As Reference

'For Each ref In Access.References

       'MsgBox ref.Name

'Next

If Dir("C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.exe") <> "" Then
    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.exe")
End If
If Dir("C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.exe") <> "" Then
    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.exe")
End If

End Function

Here's the code that is run on exit

Private Sub Form_Close()

Dim ref As Reference

    For Each ref In Access.References

        If ref.Name = "Excel" Then
            Access.Application.References.Remove ref
            'MsgBox "Found It"
        End If
        MsgBox ref.Name

Next
DoCmd.Save
End Sub
todaroa
  • 329
  • 1
  • 4
  • 15
  • If every user has their own local frontend (on a computer with one of the two Office versions), why do you need to remove the reference at all? – Andre May 04 '17 at 13:13
  • They don't all have their own front end. It is stored on a shared drive due to frequent modifications. – todaroa May 04 '17 at 13:26
  • 1
    If the frontend is shared and open by another user that uses a reference, you can't remove that reference. – Gustav May 04 '17 at 13:29
  • 1
    You may benefit from [this tip](https://stackoverflow.com/questions/35189702/acccess-2013-distribute-fe-or-leave-on-shared-network-drive-for-split-db/36382947#36382947). – Gustav May 04 '17 at 13:31
  • 2
    Isn't this a case for using late binding? No references needed, just open whichever version of Excel you have. – Darren Bartrup-Cook May 04 '17 at 13:42
  • @DarrenBartrup-Cook: It certainly is. But it might be a major rewrite if Excel functionality is used extensively. – Andre May 04 '17 at 13:43
  • @Andrew. Might be worth doing in the long-run though. – Darren Bartrup-Cook May 04 '17 at 13:49
  • @DarrenBartrup-Cook It does have extensive Excel references. It was something I inherited. – todaroa May 04 '17 at 17:48
  • I suggest reconsidering the hint from Andre. Split the DB into back and front ends. Keep the back end on the shared drive. Create a simple batch file / shell script to copy the latest frontend file to a local location and open it from there. Then it wouldn't matter if it keeps the reference or not. You don't need Citrix or other installed service to make this workable and reliable. I do this for only 4 frontend clients, but I suppose it will scale up to at least as many as you have accessing it now. – C Perkins May 05 '17 at 17:22

0 Answers0