Go Premium for a chance to win a PS4. Enter to Win


Linking multiple tables by primary key...

Posted on 2000-04-20
Medium Priority
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?

Question by:davemillhouse
  • 3
  • 3
  • 2
  • +1

Expert Comment

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.

Expert Comment

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


LVL 54

Expert Comment

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.
Technology Partners: 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!


Author Comment

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.

Expert Comment

ID: 2734243

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....


LVL 54

Expert Comment

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.

Author Comment

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.


Accepted Solution

GOLLEM earned 102 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...



Author Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

926 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