Link to tables via code

[IfButOnly]

I am wanting to change the db where my linked tables via code.

I have an access application that remains consistent with only one concurrent user. each user may belong to a different group and I was wanting to keep their data seperate in linked tables. the application would have a menu such as :-

1... Junior Championship
2... Members Championship
3... Ladies Championship

The user selects one (or creates a new one) and the tables for that event are linked automatically via code.

Can anyone help point me in the right direction on this.. thanks

 

[lobodava]

Here is my example. Hope, it will be useful for you.

Public Function LinkTablesToProperDrive()
Dim tdf As DAO.TableDef
Dim strPath As String

strPath = ";DATABASE=\\server\MyDB.mdb"
'strPath = ";DATABASE=C:\MyDB.mdb"

For Each tdf In CurrentDb.TableDefs
    If Len(tdf.Connect) > 0 Then  'Linked tables fit the condition
        tdf.Connect = strPath
        tdf.RefreshLink
    End If
Next

Beep
MsgBox "Ok"
End Function

 

[IfButOnly]

Thanks a lot , lobodava, that was perfect.

 

[IfButOnly]

Thanks Pat, I will rethink the table layout. I originally moved away from that as the client will basically download the championship players from another system. The seperate db's made it simplier to do most functions for each comp - anyway will rethink.

As far as women members are concerned, we do have them but they play in seperate competitions - they are allowed to play in the men's but need to play off the men's tees and qualify for a men's handicap ( I think, the LPGA handicaps differently) - none have taken it up at this club.

Thanks again