Creating a great FE/BE from existing monster database

[marinelizard]

Hi Access Nation,



Hoping you can help guide me. I inherited a monster database accessed/modified by several users on a shared network; naturally the db constantly corrupts. I want to split the db into front end (FE) and back end (BE) databases to minimize corruption, increase speed over the network, and be able to fix bugs without worrying about the data via updates to the FE. I've also been asked to add/make changes to this new database. They include:

1. A login that assigns permissions (I guess that means access to forms in the FE)
2. A way for users to create metadata on the data they add via forms
   
   • We have a template form database; I've already imported a template form/subforms with related tables
   
   
3. New tables that would link to the primary key to another existing table (SiteID)
   
   • Q: Should I create these tables before splitting?
   
   
4. Create new relationships to allow for multi-year entries at the same sites
   
   • SiteID is currently primary key for most tables, but sites are now to be sampled multiple years-advice on keys in this situation?
   
   
5. Establish standardized queries so everyone uses the same ones for quality assurance purposes (is this part of the permissions?)

I've never done any of this before. Is splitting the right approach? Is there a specific order I should/need to do these operations?

I realize that I'm asking questions for about 12 topics here, but would love advice or a point in the right direction.

Thank you for any help you can provide!

[June7]

1. common topic in forum start with https://www.accessforums.net/access/...gin-23585.html

2. not sure what this means - more fields in table?

3. before or after, doesn't really matter

4. don't know if new keys are needed - maybe already accommodates assuming entries have a date/time field - would need to know more about data and relationships

5. users should not be working directly with tables and queries, just forms and reports, and there should be 1 developer

Multi-user db should be split. Also a common topic start with https://www.accessforums.net/access/...ers-48543.html

[ItsMe]

I would start here ...
https://www.youtube.com/playlist?lis...XClzNCMynac38g

Videos #6 and #7 are more focused on SQL server and #8 talks about big data.