Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Access Web App: Primary & Foreign Key, Fetch Values?

Posted on 2016-10-03
Medium Priority
Last Modified: 2016-10-06

I have a doubt related to Access Web App

I have 2 Tables:

Table1: TA1ID (pk) [AutoNumber],                            A, B, C, D               [Related Table]
Table2: TA2ID (pk) [AutoNumber], TA1ID (FK),     E, F, G, H, I, J, K, L  [Main Table]

My questions:

1. I want Values from A Table1 to E Table2. where should i have the foreign key Table1 or Table2 ?

how should i have it?

  TA1ID in Table2 as Foreign key
  TA2ID in Table1 as Foreign Key

What data type to be used?

      Example:      If i enter 12345 in A [Table1],
                          it should automatically be added to E [Table2] (Not as Dropdown list)
                          If i update 12345 to 134 in A [Table1]
                          it should automatically be Update to E [Table2] (Not as Dropdown list)

                                If i delete Record 134 in A [Table1]
                                it should automatically delete in E [Table2]also.

2. I want A values to be Fetched into E, B values into F, automatically (update, delete should be easily done)

      Note: Not in Dropdown list (if i use lookup i will be able to get values but we have to type manually and select)

3. What data type to be used in both tables for A & B where values is entered in Table1 and fetched into Table2.

4. How do we create a Referential Integrity as we do create in Access Desktop?

5. When i create a lookup in access desktop it shows option to create Referential Integrity & Update & Delete Records. when i do the same in Access Desktop it doesn't happen that way it just shows a Dialogue box to lookup values from but no Referential integrity to update or delete records from related tables.

Question by:Satish Kumar G N
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
  • 5
  • 4
  • 2
  • +1
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41825937

For 1. Main table will have Primary Key. Related table will have Foreign Key. Data Type of both the fields should be same. Normally it should be Integer.

Now if you insert something in the main table then you have insert the data in the related table also. It is part of referential integrity.

You have to enable the Delete and update cascade if you want delete and update feature.

For 2.  For this you have to write a Query to handle this scenario.

For 3. Seems like you are storing strings so use Varchar data types.

For 4. Once you create primary and foriegn key constraint in the access DB the database engine will automatically takes care of this.

For 5. If you sent all above it will be automatically be taken care by the system.

Enjoy! Let me know in case any issues.

Author Comment

by:Satish Kumar G N
ID: 41825946
Hi pawan,
thanks for your reply, your answers are correct but for the Access Desktop. In access Webapp these don't work. There is no option to enforce referential integrity , update and delete cascade when you select lookup in AccessWeb.
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41825953
Ok so is there any option of custom coding ?
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

by:Satish Kumar G N
ID: 41825996
not in access webapp, that's what I am trying to figure out. So I posted the question to seek answers for the same.
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41826034
Okies, Actually I got confused with MS Access DB. !!
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41826036
Can't you take data from existing connection like SQL SERVER ?
LVL 85
ID: 41826077
You create "relationships" in an Access Web App as described in this MSFT article:


That page should also answer your other questions.

Also, I'd suggest you take a little time to ensure the WebApp is going to provide you with the right environment. As of now, the Access WebApp is not the most robust platform, and while that may change right now, if you need anything more than a very basic CRUD application you're probably not going to be satisfied with an Access WebApp.

Author Comment

by:Satish Kumar G N
ID: 41826112
Hi scott,
thanks for your reply, but that doesn't answer my question fully, only till 6th point in Web Access section is clear, post that its unclear , not properly understandable for basic level people.

LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 41826140
That's the way "relationships" are managed in Access WebApps, so I'm not sure how much more information we can provide. As mentioned earlier, WebApps are not really ready for prime time, so there are some shortfalls you cannot get around.

Pawan answered your questions regarding how to structure your tables (i.e. the "child" table should store the Primary Key value of the "parent" table), as well as your #2 and #3 questions. The answer to you #4 question is "you can't do that in a WebApp", and the article I provided you explains why (and how to work around it, as much as possible). Your #5 question would be moot, since what you're asking doesn't really have any correlation to the WebApp world (you'd use the article I linked to provide that sort of functionality).

If you're not able to implement these features in your WebApp, I'd suggest you review some of the basic tutorials regarding WebApps. Microsoft has quite a few of them, and there are other sites around the internet that provide them as well (some are free, others have a cost).

Exactly what are you having troubles with, after following the steps outlined in that article?
LVL 22

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 1600 total points
ID: 41827459
web apps relationships can have referential integrity -- it will only be available, however, if the fields are the same data type (foreign key is Whole Number) and the foreign key is linking to the primary key (AutoNumber) in the main table (or perhaps a field with a unique index)

You cannot put the same text fields in both tables and expect them to be able to get updated without any kind of valid link (and if you have a valid link, you don't need to repeat them -- this is not proper either).  Keep those text fields in the main table and NOT in the related table.  The related table should just have a whole number foreign key (FK) with a relationship to the primary key (PK) in the main table.  Once the FK gets a value, everything on the record in the main table with the corresponding value for PK is known too.


Main table:

MainID = Autonumber PK = 2
ProjectName = Learn Access
ProjectManager = Satish

Related table:

RelatedID = Autonumber PK = 15
MainID = Whole Number = 2 --> Foreign Key related to main table PK with referential integrity
more fields ... but NOT ProjectName or ProjectManager  since those are in the main table

Author Comment

by:Satish Kumar G N
ID: 41829195
Hi crystal, thanks for reply, I have mentioned in detail as what I am trying to do in question Access Webapp: Table to Query to Table
there in comments I have mentioned what I am looking for, could u please use my data and answer as how to do it and I am replying for all abandoned questions as pointed out by you thanks , I didn't knew this part of EE.


Author Closing Comment

by:Satish Kumar G N
ID: 41831492
Thank you crystal and scott for constantly helping me out.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

604 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