UNIQUE ID for the whole Database

[challasateeshkumar]

Hi, I have a Access 2000 mdb which has 4 tables, These 4 tables are mapped to front end subforms. whenever a new record is created a unique id is generated using the Max ID+1 logic for that table.
Keeping this structure intact as 4 levels is there a way I can create a unique id for the whole Database so that each record can be identified.

I came across GUID datatype can I use it at every level to uniquely identify records at the overall Database level?

Please suggest If there are any other options?

[Rabbit]

Hi, I have a Access 2000 mdb which has 4 tables, These 4 tables are mapped to front end subforms. whenever a new record is created a unique id is generated using the Max ID+1 logic for that table.
Keeping this structure intact as 4 levels is there a way I can create a unique id for the whole Database so that each record can be identified.

I came across GUID datatype can I use it at every level to uniquely identify records at the overall Database level?

Please suggest If there are any other options?

Why would you need to do this?

[MMcCarthy]

Check out this tutorial.

http://www.thescripts.com/forum/thread585228.html

[challasateeshkumar]

Why would you need to do this?

Hi ,
The information source of the 4 tables comes from different feedfiles.Each table is a subtable for the other. Example table1 is the main and table 2 is related to table 1. Table3 to table 2 and so on. However each item in the table is unique . This relationship is shown as a mainform subform in the frontend.

We want to retain this relationship and structure as changing it has a huge impact. The problem is not with the structure but the logic in generating a unique ID across the 4 tables.

[challasateeshkumar]

Check out this tutorial.

http://www.thescripts.com/forum/thread585228.html

Thanks for the link but we want to keep the structure denormalised and yet have a unique ID across all the 4 tables as the information source of the tables comes from different feedfiles.Each table is a subtable for the other. Example table1 is the main and table 2 is related to table 1. Table3 to table 2 and so on. However each item in the table is unique . This relationship is shown as a mainform subform in the frontend.

We want to retain this relationship and structure as changing it has a huge impact. The problem is not with the structure but the logic in generating a unique ID across the 4 tables.

I hope I have explained it properly. Any help is appreciated.

[MMcCarthy]

Sorry if I'm not understanding you properly but for a mainform subform relationship to work. You would need normalised data.

For example Table1 could have a unique ID called Id1 and Table2 could have a unique ID called Id2 and a foreign key reference to Table1's uniqueID. Table3 could have a unique ID called Id3 and a foreign key reference to Table2's uniqueID, and so on.

You can now call any record in Table 1 and the related records from all the other tables.