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

Posted on 2016-10-03
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
  • 5
  • 4
  • 2
  • +1
LVL 24

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 24

Expert Comment

by:Pawan Kumar
ID: 41825953
Ok so is there any option of custom coding ?

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 24

Expert Comment

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

Expert Comment

by:Pawan Kumar
ID: 41826036
Can't you take data from existing connection like SQL SERVER ?
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

LVL 84
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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 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 19

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 400 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 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