Solved

Linking multiple tables by primary key...

Posted on 2000-04-20
9
251 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views 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 Access…
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…

772 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