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.
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.
ASKER
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.
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.
Ok so is there any option of custom coding ?
ASKER
not in access webapp, that's what I am trying to figure out. So I posted the question to seek answers for the same.
Okies, Actually I got confused with MS Access DB. !!
Can't you take data from existing connection like SQL SERVER ?
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.
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.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Thank you crystal and scott for constantly helping me out.
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.