473,390 Members | 1,406 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

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 9468
NeoPa
32,556 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,556 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,834 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,556 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,834 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,556 Expert Mod 16PB
No problem and Good Luck :)
Jan 11 '07 #11

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

Similar topics

1
by: Jeff Hagelberg | last post by:
I'm trying to create a python module which can be used by a python interpreter embedded inside a fortran program I have. To do this, I first created python wrappers for all the functions in my...
0
by: Dibyendu Roy | last post by:
Hi All, I build an object called "dblorcle" to connect to oracle database in Sun solaris box. This is built linking with various oracle ".a" (archived, for static linking) files come with standard...
2
by: Wilder | last post by:
I'm trying to update a field in one table with the minimum values of the field in another table. The two tables are linked via a common field. I want to populate a date field in one table with...
3
by: K.S.Liang | last post by:
Hi all, 1> If there are more than one dynamic linking libraries in the file system, how do I know which one is loaded into system? Any C library or system call can tell me which *.so or *.sl is...
0
by: Pat Sagaser via .NET 247 | last post by:
I'm using a repeater with a dynamic template. I don't know the fields to display (or how many) until runtime. I have everything working except for linking Button events to the repeaters ItemCommand...
11
by: Sean M. DonCarlos | last post by:
I have an unmanaged Win32 app that looks up the name of a DLL (unknown at compile time) from an external location, loads it with LoadLibrary, and then uses GetProcAddress on three exported...
1
by: zpinhead | last post by:
I am unable to get my downloaded extension from pecl to link up with php properly. seems like the php.so I could not use pear install http. pear claimed the extension was already installed....
1
by: srikar | last post by:
what is the difference between static linking & dynamic linking, what are the advantages of each? How to perform static linking & Dynamic linking by using gcc -o liniking will be done , but...
7
by: Ajinkya | last post by:
I have writen a program for a game called game.exe Now it includes a player part to which has to be a function to be writen by someone else. Now I want to provide this exe to some tester who...
2
by: englishman69 | last post by:
Hello, I have been banging my head against this one for a while... Searches online have revealed many different proposals for correcting my issue but none that I can follow! My basic situation...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.