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
– Clint, Commented May 4, 2018 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
– ccarpenter32, Commented May 4, 2018 at 20:46
@Clint I have done an If Exists procedure and that seems to be doubling up as well. — kpudlo
– kpudlo, Commented May 4, 2018 at 20:49
@Jiggles32 I have done a compact and repair, and it still does it after as well — kpudlo
– kpudlo, Commented May 4, 2018 at 20:51
@kpudlo, how will there be multiple references if you are adding it only after checking ? — Clint
– Clint, Commented May 4, 2018 at 20:53

Clint · Accepted Answer · 2018-05-04 21:01:51Z

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 4, 2018 at 21:01

Clint

6,7691 gold badge24 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kpudlo Over a year ago

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?

Collectives™ on Stack Overflow

Access VBA adding reference is duplicating

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related