switching link between front-end and 2 back-ends

[WalkieTalkie]

I have an Access 97 database on a network, split into front-end, back-end. I want to archive all the records that are older than 2 years into another back-end (archive_be.mdb). I have written some code that will run some append and delete queries to copy the records, then delete them from the original back-end. The purpose of this is to limit the size, to improve performance etc (currently some of the tables have around 60,000 records)

My problem is that users need the ability to switch the link from the front-end, so they can use the same forms to switch between data from both back-ends. I have tried using the Connect and RefreshLinks methods but am having no success. Can anybody help?

The code I have tried so far is:

Dim dbs As Database, tblNewLink As TableDef
    Set dbs = CurrentDb
    Set tblNewLink = dbs.TableDefs("tbl1")
    tblNewLink.Connect = ";DATABASE=C:\Archive\Archive_be.mdb"
    tblNewLink.SourceTableName = "tbl1"
    Set dbs = Nothing]

The error I am getting is:
'Run-time error 3268: Can't set this property once the object is part of a collection', pointing to the line tblNewLink.SourceTableName = "tbl1"

I have tried leaving out the 'Set tblNewLink = dbs.TableDefs("tbl1")', but of course then get an error saying that the object variable isn't set.

Any help will be much appreciated!


'

 

[AvGuy]

Pass the name of the backend database to which you want to connect (strFileName) and call the following routine. It should work fine to switch between databases provided the filenames are the same.

Public Function RefreshTableLinks(strFileName As String) As Boolean
' Refresh links to the supplied database. Return True if successful.

Dim tdf As TableDef

' Loop through all tables in the database.
Set DB = CurrentDb
For Each tdf In DB.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
RefreshTableLinks = False
Exit Function
End If
End If
Next tdf

RefreshTableLinks = True ' Relinking complete.

End Function

Cheers,
AvGuy

 

[WalkieTalkie]

Thanks for that. It is now switching beautifully. The only problem now is that I would prefer the user not to have to close and re-open the front end form, in order to view the new back-end records. Is there a way to do that? Refresh and requery don't seem to work.
Miranda