Linking multiple tables by primary key...

Posted on 2000-04-20
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.

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.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


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



Author Comment

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now