Link/Re-link tables

Hi All

I've read all the posts on Linking tables and found no solution to my problem. Would be grateful if someone could help.

I have a FE database linked to many BE databases. I'm concerned with only 2 tables in one of my BEs.

tblCourses
tblStudents

each table contains data within for an academic year.

Each table is static and should never be renamed or moved from one of two databases.

05.mdb
06.mdb

i.e. there is a tblCourses in both 05.mdb and 06.mdb with different data.

I have a form with a list box containing the following values:-

2005/2006
2006/2007

When the user selects 2005/2006 it links the tables to 05.mdb likewise when the user selects 2006/2007 it links these 2 tables to 06.mdb.

I would have thought this was really easier - without having to write pages and pages of code?!?

The nearest I have got is the following code:

Dim dbsTemp As Database
Dim tdfLinked As TableDef
Dim strTable As String
Dim strConnect As String
Dim strSourceTable As String

Set dbsTemp = CurrentDb

'Fill in the name of the link
strTable = "tblCourses"

'Fill in the path of the database you want to link to
If FindAcadYear = "2005/2006" Then
strConnect = "\\server\share\05_BE.mdb"
ElseIf FindAcadYear = "2006/2007" Then
strConnect = "\\server\share\06_BE.mdb"
End If

'Fill in the name of the table you want to link to
strSourceTable = "tblQLCourses"

Set tdfLinked = dbsTemp.CreateTableDef(strTable)

tdfLinked.Connect = ";DATABASE=" & strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked

But all this does is say the table already exists. I don't want to use the Linked Table Manager and I don't want to have to delete the tables first.

Thanks & Regards

I have never tried it, but I would have thought you could make use of the MSysObjects table for this. This is the system objects table that stores all data regarding your tables/forms, etc. If you look at the field 'database' it stores the path of the linked table. You could then write a couple of update queries to change the path of the table from 05.mdb to 06.mdb (and vice versa). - I am obviously presuming the table names are exactly the same in each.

If this doesn;t work then the code on the following site could be modified for your use

http://www.mvps.org/access/tables/tbl0009.htm

You could use Docmd.Transferdatabase to link the table.

I think you will have to delete the table first, but as its a linked table you're only deleting it from the FE.
Docmd.DeleteObject actable, "YourTable"

Hi

I don't want to delete the table first.

Thanks for the code but I alsso don't want the user to specify the location. it will be one of two locations which will also be static.

Thanks

here ya go:

Thanks for this, How do I place this code after here.

Private Sub OKButton_Click()

Also am I right in thinking that the following lines would be changed:-

arrTables = Array("tblmyFirstTable", _
"tblmySecondTable", _
"tblmyThirdTable")

For Each tblName In arrTables
Set tbldef = db.TableDefs(tblName)
tbldef.Connect = ";DATABASE=MYBEDatabase.mdb" & "\\myserver\lmyshare"

Also am I right in thinking that the following lines would be changed:-

arrTables = Array("tblmyFirstTable", _
"tblmySecondTable", _
"tblmyThirdTable")

Click to expand...

correct.

you pass the path statement and the database through the parameters, so you do not change the connect strings, or anything else within the sub routine except the array of table names. That array would be replaced by a table of table names once the number of relinked tables becomes sufficient to warrant the added overhead or the table names require routine maintenance.

I've done this and it now says

Sub or Function now defined.

Again, thanks.