Backend File Swap/Rename Utility Required

[PaulO]

Hi

I might be reaching for the Stars here (?) but here's what I'd like and why:-

I have a split database which, ordinarily, has a normal one-to-one relationship between the backend and program files e.g. db_be.mdb to db.mdb

However I have an instance now where one user needs THREE backend files and one program file. This need arises from the fact that I:-
1) don't want three desktop shortcuts
2) want the user using just one program
3) subsequent program file updates will be all the more tricky because of the hard-coded links in the Linked Table Manager

So, what I'd really like is to install db1_be, db2_be, and db3_be into the target folder and have, perhaps, a desktop (.bat?) file routine that enables the user to rename, in the first instance, db1_be.mdb as master_be.mdb (which is the filename used in the Linked Table Manager). When db_2.mdb is required to be used I want to change master_be.mdb back to db1_be.mdb and rename db2_be.mdb as master_be.mdb, etc

Is this possible?

 

[namliam]

Would this be usefull?

 

[DCrake]

My approach to this would be as follows.

For the person who wants to load one front end but in some instances they need to access one of three different backends. I would give them three login names.

Then on the login name validation routine it looks up the default backend to link to. This can be acheived by simply adding a new field to the users table with the name of the default mdb to open.

You might say... well how do I read the users defaults without first logging into a mdb and reading its users table. The answer to this is to create a new common back end that only has the users in it. When the front end launches it gets the login name, password and default back end from this mdb. Validates it and relinks to the default mdb.

This way there is no user intervention required apart from them using the correct login name to open the relevant mdb, and it can be maintained from 1 front end.

 

[PaulO]

David

Very interesting but a little above my mental pay-grade! One question, though, is how would the Linked Table Manager within the Program File know which of the three databases it is to look at?

Thanks!

 

[PaulO]

Namliam

Not sure whether this is exactly what I need ... does that routine incorporate the ability to rename the databases to master_be.mdb?

I think the key to this is the Linked Table Manager and how it behaves and/or how it's controlled?

 

[DCrake]

There is a major concern over renaming back end mdb's. Just think about it, normally all users use the same mdb, with the one exception. Now if this user logs in and you do get it to rename the back end as you describe this will overwrite everyone else's back end. What happens if othe rusers are logged in to it as well? I don't think Windows will allow you to rename an open object. What about the ldb file as well? You bneed to stand back and think strongly about renaming back ends dynamically. I just love being pessamistic, but it's the only way to be these days.

 

[PaulO]

Some excellent points made there David for sure ...

In this instance there will only ever be ONE USER who will only ever want to access ONE backend file during each session so no file sharing or locking issues should arise.

Ideally I want to manage the renaming routine outside of the Program File i.e. from a .bat file on the user's desktop.

The program file has hard-coded links in the Linked Table Manager which is a major constraint with this requirement, and I'm desparately trying to avoid the user having to manually rename the backend files in File Manager.

 

[DCrake]

Why the insistance of using a bat file? What happens if the user logs in on another machine the bat file will not exist, unless you place the bat file on the server (Which is a bad thing to do). Take a look at this Demo I posted a while ago. This should go along way to explain the correct way to go.

 

[PaulO]

There are no networking issues at stake, fortunately. Single user, single Laptop.

I don't know whather a .bat file is the answer or not, the sound you're hearing is that of me clutching at straws in a area way out of my comfort zone! lol

 

[namliam]

I think the key to this is the Linked Table Manager and how it behaves and/or how it's controlled?

This is exactly what the linked code does, remap linked tables to a different location/backend.

It original intend is to link to the same backend in a sub folder depending on where it is opened from, but I am sure you can addapt the code.

 

[DCrake]

The fact that this example uses a network is irrelevant. All systems be they single or multi user should be split. What I can't understand now - based on your last post - is why a single user who is not part of a network would want to toggle between 3 different back ends from one front end. Perhaps if you explained the difference between the backends and reason for attempting to adopt this methodology then perhaps it may become clearer.

