Select from mulitple Backends

[ontopofmalvern]

Hi

I have a split database I use to manage my school's timetable. What I want to be able to do is work on new timetable while still being able to access and tweak current one. The easiest way I see to this is to able to switch between different backends.

This is effectively what I am doing at the moment - as it is a single user database - I just move or rename the backend in windows and relink to using linked tables manager - I'd like a more elegant solution which would allow me to switch in the front end. I can easily enough create the new backend in Windows i just want a bit of VBA I can put behind a button to close one backend and open another.

As I am the only user it doesn't need to be pretty I can hard code filenames, paths etc.

Hope that makes sense

Richard

 

[moke123]

One simple utility is Jstreet relinker....http://www.jstreettech.com/downloads.aspx

 

[isladogs]

I use the following approach based on 2 tables:
1. Table tblTableLinks with all name & alias of all tables
TableID (autonumber PK), TableName, TableAlias, LinkType (FK number), LinkActive (boolean)

2. Table tblTableLinkTypes with details of locations for each link
TableLinkType (PK autonumber), LinkDescription, LinkType, LinkFolder, LinkDatabase, LinkUserName, LinkPassword, LinkInUse (boolean)

User name / password may not be needed
If the BE files have no user name / password info omit those fields


To relink I have a lengthy routine which runs from a form.
A combo box or listbox is used to select the new location
Code first removes all current links, then loops through the recordset updating the links to the new location.
Connection strings are used to manage the links - not ODBC
If necessary additional code can be used here if anything else needs doing

The whole process takes a few seconds to complete

Hopefully that's enough to get you started
If necessary I can supply the code but I'm out for most of today

 

[ontopofmalvern]

Thank-you moke123 and ridders for your quick replies, I think I'll use the Jstreet relinker as it is ready made just tested it and it seems to work like a dream.

 

[isladogs]

Good idea - no point reinventing the wheel (as I did!)