Access VBA adding reference is duplicating

Question

I'm currently working on the main Access database of my application, and whenever I add a reference using VBA it seems to double up the reference under the Project Window.

Below is the following code I'm using:

Private Sub AddRef()    
     Application.References.AddFromFile ("C:\Databases\Database2.accdb")
End Sub

If I add the reference manually Tools > References, it only adds one instance of the reference.

If I use this exact code in another fresh database it only loads the reference once.

I know it's not extremely hard for Access databases to get corrupted, is that what this would be, or has anyone else experienced this issue and have an idea on how to resolve this problem?

This is the procedure I just created and ran, still doubling up in the project window. If I go to remove the reference (tools menu) it only shows once

Private Sub Test2() 
Dim ref As Reference 
Dim refExists As Boolean 
refExists = False With CurrentProject.Application.References 

For Each ref In References 
    If ref.name = "ARS" Then 
        refExists = True 
    End If Next 
End With 

If refExists = False Then 
    CurrentProject.Application.References.AddFromFile (CurrentProject.Path & "\Sections\ARS.accdb") 
End If 
End Sub

Update 5/7/18: So I figured out another part to the puzzle. If I move the databases to any other directory the procedures run correctly and the referenced database only appears once in the project window. So I don't know why it's doing it in this one specific directory but at least It shouldn't happen when I make the updates live.

How about an If condition to check if reference exists and then adding it ? — Clint, May 04 '18 at 20:45
It looks as if you may have answered your own question. It's plausible a repair may fix it. What @Clint said is a good start for debugging purposes. — ccarpenter32, May 04 '18 at 20:46
@Clint I have done an If Exists procedure and that seems to be doubling up as well. — kpudlo, May 04 '18 at 20:49
@Jiggles32 I have done a compact and repair, and it still does it after as well — kpudlo, May 04 '18 at 20:51
@kpudlo, how will there be multiple references if you are adding it only after checking ? — Clint, May 04 '18 at 20:53
Are you calling the procedure twice by chance, maybe somewhere you're not looking? — ccarpenter32, May 04 '18 at 20:55

score 2 · Answer 1 · answered May 04 '18 at 21:01

2

Try

If Dir("C:\Databases\Database2.accdb") <> "" And Not refExists("access") Then
    Access.References.AddFromFile ("C:\Databases\Database2.accdb")

Function Definition: refExists()

Private Function refExists(naam As String)
Dim ref As Reference
refExists = False
For Each ref In References
    If ref.Name = naam Then
        refExists = True
    End If
Next
End Function

Check Answer by TheLaurens: Add references programmatically

answered May 04 '18 at 21:01

Clint

6,011
1
21
28

1

I did try that, it didn't seem to get past the if statement. I'll take a look at it again on Monday when I'm back at work. I'm also wondering if looping through the VBProjects might work? – kpudlo May 04 '18 at 21:31

Access VBA adding reference is duplicating

1 Answers1