469,286 Members | 2,521 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

Dynamic Table Linking

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

Expand|Select|Wrap|Line Numbers
  1. SELECT Customer_ID, Sales_Rep_ID
  2. 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.
Jan 9 '07 #1
10 9081
NeoPa
32,173 Expert Mod 16PB
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.
Jan 9 '07 #2
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.
Jan 9 '07 #3
NeoPa
32,173 Expert Mod 16PB
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.
Jan 9 '07 #4
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 :)
Jan 9 '07 #5
ADezii
8,800 Expert 8TB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT Customer_ID, Sales_Rep_ID
  2. 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.

Expand|Select|Wrap|Line Numbers
  1. Dim tbf As TableDef
  2.  
  3. If CurrentUser() = "Admin" Then    ' Can use Select Case for other Users
  4.   For Each tbf In CurrentDb.TableDefs
  5.     If Left(tbf.Name, 4) <> "MSys" Then   'Don't DELETE System Tables
  6.       If tbf.Name = "tblEmployee" Then    'You'll probably be Deleting all Links
  7.         CurrentDb.TableDefs.Delete tbf.Name    'Critical step
  8.             DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\Haz- _
  9.             Mat\HMAU_Data.mdb", acTable, "tblEmployee", "tblEmployee", _   
  10.             False, False
  11.       End If
  12.     End If
  13.   Next
  14. End If
Jan 10 '07 #6
NeoPa
32,173 Expert Mod 16PB
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).
Jan 10 '07 #7
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).
Jan 10 '07 #8
ADezii
8,800 Expert 8TB
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...
Jan 10 '07 #9
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.
Jan 11 '07 #10
NeoPa
32,173 Expert Mod 16PB
No problem and Good Luck :)
Jan 11 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jeff Hagelberg | last post: by
reply views Thread by Dibyendu Roy | last post: by
reply views Thread by Pat Sagaser via .NET 247 | last post: by
11 posts views Thread by Sean M. DonCarlos | last post: by
1 post views Thread by zpinhead | last post: by
7 posts views Thread by Ajinkya | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.