Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

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

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
Satish Kumar G N
Asked:
Satish Kumar G N
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
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
 
Satish Kumar G NStudent Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Ok so is there any option of custom coding ?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Satish Kumar G NStudent Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Okies, Actually I got confused with MS Access DB. !!
0
 
Pawan KumarDatabase ExpertCommented:
Can't you take data from existing connection like SQL SERVER ?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Satish Kumar G NStudent Author Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Satish Kumar G NStudent Author Commented:
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
 
Satish Kumar G NStudent Author Commented:
Thank you crystal and scott for constantly helping me out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now