Printable View
Hi i m currently doing a database for a company using ACCESS 2000.The currently system is using EXCEL to store data. However they refuse to use ACCESS as the new database because they said that it is a flat file and not stable. Is this true? How is the reliablity like? :confused:
I wouldn't exactly say that Access is a "flat file" storage format, but neither is it really a full scale RDBMS of the order of SQL Server or Oracle. Your clients storing their data in Excel - now that's a flat file !
On the reliability front, I must admit I've had my fair share of problems over the years with applications written within Access. As a data storage environment for use with other languages (e.g. VB) it's been fine, but I have had lots of problems particularly with older versions of Access (97) with applications written in VBA that are in the same database as the actual data. These problems seemed to me to center mostly around multi-user issues. One thing I have noticed though is that occasionally an Access database can get corrupted, sometimes to the point where you can't actually do anything with it (even get at the data). Even if the database corrupts and you can repair it, it never seems to be fully reliable ever again. So if you're building a "mission-critical" app then you might want to have a good think about it. However moving from Excel to Access is definately a step in the right direction !
Yep, what OtarBoy says is darn right. My 2 cents worth in relation to OtarBoys comments are
To minimize corruption issues
i. Split your database in 2. Put the tables in one file (back end) and the forms etc in another (front end). Normally it will be the front end that corrupts.
ii. Give each user a seperate copy of the front end to run off their client. Keep the backend on a file server.
iii. Make sure the network is running smoothly, apparently bad network packets are a cause of JET corruption.
iv. good luck working with your clients. Excel is good at being a spreadsheet and storing some data.
Peter
Ok, then one more thing. They also mention that in the long run, ACCESS will run slower and esp when a few users are accessing the database. But I feel that this happens to all programs, not juz to ACCESS. So wat is ur opinion?Quote:
Originally posted by scotte
However moving from Excel to Access is definately a step in the right direction !
It all depends on your clients requirements as to what is the best solution for them. Please provide some more detail on what kind of app. you're developing.
Opps,
I meant to say what Scottie said was damn right....
The database is meant to store records of customer orders. There is another table meant to store those customers who send back their product for repair.Quote:
Originally posted by KUZANAGI
It all depends on your clients requirements as to what is the best solution for them. Please provide some more detail on what kind of app. you're developing.
This is an ideal candidate for development with Access. At it's most simplistic level, it looks like a Customer Table, linked to an Orders table, linked to an Orders Items table, along with a Repair table and a Repair Item table.Quote:
Originally posted by OtarBoy
The database is meant to store records of customer orders. There is another table meant to store those customers who send back their product for repair.
Are you familiar with Relational Database techniques? (Primary Keys, composite keys, tables, relationships, queries etc.)
If you aren't then you run the risk of producing a flat 'Excel Spreadsheet' database, which is essentially a waste of time.
By the sounds of it, going with Access is definately your best option. It will get you away from Excel and even if you don;t know masses about Relational Databases at the moment, Access has plenty of Wizards that are pretty good at getting you started. Also another advantage would be that as you became more farmiliar with the environment e.g. creating/managing tables, writing SQL queries, you will be able to use alot of the work you had done in your project and knowledge gained to upsize to something like SQL server should the need arise.
Ya i get wat u mean. The main reason i use ACCESS instead of EXCEL is to make use of the query features so that data can be retrieved faster. Basically there is nothing much to split as i create only a single table for new orders and another for repair.Quote:
Originally posted by KUZANAGI
Are you familiar with Relational Database techniques? (Primary Keys, composite keys, tables, relationships, queries etc.)
If you aren't then you run the risk of producing a flat 'Excel Spreadsheet' database, which is essentially a waste of time.
Presumably you're intending to normalise the database?Quote:
Originally posted by OtarBoy
Ya i get wat u mean. The main reason i use ACCESS instead of EXCEL is to make use of the query features so that data can be retrieved faster. Basically there is nothing much to split as i create only a single table for new orders and another for repair.
Sorry but what is the meaning of normalise?Quote:
Originally posted by KUZANAGI
Presumably you're intending to normalise the database?
One of the fundamental difference between a (basic) Spreadsheet and a Database in the way that they store their data is that Databases utilise relationships between tables to provide a more effective and efficient way of storing and accessing data.Quote:
Originally posted by OtarBoy
Sorry but what is the meaning of normalise?
Consider the following scenario. You have a spreadsheet of parts which are to be returned to their manufacturers. In this you have the columns "ITEM", "DESCRIPTION", "MANUFACTURER" and "DATE RETURNED". What is to stop the user from typing in "Wigdte" instead of "Widget"? The answer is nothing but the users unwillingness to enter garbage data, which any Database Developer knows it practically non-existent. You potentially could end up with a table full of parts, descriptions and manufacturers which are entered with deviations between each entry. Why is this a problem?
Consider this. You search for any and all items called "Widget A". If the user has entered "Widgt A" then that record will not be found. If the user entered "A Widget" or "Widget A" then the record will not be found. It gives rise to a lack of data integrity, reduces the search functions of you database, and in essence, fucks up your game.
In an Access 2k Database, using the example above, you would have a series of tables, linked by queries to provide the same data as the Spreadsheet above. Only the database (if designed correctly) would not have these problems that the Spreadsheet has. The reason for this is normalisation.
Normalisation is the process of identifying recurring areas of information within a table, and breaking that down into another table linked to the orignal table and identified by a Unique identifier (Primary Key).
In simple terms, your tables would look like this:
Items Table - Fields: ITEM_ID, ITEM_DESCRIPTION
Manufacturers Table - Fields: MANUFACTUER_ID, MANUFACTURER_NAME
Repairs Table - Fields: REPAIR_ID, ITEM_ID, MANUFACTURER_ID, DESCRIPTION.
Now, the tables would be linked via their Primary Keys in the following way.
ITEMS TABLE - ITEM_ID >--> REPAIRS TABLE - ITEM_ID
and
MANUFACTURERS TABLE - MANUFACTURER_ID >--> ITEMS TABLE MANUFACTURER_ID
Then in query, you'd reference all three tables together, and upon entering an ITEM_ID code into the REPAIRS Table, the database would look-up the details about this item in the ITEMS table.
As you can see, it isn't the easiest thing to explain on a forum. Take a look around some of the help within Access 2000, searching for keywords such as 'RELATIONSHIPS', 'PRIMARY KEYS', 'COMPOSITE KEYS' and 'RELATIONAL DATABASE'. Alternatively, buy a good book. "Running Access 2000" by John Viescas (Microsoft Press) is what I usually recommend for someone starting out in the world of Databases & Access.
Once you understand the fundamental processes of Databases then you can explore further into them. The biggest mistake you can make is to treat a Database like an alternate form of a Spreadsheet. Just because the Table View in Access resembles a Spreadsheet, doesn't mean that it is anything like it!
Ok i understand now, really grateful for ur patience. One thing to point out, the repair and new order are 2 separate records and would not be requested at the same time. So I din create any primary/foreign key relationship. Within each table i have the primary keys for them.
No problem. I started out developing from a book and used a few message boards for help, so I don't mind spreading the knowledge now that I actually have some.Quote:
Originally posted by OtarBoy
Ok i understand now, really grateful for ur patience. One thing to point out, the repair and new order are 2 separate records and would not be requested at the same time. So I din create any primary/foreign key relationship. Within each table i have the primary keys for them.
As for the structure of your tables, obviously I was making massive assumptions regarding the requirements of the database, and based it on the info you'd given me.
Finally, if you're serious about expanding your knowledge as a developer, you might want to consider purchasing a decent book on the subject.
:) Definitely, using Access is better than using Excel for some serious database development..I would advise Otarboy to read some books on systems analysis and design. Try Systems Analysis and Design by Kendall and Kendall ISBN 971-37=0060-0. there are topics here on database design to include normalization..Kuzanagi's comprehensive reply is something to be proud of. His patience could provide inspiration to newbies..Keep up the good work..
Thanks for the kind words.Quote:
Originally posted by nutcracker53
:) Definitely, using Access is better than using Excel for some serious database development..I would advise Otarboy to read some books on systems analysis and design. Try Systems Analysis and Design by Kendall and Kendall ISBN 971-37=0060-0. there are topics here on database design to include normalization..Kuzanagi's comprehensive reply is something to be proud of. His patience could provide inspiration to newbies..Keep up the good work..