08-04-11, 22:10 #1Registered User
- Join Date
- Aug 2011
Unanswered: Access appropriate for this application?
I have basic knowledge of databases and basic knowledge of MS Access. I am not a database professional by any means.
I have a specific application in mind for a database. It will initially have a total of 7 users. These 7 users may all be querying the DB concurrently. One of those users will be an administrator, and one of them will responsible for data entry.
Therefore, there will be 2 users capable of editing data and 7 users capable of querying data.
The database can be expected to hold several thousand records in a primary table its first year of operation, and the number of new records added will increase year on year. Secondary tables will not hold more than several hundred records, at least in the first year.
It is my humble and uninformed understanding that an MS Access database would be an appopriate DBMS to use in this situation.
I would very much appreciate the input of more experienced users about
1. Is MS Access an appropriate DBMS to use in this situation? If not, what is a more appropriate DBMS?
2. How would you suggest deploying the database to achieve the query and data entry objectives above?
3. Am I betraying such a lack of knowledge that such an application may be beyond my reach?
Cheers for any replies,
08-05-11, 02:28 #2Moderator
Provided Answers: 14
- Join Date
- Mar 2009
0. Volume of data
The number of rows (records) is only one of the parameters to consider. The other one being the size of the data you intend to store into the database. An Access database (.mdb file) is limited to a size of 2GB but you could experience problems (read: data corruption and crashes) before reaching this limit. Also, storing BLOBs (Binary Large OBjects, such as pictures, sound tracks, etc.) is not recommended.
1. Adequacy of Access
If not for the limitations described here above, I don't see any serious reason why you should not use Access. The only objection that I can think of is that you describe potential users having different roles, so different permissions. Depending on how important the security concerns are, this could be an issue (but solutions exist).
2. Best practices
In every case, adopt the Front-end/Back-end schema from the beginning of the development. The back-end is a database (.mdb file) that contains only the tables and is used for data storage, while the front-end is another database (also a .mdb file) that contains the interface (forms, reports) and the logic (queries, functions) of your application. I would also strictly recommend to avoid Macros (for several reasons that would be too long to explain here). A very important point too is to carefully analyse the business rules, write the specifications, create a schema (or blue print) of the system you want to build, before anything else.
3. Needed skills
Are you preposterous? I don't think so, but I cannot be totally sure. It depends on the complexity of the system you want/need to build, on your knowledge of RDBMS theories in general (Set Theory, Normal Forms, etc.), on your knowledge of progamming languages in general (specifically: the BASIC language, Visual Basic for Applications in this case), on the Access Object Model and probably a few more. Don't let this enumeration deter you: you can (and will) learn, you don't need to be an expert in all these domains, you'll find people here and in several other places that can help you, there is an abundant and easily available litterature on the subjects as well as many free ressources, examples and models on the net.
4. Access and beyond
Should you reach the limitations described here above in (0.-size/volume of the data) and (1.-security/role management), you could always migrate the back-end (data storage) of your application to another data engine without needing to completely rewrite the front-end (interface and logic). Access can work easily with "true" database servers such as MS SQL Server, MySQL or even Oracle. In the case of MS SQL Server, it's easily available, some versions (slightly limited) are free or rather cheap, and several tools are available to migrate a "pure" Access solution to a composite SQL Server back-end/Access front-end one very easily.
Don't hesitate to come back if you have more questions or need more detailed explanations.Have a nice day!
08-07-11, 14:15 #3Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
The Front End/Back End configuration, mentioned by Sinndho, is absolutely required in this situation. However, because of the stated initial size, and projected future size, I would opt for using SQL Server, MySQL or Oracle from the outset, as the Back End.
In addition to circumventing the 2gb size limit, these also provide far, far better data security than Access ever can, and such security is generally a concern with databases of this magnitude.
Linq ;0)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007