Automatically updating/relinking certain references?

[Access9001]

I have a bunch of databases. When I port them over to a new server, they break on open due to two references. Is there a way to update these references without needing to go into VBA Tools References manually for each one?

 

[Access9001]

e.g. I want to update reference X from missing-path A to correct-path B. Some references don't automatically know where to look, so they go from A to B, but all that manual tweaking requires time I don't have.

 

[DJkarl]

You should look into Late Binding objects for production releases, it will avoid the reference issues you're running into.

 

[Access9001]

I understand the differences between early/late binding but I don't have the time to go through all these databases and code and uncheck all references/change the code from early to late. I can do that down the line, but not now.

 

[DJkarl]

Look into the Application.References object then, the issue you may run into though is if your code won't compile then it may not run to be able to update the references.

 

[Access9001]

Is there a way to do this with a database that opens/updates/scans other databases?

The problem is that when I open the offending database, it throws an error immediately.

 

[DJkarl]

Declare an access application object then set it = to the remote database.

Set appACC= getobject("PathToFile")

Then use the references method of the application object, that might work.

 

[Access9001]

Is there a way to open/get the object without kicking off any startup processes in that database? I just tried it and it just hung there until I realized it was beginning to run the automation of that db.

 

[DJkarl]

Hmm, if you have an autoexec macro or form set for startup that is more difficult, you can look at using API's to simulate holding down the shift key, but aside from that I don't know of a way to disable that code from running.

 

[Access9001]

Alright, I think I have the right code to open database with shift-held.

How might I iterate through its application.references and update them?

 

[DJkarl]

many options there, but if you know the old path and the new path I might try something like

If Application.References.Item(x).FullPath = OldPath Then
    Application.References.Item(x).FullPath = NewPath
end if

you could throw that into a for next loop, or use any other method you'd like.

 

[Access9001]

I had been trying this:

Sub test()
Dim a As Database
Dim varItem
varItem = "C:\pathtomydb\mydb.mdb"
Set a = OpenDatabase(varItem)

For Each refItem In a.References
      If refItem.IsBroken Then
         strMessage = "Missing Reference:" & vbCrLf & refItem.FullPath
      Else
         strMessage = "Reference: " & refItem.Name & vbCrLf _
            & "Location: " & refItem.FullPath & vbCrLf
      End If
      Debug.Print strMessage
   Next refItem


a.Close
Set a = Nothing
End Sub

But it doesn't seem to think a.references exists

 

[DJkarl]

I had been trying this:

Sub test()
Dim a As Database
Dim varItem
varItem = "C:\pathtomydb\mydb.mdb"
Set a = OpenDatabase(varItem)
 
For Each refItem In a.References
      If refItem.IsBroken Then
         strMessage = "Missing Reference:" & vbCrLf & refItem.FullPath
      Else
         strMessage = "Reference: " & refItem.Name & vbCrLf _
            & "Location: " & refItem.FullPath & vbCrLf
      End If
      Debug.Print strMessage
   Next refItem
 
 
a.Close
Set a = Nothing
End Sub

But it doesn't seem to think a.references exists

In this case it doesn't, references is part of the application object, not the database object. You need to declare a Access.Application variable then set that equal to your database.

 

[Access9001]

Trying code at http://access.mvps.org/access/api/api0068.htm

It's now not liking Fullpath property

 

[DJkarl]

Well it may not be possible then to programatically update just the path, you could try removing the references then remotely using the AddFromFile method of the reference object. Bottom line though this is dodgy business and I am not surprised it is difficult and perhaps not possible to do since Access gives aother way to get around reference issues.

 

[Access9001]

How do I remove a bad reference programatically when it technically doesn't exist?

 

[DJkarl]

I would think application.References.Remove

 

[Access9001]

Ok, this correctly iterates through them all:

x = 1

For Each myref In a.References
    If a.References.Item(x).IsBroken = True Then
        a.References.Remove (SOMETHING)
    Else
        Debug.Print a.References.Item(x).FullPath
    End If
    
    x = x + 1
Next myref

What is the "something" i should put here? It wantes type "reference" there but replacing SOMETHING with a.References.Item(x) doesn't work

 

[Access9001]

I also tried a.References.Item(x).FullPath = Replace(a.References.Item(x).FullPath, "D:\", "C:\")

and it said FullPath is a read-only property

 

[Access9001]

Also tried

Dim ref As Reference
 
For Each ref In a.References
  If ref.IsBroken Then a.References.Remove ref
Next

but got error "Object library not registered"... uh, isn't that the entire point?