Linking multiple tables by primary key...

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?

Who is Participating?

Improve company productivity with a Business Account.Sign Up

GOLLEMConnect With a Mentor Commented:
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...


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


What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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.
davemillhouseAuthor Commented:
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.

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


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.
davemillhouseAuthor Commented:
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.

davemillhouseAuthor Commented:
Not quite solved my prob, but thanks for all your help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.