Mar 28 2023 10:04 AM
Mar 28 2023 10:04 AM
I have an MS Access 2016 database storing its back end on a SharePoint online site for one or our teams.
Another team within our agency wants to use the same database for their site. I must upload the same database skeleton and lookup tables and their information to the new site.
The development team leader (cannot be contradicted) insists that both sites' have the same primary keys for all lookup items.
Whenever I upload a table from a backend in Access, SharePoint resets the primary keys starting with the number one and continuing sequentially until finished. If any changes were made since the tables were uploaded, when those tables are localized and then uploaded to a new site, SharePoint changes the primary keys to remove the gaps in numbers that exist because of deleted entries or changes.
Is there a way to bypass this issue? Do I need to create a redundant primary key field that will not be changed when the table is uploaded to the new site? Creating a redundant key means that all the tables on the old site will need to be updated to include this field, and the entire front end (queries and forms) will need to be revised for both sites to use the same interface.
Please tell me there is an easier way to ensure the primary keys are the same.
Mar 28 2023 12:17 PM - edited Mar 28 2023 12:18 PM
"Is there a way to bypass this issue?"
Not that I know of, not without custom hoops and special jumping shoes. If you really want to jump through hoops to satisfy that team leader, you might be able to figure out a complicated plan, but those special jumping shoes can be expensive and hard to maintain. I doubt it's worthwhile.
Here's why. Primary Keys are NOT, NOT, NOT for public consumption. The Team Leader insisting on matching them up across two different applications is simply asking for more than is reasonable.
You could add that additional field, not a Primary Key per se, but one with a Unique Index on it. You could use it to coordinate your two applications.
Users should not need to see, or even be aware of, the existence of Primary Keys in a table. They enforce uniqueness on the values in a table and they provide a way to reinforce referential integrity between related tables. Hide them from this Team Leader and let them see your surrogate, Uniquely Indexed, Matching field.
Mar 28 2023 12:20 PM
Mar 29 2023 04:53 AM
Mar 29 2023 05:16 AM
Mar 29 2023 05:20 AM