Separate Databases---one for tbls and another for qry,frm, and rpts

[NightWalker]

I am wondering if it is good practice to have separate databases for a database as a whole. one that holds all tables and another or multiple others to hold reports, queries, and forms. those possibly divided depending on the users that need access to the different forms and reports.

I am trying to design a database that will have many different sections for our production facility. There will be a supply inventory section, a test equipment section, a production section that has different focus areas on what they produce, a testing section and the HR (administration) section.

I am wanting to get this designed properly the first time to avoid making too many changes to the structure later.



Any help on this subject would be greatly appreciated.


Thank you for your time and input,

Walker

[orange]

I am not sure of your experience and background, but you have hit on a fundamental issue with MS Access.
The split/linked database -- backend with tables and frontend with queries, forms, reports etc AND a copy of the FE on each user's PC.

for more see
https://www.fmsinc.com/microsoftacce...abaseSplitter/
http://www.fmsinc.com/microsoftacces...dDatabase.html

Good luck.

[NightWalker]

I am a self taught user of about a year. I have made many mistakes with some small databases. trying to go back and fix things on the front end while users and utilizing it is very difficult. I have to go around to each user and have them delete their version and copy the new FE to their PC each time I make changes. I wish there was an easier way to take care of that problem too.

This database I am tasked on creating doesn't have to be released until I have the entire thing debugged as best as I can. Is splitting the BE and FE a better way to go in the beginning?

Thank you for the links I am going to look at them now. The books I have read don't really touch on this subject.

Thanks,
Walker

[orange]

You should be working with a separate copy. Break your development and production/operations into separate environments.
In fact, depending on how large/complex your organization and database is, you might also consider maintenance and archive environments.
You can't be building/modifying a shared, multi-user database while people are working with it. Just having a non-split database with multiple simultaneous users is asking for trouble.

There are samples of code to :
-allow you to update your "current production" front end in a known location
-set up the "standard front end" to compare the user's current front end with the "latest version of the front end". If the user's copy does not match the "latest version of the FE", then copy the "latest version to the user's PC". That way user's always have the latest version and it's automated.

Production/Operations is where real work (production) runs. It uses the "latest version of the front end" and the production version of the backend. The backend is "backed up" on a regular basis, and is readily available if/when corruption or other incident is identified requiring a "refresh/re-establishment" of Production. Some transactions may have to be re-input.
For thoroughness, you might have to be running some "database/audit logging" routines.
Development is where you do new work. You may not have this as multi-user and may only need one database combining FE/BE. You don't have to do it this way. You can have a copy of Production, but placed in your Development area, and use your development FE (the one you are developing/designing) to interact. This is completely separate of the Production system.

If you have a Production system, and you need to make some changes, you can move a copy to Maintenance, do you modifications and testing there. When changes and testing have been completed, you could have it tested by an Acceptance group who will use their own test data; check it for accuracy/expected results; and ensure any/all related documentation has been updated to reflect the changed version. Then, the database is moved from Maintenance and placed into Production/Operation.

You can get more info by Googling database administration and/or information management and the related processes and procedures.

If your database is very important/essential to your business and business decisions, then every effort to have thorough, practical procedures and documentation is key to your company's success/viability.

Not everything is necessarily critical. You and your bosses know where this fits, and should act accordingly.

[NightWalker]

This database I am creating will take place of ALL hand written documentation in the entire company of about 120 people. This project has me stressing because I am the only person who has any knowledge of Access and I just started teaching myself about a year ago. The bosses have a very high expectation of my abilities because I got a single test data results form and report to work. I am trying to figure out how to make such a large database. I feel like I am already drowning and I am just starting. I appreciate all your help already.

[CJ_London]

120 people? all using the system at the same time? There is more to consider than just using Access.

An Access front end on each users machine is fine, but seriously consider using sql server or mySQL rather than access for the backend.

Also make sure your network is well tuned otherwise performance will suffer to the extent that it may become unusable. And if using an Access backend, all users need a wired network connection - if you get an wireless outage/fluctuation you can get corruption in the backend (another reason for using SQL Server as a back end)- means more learning but you'll be a better person for it

You might want to investigate using Azure....

[ssanfu]

A few tips:

Read this thread>> https://www.accessforums.net/showthread.php?t=52954
And print out this PDF for a reference:
Relational Database Theory and the Rules of Normalization
http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf



Start by writing a short "mission statement". (define your problem)
Talk to the users to find out what data they need to save and what their process is.
It doesn't do any good to design a dB that no one will use or is unusable.

Then use pencil and paper or whiteboard or sticky notes or a window or ... (you get the idea - not a computer) to design the tables, set relationships.
Understand Normalization!! It is very important to get the table structures and relationships correct! Validate the design by trying to add data.
If it helps, post your dB/design here for review.

Only AFTER the table design/relationships have been created (and subject to change), should you think about creating forms and reports.


Speaking of design:
Use only letters, numbers or the underscore in object names. NO spaces, punctuation or special characters.
Do not use multi-value-fields (MVF)
Do not use look up fields (different from look up tables).
See http://access.mvps.org/access/tencommandments.htm
and http://access.mvps.org/access/lookupfields.htm

And speaking of primary keys:
(Personally, I use an Autonumber PK field in every table.)

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm


Be aware of reserved words.... see http://www.allenbrowne.com/AppIssueBadWord.html


------------
With 120 people, I totally agree with Ajax that you should not use Access for the BE. Access is good up to about 10 people,then it gets dicey.
You could start by using SQL Server Express (free) to begin with, then move up to MySQL or SQL Server Standard if necessary.

[NightWalker]

They wont really be using it at the same time. My company doesn't allow wireless anything. They also are not allowed to use anything other than approved software. Currently Access is the only thing I have to work with. In this case should I make multiple BE for each section and only link the tables that are necessary to be linked.

[NightWalker]

Steve, Why do you not use lookup fields? Are you referring to Value lists?

Also thank you for the links I am learning a lot already.

This whole drawing it out on paper has me stuck but I will work it out then post what I have for review.


Thank you all for your help.

[ssanfu]

Why do you not use lookup fields? Are you referring to Value lists?

1) The major thing is look up FIELDS hide what is really going on. See
The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm

2) The only time look up fields could be used is if you are entering data into a table or in datasheet view of a form.
Unless a dB for only me and I am doing a "quickie" dB, all data entry is through forms. So creating a look up field in a table is a waste of time. You have to re-create it again as a combo box on the form.

I don't use datasheet view forms; I use a continuous view form and arrange the controls to look look a datasheet view. Granted, it is more work, but easier to control the "look and feel" of the form. In datasheet view the look up fields "might" be a little quicker in entering data into a table.... but I have never used a look up field - too many down sides (IMO).

I am now converting an Access BE to SQL Server Express. It has been pretty painless because there were no look up fields, no Multi Value Fields (MFV) and no calculated fields.

3) Value lists are only useful if the options are few and very stable. Things like "No/Yes", "Male/Female", "Open/Closed" I might think about using a value list. Anything more that a couple of options, I use a table.


These are my "conventions" ...... you may do things differently.