Solved

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

Posted on 2016-10-03
12
67 Views
Last Modified: 2016-10-06
Hello,

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.


Thanks.
0
Comment
Question by:Satish Kumar G N
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41825937
Answers..

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.
0
 
LVL 1

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.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41825953
Ok so is there any option of custom coding ?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 1

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.
0
 
LVL 28

Expert Comment

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

Expert Comment

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

https://support.office.com/en-us/article/Create-a-relationship-40c998dd-4875-4da4-98c8-8ac8f109b85b?ui=en-US&rs=en-US&ad=US&fromAR=1#__create_a_relationship_webapp

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.
0
 
LVL 1

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.

Thanks
0
 
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?
0
 
LVL 19

Accepted Solution

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

example:

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
0
 
LVL 1

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.

thanks
0
 
LVL 1

Author Closing Comment

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

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

831 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