Refresh Links to Multiple Backends?

[SteveC24]

Hello,

I have been quite happy with my existing code that relinked the backend db, in case it was all moved directories or anything. Now, I am looking to have more than 1 backend.

How might I go about getting the db to refresh the links to multiple backends?

Any help greatly appreciated!

 

[Travis]

The code should be very similar to how you are doing the single link refresh. However, you will need to break the tables out that will be refreshed with each Backend. In the Past I have used a Table Structure to Hold the Table names and what Database they reside in.

Of course without know what your code for relinking looks like I can't give you any additional usfull advice.

 

[SteveC24]

Thanks for your reply.

The code I am using was pinched off here AGES ago....

Function RefreshLinks(strFileName As String) As Boolean

    Dim dbs As Database
    Dim intCount As Integer
    Dim tdf As TableDef

    Set dbs = CurrentDb
    For intCount = 0 To dbs.TableDefs.count - 1
        Set tdf = dbs.TableDefs(intCount)

        If Len(tdf.Connect) > 0 Then
            tdf.Connect = ";DATABASE=" & strFileName
            Err = 0
            On Error Resume Next
            tdf.RefreshLink
            If Err <> 0 Then
                RefreshLinks = False
                Exit Function
            End If
        End If
    Next intCount

    RefreshLinks = True
    
End Function

Thanks again!

 

[SteveC24]

Sorry to post just to bump this back up to the top, but I am still none-the-wiser as to how to go about this!

Any help much appreciated...if it is easier, I can change the code I am using to relink the backend...that isn't a problem.

 

[Travis]

First Create a Table Two Columns (tblLinkage)

1. Table name [TableName]
2. Database Table Resides in [DatabaseName]

'Change the strFileName to strPath
Function RefreshLinks(strPath As String) As Boolean

    Dim dbs As Database
    Dim intCount As Integer
    Dim tdf As TableDef

    Dim strFileName as String

    Set dbs = CurrentDb
    For intCount = 0 To dbs.TableDefs.count - 1
        Set tdf = dbs.TableDefs(intCount)

        'First is the Field Name in the table that Holds the MDB Name
        'Second is the Name of the Table that Holds the TableNames and What Database they are in
        'Third is the Where Expression
        [COLOR=Red]strPath=[/COLOR]DLookup("[DatabaseName]", "[tblLinkage]", "TableName='" & tdf.Name & "'")

        If Len(tdf.Connect) > 0 Then
            tdf.Connect = ";DATABASE=" & strPath & strFileName
            Err = 0
            On Error Resume Next
            tdf.RefreshLink
            If Err <> 0 Then
                RefreshLinks = False
                Exit Function
            End If
        End If
    Next intCount

    RefreshLinks = True
    
End Function

This changes your procedure to just passing the Path where the Files Reside. This of course assumes that both databases got moved to the same place.

Another option to enhance the above would be to create a table that contains two fields the Database Name and Path. That way you just update this and then run the procedure (this method no variables need to be passed as they can be looked up in a table structure)

I apoligize forgot what is in red above

 

[SteveC24]

Thank you very much for your reply - sorry for my delay...

I have just tried that code, and it isn't liking the DLookup. It is telling me that is expects a "=", and just turns the dlookup red.

I have tried to sort it myself a couple of ways, but am not totally sure of what all the apostrophies etc at the end do - so I havn't had any success!

Thanks for your continued help! Much appreciated!

 

[SteveC24]

Sorry Travis, I am still having problems...

I have made the change you put in your code, but when I run the code, I get the following:

Runtime error '94':
Invalid use of null.

Clicking debug highlights the:
strPath = DLookup("[DatabaseName]", "[tblLinkage]", "TableName='" & tdf.Name & "'")
line.

Any ideas? I have populated the table with the names of the tables, and the filename of the MDB.

Thank you for your continued assistance!

 

[Travis]

If you are using this line of code

strPath = DLookup("[DatabaseName]", "[tblLinkage]", "TableName='" & tdf.Name & "'")


I'll Assume that you have a Table named 'tblLinkage'
It also has two fields 'Databasename' and 'Tablename'

Also the Name returned by tdf.Name exists in this table and the field 'DatabaseName' has a value that is not null.

Also just as a note. (My bad) it should be

strFileName
= DLookup("[DatabaseName]", "[tblLinkage]", "TableName='" & tdf.Name & "'")

 

[SteveC24]

OK - I have double-checked all that, and yep, it is all fine.

I am not sure what you mean by "the Name returned by tdf.Name exists in this table". I assume you mean that the database names I have entered DO actually exist...and they do, called back.mdb and back2.mdb. I have entered it exactly like that in the table too.

As for what the tdf.Name returns, I put in a little bit of code to display what tdf.Name equals JUST before that line of dodgy code, and apparently, it is:

"MSysAccessObjects"

NO idea what that is!!!!!????

 

[Travis]

that is one of those hidden system tables. Obviously the table is not (or should not be) in your linkage table. You will need to write code that skips these tables.

The result you are getting of Null is because that table name does not exist in you table, thus the [DatabaseName] field is returning Null.

Dlookup is returning Null.

You can trap this by using the NZ (NullToZero) function.

Wrap it around the DLookup Function

strFileName
= NZ(DLookup("[DatabaseName]", "[tblLinkage]", "TableName='" & tdf.Name & "'"),"")

This will set strFilename="" when the table name does not exist in the Linkage Table.

 

[SteveC24]

Well, I have made said changes - and there are no more errors, which is a bonus...but it isn't actually relinking the tables?!?

As I say, no error messages or anything, it seems to run through fine, but the backends arn't relinked?

 

[Travis]

You'll need to add a break point and walk through the code.

Check that the Path & filename combined actually points to the file you are trying to relink.

It is possible that you are missing the "\" between the path and the filename and it is not able to relink because the file does not exists.

You are not getting an error becuase of the "On Error Resume Next", but it will only try one table and then kick you out.

 

[SteveC24]

BRILLIANT!

Well, after ALL that it is now working beautifully. I have added a "\" to all the file names in the Linkage table, and it now relinks all the backends perfectly.

Thank you ever so, ever so much!!! You are SUPER!

 

[mazza]

I am using the same code but want to protect my backends with the default access password security.
Hwr when you set passwords it won't be able to relink as it is not able to open the backends because of the password(s).
Is there a code you can use here to open the backends with the password and then relink the tables???

 

[Dan_T]

I have a form that lists all the backend databases for an application in a combobox. You select one and it lists the tables you expect to see. Then you can select a new location and hit replace. It then drops the old and adds the new displaying success results for each table.

There are a few more processes going on to ensure you don't drop a table without adding its replacement etc. You can drop the form into any application and it will work. If it's any use I can mail you the form.

Dan T.

 

[SteveC24]

Have PM'ed you my address!

 

[DosR]

Hi! Dan T,

Could You Please send me that example form?

 

[Dan_T]

sure. Give me an email address and i will send it to you.

Dan

 

[DosR]

Here it was

botyemail*** NOSPAM***gmail.com

Thank You in advance