For your application to work seamlessly irrespective of which back end it is linked to then the structures would have to be identical. Only the data is different. This leads me to summise that the user wants to look at archived data as apposed to current data. If I am wrong then say so.

 

[namliam]

Ideally I want to manage the renaming routine outside of the Program File i.e. from a .bat file on the user's desktop.

Assuming each user will have his own front end you dont need to rename the backend, just have them be what ever they need to be and use my linked code to relink the tables in the front end.

 

[PaulO]

The fact that this example uses a network is irrelevant. All systems be they single or multi user should be split. What I can't understand now - based on your last post - is why a single user who is not part of a network would want to toggle between 3 different back ends from one front end. Perhaps if you explained the difference between the backends and reason for attempting to adopt this methodology then perhaps it may become clearer.

For your application to work seamlessly irrespective of which back end it is linked to then the structures would have to be identical. Only the data is different. This leads me to summise that the user wants to look at archived data as apposed to current data. If I am wrong then say so.

OK the User is using a 'Members Club' management database but is managing three such 'Clubs'. The three backend files are identical in terms of tables and fields, but the live (not archived) data different i.e. different Members and Activities/Calendar/Accounts, etc. The database as it stands is designed only to manage a Single Club ... for it to handle more than one club would require some significant program re-writes for forms, queries and reports.

I hope this helps ... ?

 

[DCrake]

That explains alot. On your login screen have a drop down combo that says open database. The user then selects which database to open it then goes away and relinks the front end to the relevant back end. The only changes you need to make is in the login screen as per the relink tables routine suggested.

 

[PaulO]

David

My VB code-writing skills are superior only to my 6yo Son (and that's a judgement call!) ... so I'm gonna need some more help, if you can spare some?

I promise though that I'm not a complete numptie and am great with Tables, Queries, Forms, Reports, and Macros (all self-taught like most people) but because I invest heavily in Queries within my programs the subtlety and power of VB has passed me by and I recognise I must do something about that).

I looked at your training database and like the idea of the pop-up form at startup (though it could equally be driven from a standard form), but rather than using it to capture a login with Name & Password I'd prefer just to have a drop-down list with the three backend databases (db1, db2, db3) and for the user to select/ok the database they wish to use in the current session?

Quite how this selection then re-points the Linked Table Manager to the correct database and/or renames the selected database to that contained in the Linked Table Manager is the rocket-science bit that I simply cannot grasp?

 

[namliam]

This is exactly what the linked code does, remap linked tables to a different location/backend.

It original intend is to link to the same backend in a sub folder depending on where it is opened from, but I am sure you can addapt the code.

Feeling totaly left out and ignored

Did you even try looking at the code in my link? It is IMHO pretty self explaining...
FYI
tbl.Connect = ";Database=" & tblDB
tbl.RefreshLink
Is where the linked table is being relinked

 

[DCrake]

Here is a very quick mock up of what you want.

 

[PaulO]

Namliam

... I did look at your code and I greatly appreciate your input as well as David's

However as my last post explains I am very short on VB knowledge and confidence and therefore even the simplest code is a big challenge to me.

I'll take another look at your code right now

 

[PaulO]

David

I like the look of that ... I think I might even understand the code!

Question:-

Select Case Me.Options
Case 1: StrDatabaseToOpen = "C:\Temp\TempA.mdb"
Case 2: StrDatabaseToOpen = "C:\Temp\TempB.mdb"
Case 3: StrDatabaseToOpen = "C:\Temp\TempC.mdb"

Does this command, based on my selection, change/update the hard-coded links in the Linked Table Manager and does the Program file (and all the linked tables) therefore look at the correct Backend database?

 

[DCrake]

Yes it does

Each option in the option group has a default value 1,2 & 3 by ticking a box it resets the tick value This is what is used in the select case statement to determine which database the user has selected to open. The function RelinkTables uses this information to reset the hardcoded links in your database.

You can change the captions on the option group to suit your needs. You will also have to hardcode the path and file names in the select case statement to reflect your environment.