Dynamic Table Linking

[JacobWalker]

Hello all,

My problem is about changing which Back End my Front End is linked to depending on which user logs in. I'm using Access 2000 and Windows 98, however different users will be using different versions of Windows.

I understand that I can use the IN function in VBA such as

SELECT Customer_ID, Sales_Rep_ID
FROM m_customer_orders IN 'p:\database\shoe_orders_data.mdb'; 

and have a Variable for the specific name of the database, but I would rather not, if it can be avoided.

Any help is appreciated.

[NeoPa]

Are you asking how, dynamically, to relink linked tables when a database is opened?
I think this would be very difficult, prone to errors and not be multi-user compatible.
I know of no way to do this either btw.

[JacobWalker]

Are you asking how, dynamically, to relink linked tables when a database is opened?
I think this would be very difficult, prone to errors and not be multi-user compatible.
I know of no way to do this either btw.

Yes, that's what I'm asking. We have four shifts with seperate data and would like to avoid putting all data into one back end. We were hoping one front end would be able to reference different back ends based on the user.

Since my post I've been thinking about it more and what you said about multi-user incompatibilty crossed my mind.

I'm looking into the TransferDatabase function in VB that should relink the tables. I'll post if I get anything to work.

[NeoPa]

I do have a database that I developed which has two different back ends.
I handle it by having linked tables to both of them and renaming the appropriate set into place on detection of which that is.
To avoid the multi-user issue I have have two Front-end databases stored, one for each option. This may seem like a waste of time, but it means that whenever I have to release a new version I can release the same database to both in the knowledge that one will redirect itself automatically to the correct back-end.
Might this be a concept worth following.
If so, I'll try to remember tomorrow at work to dig out some code for you.

[JacobWalker]

I do have a database that I developed which has two different back ends.
I handle it by having linked tables to both of them and renaming the appropriate set into place on detection of which that is.
To avoid the multi-user issue I have have two Front-end databases stored, one for each option. This may seem like a waste of time, but it means that whenever I have to release a new version I can release the same database to both in the knowledge that one will redirect itself automatically to the correct back-end.
Might this be a concept worth following.
If so, I'll try to remember tomorrow at work to dig out some code for you.

Your idea of renaming tables sounds cool, but as you pointed out wouldn't work well with multiple users.

It's looking like multiple front ends is going to be the only viable option. Users will be logging in to a seperate database that's already in place for other functions and at that time will be redirected to their appropriate front end.

Thanks for your help :)

[ADezii]

Hello all,

My problem is about changing which Back End my Front End is linked to depending on which user logs in. I'm using Access 2000 and Windows 98, however different users will be using different versions of Windows.

I understand that I can use the IN function in VBA such as

SELECT Customer_ID, Sales_Rep_ID
FROM m_customer_orders IN 'p:\database\shoe_orders_data.mdb'; 

and have a Variable for the specific name of the database, but I would rather not, if it can be avoided.

Any help is appreciated.

As stated by NeoPa, dynamically Linking Tables is playing with a loaded gun, but if you would like to try it out, here is some demo code that should point you in the right direction. It will:
1) Check the Current User Status.
2) If the Current User is Admin, the code will loop through all the Tables in the Database filtering out the System Tables.
3) If a specific Table is found (tblEmployee), it will Delete it. Since it is a Linked Table, only the Link itself is Deleted
4) It will Re-Link tblEmployee from the shown Path and maintain its same structure, name, and data.

NOTE: The code has been kept as simple as possible and should be easily adaptable to suit your needs. Hope this helped.

Dim tbf As TableDef
 
If CurrentUser() = "Admin" Then    ' Can use Select Case for other Users
  For Each tbf In CurrentDb.TableDefs
    If Left(tbf.Name, 4) <> "MSys" Then   'Don't DELETE System Tables
      If tbf.Name = "tblEmployee" Then    'You'll probably be Deleting all Links
        CurrentDb.TableDefs.Delete tbf.Name    'Critical step
            DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\Haz- _
            Mat\HMAU_Data.mdb", acTable, "tblEmployee", "tblEmployee", _   
            False, False
      End If
    End If
  Next
End If

[NeoPa]

ADezii,
A more reliable way of finding linked tables is to check the .Connect property of the TableDef object. If this is an empty string (Not Null but .Connect="") then the table is a local one.
Another point worth bearing in mind (sorry to pick holes) is that you can't split a line within a text literal (Your .Mdb filename).

[George Oro]

I normally put this option to my program to be able the user to select which backend to use or create a complete blank database. Unfortunately I cannot send you the code because it is to complex due to some of my own requirements. I got the idea on the sample I found in www.rogersaccesslibrary.com

hth,
George

ADezii,
A more reliable way of finding linked tables is to check the .Connect property of the TableDef object. If this is an empty string (Not Null but .Connect="") then the table is a local one.
Another point worth bearing in mind (sorry to pick holes) is that you can't split a line within a text literal (Your .Mdb filename).

[ADezii]

ADezii,
A more reliable way of finding linked tables is to check the .Connect property of the TableDef object. If this is an empty string (Not Null but .Connect="") then the table is a local one.
Another point worth bearing in mind (sorry to pick holes) is that you can't split a line within a text literal (Your .Mdb filename).

I was assuming that all Tables contained within the Database were in fact Linked, and that no Tables were Local. Thanks for the pointer on the split, I did not want to place the code on a single line and my Syntax Checker was not working last evening. Always a pleasure...

[JacobWalker]

Thanks for all the help, guys. Your solutions to redefining links work well, but I've discovered that isn't what my situation needs. As NeoPa mentioned earlier, redefining links would cause problems for multiple users. I'm working on an alternative to open a new front end based on log on which I've started discussing in another thread.

Thanks again for your input.

[NeoPa]

No problem and Good Luck :)