How do I set relationships for local tables w/ a linked table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2000, and I have created a link to a table from another
database. I'm trying to define relationships with the linked table so that
when the info in the linked tables is updated, my info in my related tables
is updated as well. Is there a particular join type I need to specify, or do
I need to set-up queries that will somehow relate the linked table to my
tables? Thanks for your help...
 
Any change to data in the linked table will be automatically reflected in the
source table.
--
***************************
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?
 
STP said:
I am using Access 2000, and I have created a link to a table from
another database. I'm trying to define relationships with the linked
table so that when the info in the linked tables is updated, my info
in my related tables is updated as well. Is there a particular join
type I need to specify, or do I need to set-up queries that will
somehow relate the linked table to my tables? Thanks for your help...
Click to expand...

You can't create an enforced relationship with a table in another
database, and that means you can't get Cascading Updates. That means
that anything of that sort you want to implement, you'll have to do with
your own code. If these linked tables are only updated from within your
current database, then you can probably do it (with some pain), so long
as you only allow updates via your forms. But if the linked tables can
also be updated by their local database, or by other databases, then
this is going to be a maintenance nightmare, as the same sort of logic
will have to be implemented wherever the tables might be updated.
 
Dirk Goldgar said:
You can't create an enforced relationship with a table in another
database, and that means you can't get Cascading Updates. That means
that anything of that sort you want to implement, you'll have to do with
your own code. If these linked tables are only updated from within your
current database, then you can probably do it (with some pain), so long
as you only allow updates via your forms. But if the linked tables can
also be updated by their local database, or by other databases, then
this is going to be a maintenance nightmare, as the same sort of logic
will have to be implemented wherever the tables might be updated.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
I had the same issue, and solved the problem:
I had to update/append new data to remote databases so all were the same.
My Solution:
I make a database tool to do this.

I make a database and put a copy of the local table tblShippers into it with
the data.
I make a copy of this table called tblShipper_temp without any data.

Next the tricky part, get this part and you will have this licked:

Go to the Access Menu Insert a table and Add a Linked table. (Linked Table
is the Key here)
Tell it what remote table (in a remote database) You want this New table to
link to, (like \\serverIP-or-Name or path\path\dbname.mdb), (this db includes
a tblShippers)

Then tell it what Local table (tblShippers) you want to make a copy of in My
Tool database, and give the new table a name called
tblShippers_linkedtosomewhere.
Name this new Copy of the local table, but is now linked, a similar name but
include where the linked table is linked to, thus the "_linkedtosomewhere"
part.
This is just so I can just keep track of the different tables and where the
heck they are linked to. It is hard at times keeping track of linked tables
going to several remote production locations and development and testing
locations.

So Now This is what you should have, example:
A database with 3 tables,
A local tblShippers with all the correct data,
A Local tblShippers_temp Table with No data,
And a Linked table that has all the data that the original tblShippers has,
but is linked to my remote database table. Called tblShippers_RemoteDB.

Next make a series of querys.
qry100 thru qry101 thru qry102 etc. etc.
qry100 appends tblShippers_temp to my local (unlinked table) tblShippers.
this is so I have a local tblShippers table with up todate data.
qry101 appends tblShippers_temp to the table tblShippers_RemoteDB.
this will append data to the tblShippers table in the Remote database
because It is set it up to be linked to the remote table.

To not get fancy with forms, command buttons and a little back end
programming, just do it the simple way to update all tables:
1. Open up blank local table tblShippers_temp. Add one record to add a new
shipper.
2. Run qry100. this will append the record from tblShippers_temp to your
local(unlinked) table tblShippers. So you always have updated data.
2. Run qry101. this will append the record from tblShippers_temp to the
remote table tblShippers_RemoteDB, the linked table you created to the remote
database.

Why I do it this way, it saves time and I like to create tools to make it
easy to do these things. A tool I can give to some other people and they can
make additions if there is a need and I can't do it.

But I make it easier for them:
I create a form that has subforms to pull up the tblShippers_temp table and
empties it. They enter the data they want to add to this subform and then
click a button that runs A macro. The macro runs the querys qry100 thu
qry101whatever depending on how many remote tables need to be updated.

Remote linked tables over VPNs and networks take a while to update/append.
So I include a message box or status message, after the AddButton is clicked,
for the user, to show the user that everything is working and when it is
done.
Time for my table additions to 8 different remote locations run about 1
minute or so on slow days.
 
Back
Top