Solved

Linking multiple tables by primary key...

Posted on 2000-04-20
9
278 Views
Last Modified: 2012-06-27
Hello. Can anyone help?

When I try and link more than 3 tables all by their primary key, access won't cascade update/delete the related fields in all of the tables.

Basically we're storing a truck-load of data on people and it won't fit into only 1 or 2 tab's. So I want to link them all by the same field (numbers) in each table using 1-1 relationships and referential integrity. But it won't update all the tables when I add a new record.

Anybody now why, or more importantly, how to make it work?

Cheers  
0
Comment
Question by:davemillhouse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 3

Expert Comment

by:PunisherDude
ID: 2734009
Well it's not going to automatically add new records to the other tables.  The update/delete options are for when you change to data on one side, and what is link carries that same change.  And the delete is when one record gets deleted the linked does too.

Are you using forms to enter your data? You can setup some code that will create a new record in the linked table when you add a record in the main table.

I hope I helped.
0
 

Expert Comment

by:GOLLEM
ID: 2734013
Hi Dave,

here's a simple solution:

use three subforms
have their borderstyle set to none or transparent
when you post your data you just save all three tables at once

regards,

       Michiel
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2734094
Tried it with one main table and five cascade-delete relations to five different "subtables" and had no problem.

So when you create first a "main-index" table and relate all others to thisone, it should work.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 1

Author Comment

by:davemillhouse
ID: 2734132
I'm still stuck I'm afraid.  Feeling a little dense here.

Perhaps my understanding of relationships is incorrect.  I was under the impression that a one-to-one relationship  meant that whenever you add a new record to a table, a record with the same primary key value must be added to the other linked tables.  Furthermore I thought access would do this for you automatically.

But when I create a one-to-one relationship, I can still add records to Table A, with there being no records at all in Table B.  I want to have a new record added to every table it is linked to.

I am using either a table or query for data entry - as we are pasting data from another database located elsewhere.  The subform theory didn't work - although perhaps that is the way forward - I tried to add a record and it would only let me in the "main index" subform and not the others.

I can't use code in a form because of how we are inputting data - it would take forever to do it manually.

I think I am losing my mind as well.
0
 

Expert Comment

by:GOLLEM
ID: 2734243
dave->

If you're pasting data from another DB, then why don't you use UPDATE-queries???

1 to 1 means that there is zero or one related records in the related table.

Are you using copy and paste to input data???
Can I assume that you're pasting into a datasheet type form then???

If the datasource property is correctly filled in for your form and all table fields have a control for input which is linked correctly, then there should be no problem....

regards,

         Michiel
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2734344
You get a lot of problems, just because you are trying to synchronise three (or more) tables that essentially should be one table!

Doing it correct would be making that problematic giant table. You did split the table on the "columns", but as easily you can split it on rows!

The giant number of fields/columns isn't bothering you when you define a query to show only those necessary. (In your case one view per "old-table")

Just make a simple import query that is selecting a range of primary key's that will fit and process the rows.

Another appraoch could be to create multiple .mdb's all with a "range of primary key's" and link those tables from the main .mdb you're working on. Thus you can specify queries over all data by the creation of a UNION query.
 
0
 
LVL 1

Author Comment

by:davemillhouse
ID: 2734351
Basically we're updating from an SQL database which is stored elsewhere in the world.  Yes, we are doing the old cut and paste thang.

All my efforts at update queries have failed because we're trying to update to multiple tables from a single table. We're having to use multiple tables because we reached the limit on how much memory Access could store in any one table.

I have now officially gone bonkers.

To recap on the problem - if I add a record to one table, but not to another table, this'll mcuk up all our processe from their on in, becuase different people use different tables for different things.  SO basically I need an easy way to add a batch of records to lots of tables at the same time.

Perhaps I could cut and paste from my SQL database into one Access table, then run an update query to transfer info to the other tables?  No - that wouldn't work because I can't fit all the info into one table can I.

Arrrrrrrrrgggggggghhhhhhhhhhh.
0
 

Accepted Solution

by:
GOLLEM earned 34 total points
ID: 2734637
hmmmm... interesting problem :)

I know access has it's limits. Maybe you should think about using a real database program like Oracle or DBase to serve as a backend for MS-Access.

Can't you split the table at the source?

Maybe you could try something like this:
Make as many copies of your SQL database as you have different tables.
Modify each of your copies so only the fields and records that you want to import are remaining.
(so copy #1 corresponds with table #1, copy #2 with table #2, etcetera...)
Then you can copy&paste one table at a time.
I'm sure this can also be done automatically if you use code.

Frankly I'm at a loss here too. I'm not an expert yet... :)
Maybe one of the regulars her at EE will be able to help you...

Regards,

            Michiel
0
 
LVL 1

Author Comment

by:davemillhouse
ID: 2750925
Not quite solved my prob, but thanks for all your help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question