If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I set relationships for local tables w/ a linked table?
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... |
#2
|
|||
|
|||
How do I set relationships for local tables w/ a linked table?
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" wrote: 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... |
#3
|
|||
|
|||
How do I set relationships for local tables w/ a linked table?
"STP" wrote in message
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... 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) |
#4
|
|||
|
|||
How do I set relationships for local tables w/ a linked table?
"Dirk Goldgar" wrote: "STP" wrote in message 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... 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) |
#5
|
|||
|
|||
How do I set relationships for local tables w/ a linked table?
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. "Dirk Goldgar" wrote: "STP" wrote in message 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... 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) |
#6
|
|||
|
|||
How do I set relationships for local tables w/ a linked table?
"Glen, IT Logistics, RPM corp." Glen, IT Logistics, RPM wrote in message ... "Dirk Goldgar" wrote: "STP" wrote in message 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... 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) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Unable to edit records in Query due to Relations with Linked .txt | Jens | Running & Setting Up Queries | 6 | February 15th, 2006 04:52 PM |
Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
Normalizing | Brandie | New Users | 8 | July 2nd, 2005 01:13 AM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |