I know database format could be debated forever so that is not the point of this question. I am writing a program for a small company, about 15 employees. This program will handle Job tracking for each of their projects. It is basically an accounting program with a lot of extras. They will use it to take in employee time and then be able to print invoices and reports about projects and shipping information, customer reports and things like that. It is just a way for them to combine their accounting system with time sheets and all that. My question is can I use an Access database to handle this or do I need to use SQL server or what. I plan to write the program using Visual Basic .Net and the database will sit on a Windows 2000 server with XP clients. I am very familiar with writing to Access databases but have little experience with SQL server. The company has about 2000-3000 jobs a year and most jobs have a complete cycle time of about a month. I just don’t know what the workable limit of an Access database is. I would prefer to use the Access database to keep from buying SQL server and because I am most familiar with it. I am trying to keep cost as low as possible for them. Any input would be appreciated.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hey jtwestmo!
From your description of requirements, I would say you are save to stick to Access and avoid the higher priced "spread".
Some of the limits of access are:
2GB MDB size limit
2K record length limit (not including memo's that are stored separately)
It doesn't some like you would hit either of these with the specs you stated.
Proper db design and normalization should keep you well away from the 2K record limit.
regards
Jack
I do agree with JA and JT: Access will be sufficient for the project.
Besides the point, there are existing packages that will probably do most what the company are asking for such as Quicken for business. I am just an user of Quicken.
I am not suggesting you to use it to get yourself off the hook of doing the project.
GT
Quicken 2004 Premier Home & Business for Windows
Key Advantages:
- New Express Account Setup instantly connects Quicken® to your financial institutions, downloads your transactions and balances, and sets up your accounts with your data already up to date.*
- Instant business overview. See all your unpaid invoices, upcoming bills and more in one central place. Makes it easier to manage your cash flow!
- Create customized invoices and estimates with your own look and logo. Print them or e-mail* them as HTML files for a more professional business image.
- Track multiple jobs and projects for each customer. Stay well organized as you keep track of estimates, invoices and customer payments for each job.
- Get an in-depth look at how your business is doing. Generate cash flow reports, P&L statements and more on screen or on paper.
- Simplify business taxes by easily tracking mileage and business expenses all year for your Schedule C.
- In addition to this, get all the features in Quicken 2004 Premier to see your complete personal financial picture in minutes
They are currently using quicken and are just not happy with it. I just mentioned the basics of what they were wanting to best describe it. I am a civil engineer by day and a programmer by night and I have found in all the engineering offices I have worked for thus far they are never happy with what ever accounting package they have. It is just never exactly what they want. Maybe it has to do with an engineers nature I'm not sure but this is for an engineering office as well and they just want a more complete package just for them.
jtwestmo:
2K record len: Total bytes on a record of true stored data = 2K, no more. (not including memos)
Humble Advice:
If you have designs on recreating the function of most REAL Financial mgmt packages, fasten your seatbelt. I guarantee you will be "white-knuckling" the steering wheel on this one. Unless you ARE an Accountant or have one consulting on the project you are in for some major headaches. geotiger could not be RIGHTER on this one.!!
A True Statement: When a customer indicates to me that what they want is in any way associated with financial management or tracking, I tell them to investigate a REAL package of this nature. I will NOT BUILD such a database. This does not include Inventory control systems and the like. Just those systems that "count the beans".
Quicken isn't an accounting package. QuickBooks Pro is as it supports (requires) double entry accounting practices. Don't build an accounting system, but *do* build custom add-ons, reports, etc. There is plenty of work to do around the fringes of an accounting system.
If you know Access, then stick with it. Access has a very nice upsizing feature that could move you to SQL Server in the future with your same code base. Just make sure you use ADO to access data, not DAO. DAO is obsolete.
The reason for going to SQL Server in this scenario is not so much the size of the data, but the robustness of things like backup and restore. The question you should ask the customer is how much data they are willing to lose. If they say none, then they need to be educated on what could happen so they can adjust either their spending upward or their expectations downward. Access doesn't support Transaction Logging, which is essential for maintaining the integrity of a database in the real world. But Access should work fine as long as the customer knows what to expect in advance. This is to your benefit as well as theirs.
I know you selected an answer, but one more possibility, you could also use MSDE. MSDE is the "scaled down" version of SQL Server and it's free.
You still have some of the limitations of Access (2gig DB limit and it automatically slows down with 5users--but you can have more than 5). A big plus with MSDE is the database integrity is better than access. Also, you can write true stored procedures and enforce security a lot better. Not to mention, when/if you decide the performance isn't good enough, or the company grows, you can easily move the database directly to SQL Server without changes.
Brett
Specifications does say that an MS Access database can expand upto 2 GB. But in practical situations, the speed of data access will start degrading if the size reaches even 25% of the limited 2 GB. And multi-user access to a MS Access database becomes slow if it crosses 5 simultaneous user access.
Brett has rightly suggested MSDE. It is a better database solution where you can implement real stored procedures and triggers. And the best part of it is - It's FREE. And of course, porting to SQL Server later is much easy.
Hemant
here's my two cents...
I found that access is actually faster for certain queries (like doing a full table scan with a query using the "Like" preticate. If the client is on the same machine as the data objects then your program should run like the wind. Splitting the data and programming objects can impact performance (if you link tables, etc.) even in a LAN environmet. Furthermore, if your planning on using your program in a WAN (low bandwidth, dial-up etc ) environment, then unless you really go out of your way to facilitate that with Access, SQL server is the only solution.
Afro.
If you are not familiar with MS SQL, develop your application by using access shared on a server as a file. Its faster to develop a pilot for testing purposes with access. Get yourself Access 2003 and you can easily migrate up to MS SQL using Access as database manager for both platforms. You can also get an trial lisense for MS SQL 2000 and Win2K3 server if you got the hardware to run that application on. I would after the pilot fase recommend that you try the speed of your application using MS SQL - from my exprience tuning a database for speed is much easing using MS SQL than Access.
Usually we develop for WEB using .ASP with MS SQL, comparing the same application using Access vs. MS SQL is like comparing a trutle with a jaguar in speed and elegance. No one will kill you if you show your clients a better environment using MS SQL compared with Access that take care of stored procedures (speeding up big/long queries) and the more efficient backup routines
you will have with MS SQL. Remember to enable the SQL agent to have the maintenance plan runned perfectly and to trim the database.
Access is good. But if you're talking about MS SQL... whew expensive stuff...
Why not mySQL? It's free and more robust than MSDE. It also does have a limit on databases... the new version, I do believe, supports up to somewhere around 1,000,000 terabytes (that is IF your server can handle it).
Although mySQL is CLI, there are many GUI tools available for download like mySQL-Front, which makes it very easy to use.
Just a thought,
- joshbm
The microsoft trend walk with SQL server. I think that the parameters to take for the dessision are:
Access: Until 6 simultaneus user and/or 60000 base records (it means the nuclear record of the application).
SQL: The contrapositon of Access.
I use the Ms-Access product as pilot for SQL implementation
I would agree to use MSDE instead of MS Access.
Your client application is VB and use OLE-DB instead of ODBC to connect to an MSDE database.
1. research the OLE-DB connection string speeds over ODBC
2. MSDE is very nice and you should learn MSDE instead of MS Access.
3. MS Access these days but cannot compete with MS SQL
I have coded and done systems integration for 10 years starting in MS Access 1.10a, VB3, MS SQL Server 4.21a and all the way up to today's standards, but I'm shifting gears and using mySQL and php since it has greater long range penetration across the Internet.
Good luck. I actually love DB discussions, but I'm finishing grad school and I have been away from newsgroups and forums for 2 years.
Thanks,
Andy
http://www.andyf.com
MSDE is an ideal solution for this type of application.
Be careful with licensing with MSDE - its not very clear and you'd be forgiven for thinking it was a freebie product.
If you're not a SQL expert, start off designing in Access then upsize it and see what happens.
Also, though this is a fuzzy area, if you get the client tools for MS Sql you'll have almost full functionality of SQL Server, just throttled a bit.
the MySQL argument is a good one but only when taken DB for DB. When you factor in the SQLClient connection optimisations of ADO.NET - MSDE starts to look more attractive.
hth
Damian
I never dreamed when I made this post that it would get this much attention, I guess I hit a sensitive area :) I think what I am going to do is try to use MSDE. I am very good with access but I do think someday this might need to be upgraded and it appears to me that the transition from MSDE to SQL will be easier than Access to SQL so that is what I am going to try.
Ankit_J_P:
MSDE is the Microsoft Desktop Engine - a 'scaled down' version of SQL Server 2000 - it is FREE, and does NOT come with Enterprise Manager or Query Analyzer. You can use it EXACTLY like SQL Server, and it can easily be 'upgraded' to FULL SQL server 2000. The other 'restiction' to MSDE is that it is persormance limited to about 5 simultaneous connections, and performance begines to scale back with more connections.
check out the Microsoft page at:
http://www.microsoft.com/s
that will explain ALL about MSDE.
AW
I agree with SQL vs Access. But, to clarify, performance issues is different if you are using a web database vs a network database.
In a network, use the automatic client/server wizard to place the tables on the server and the rest in a 'front end'. Both files are Access and performance issues are deferred.
Also, if your server is less than 50, you should license the Small Business Server with SQL - keeps your costs down.
On the web, when you have more than a few concurrent users, you should be using SQL.
it is not important that what should u consuntrate on or what app u like but the main thing is that u must make a feasiblity report that is important, compare every thing. soem times access works better than SQL .
the matter is not that MS SQL is better oracle or access is better but better is that is feasible to u, through u can get what u want(faster accurate and reliable etc).
thanku,
shabbir,
I've assisted in the development of a similiar application. We used an Access MDB. I do sometimes wish that we had a SQL server instead of this database. Even with a relatively small table (8000 records in the one I'm working with at the moment) it can take several seconds to run a query on a database file stored on a file server- wheres running the same query on a database stored locally (copied to a workstation's hard drive) doesn't take a noticable amount of time. With SQL, queries can be executed at the server, and it's very fast.
You mentioned you will use VB.NET. Looking at the date of these posts, im guessing you already found out, access has several problems with the RAD Design features in .NET namely using the data adapter oledb designer feature. There are several column names that compute as keywords to access (much of the vb script library) and will cause instant error on your .NET project as soon as you try to drag a table from your server explorer to your form.
I'm suprised nobody mentioned this, but i would watch out for these types of errors, they come out of nowwhere, have zero explaination when they happen, and leave you stumped.
Also, access has unnamed parameters when using the vb.net data adapter designer feature, which causes me some headaches. I've also never been fond of access's ability to go corrupt and in some cases you lose the entire database if there is faulty network traffic of any kind.
Obviously, either will work, but since MSDE is free, and its a nice addition to your personal knowledgebase, i recommend, learning it, and using it.
Business Accounts
Answer for Membership
by: Arthur_WoodPosted on 2003-12-24 at 08:39:12ID: 9997178
Access should be just fine. SQL Server would be VERY expensive.
Access 2000 is limited to a MAXIMUM MDB file size of 2 Gigabytes, but the job parameters you describe should not reach anything close to that limit for MANY years.
AW