is SQL Server>,<, or = to MS Access?
-
I will try to keep this quick, but it's kind of complicated...I built a "time-keeping" database on MS Access at work, and we have 25 people using it concurrently on a daily basis, and they create new records every time they start doing something new during the day (every new task). We are getting what we lovingly call 'F9' errors (because the records get locked by another user, and we have to find that user and get them to press F9 to save the record and unlock the database) on a daily basis, even with archiving every month. My boss--the IT director--says that getting SQL Server will eliminate this problem. My job was to research prices and see how much it would cost to get the server and licenses for our office.
In my research, I became really confused
really fast. Is SQL server an "application", complete with GUI forms and reports, or does it just manage records? I've also read that you can 'repoint' Access front-end to the server, and keep your interface. However, if SQL is a true database, I'm not daunted by 'rebuilding' the entire database, because if I don't have to rely on Access at all, I don't want to (especially after paying an exorbitant amount for SQL server!).So, to sum up, can I build a new database--complete with forms, reports, and all the other 'modules' that MS Access has--in SQL Server? And, if yes, will it cut out the conflicting traffic and locked records that the database currently exhibits?
Thank YOU!
-
SQL Server is an enterprise database management system. Access is meant more for just a few users. Some say it is only meant for one user at a time. The beauty of Access is that it has forms, reports and lots of wizards for quickly building an application. SQL Server does not come with an application interface. It does have new reporting functionality added last year. You can point an Access front end to a SQL Server database, but chances are, the way your application is written, you will continue to have the same issues.
You may want to look at making sure your users can only access one record at a time on the form. One problem with Access is the scrolling through records. You might want to experiment having a form that is not attached to a table for entering records and the records will then be saved by writing code. Another possibility, if your users are only entering new records is to change the "data entry" property of the form to yes. This will allow entering records, but not scrolling through other records.
Good luck,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Thanks, Kathi...the thing is, the existing program is perfect for our needs! None of the other Timeclock programs out there 'subdivided' the punch-in/punch-out times worked into those billable to the 6 clients we have. This 'F9' error is literally the only thing wrecking my program.
My boss is under the impression that SQL Server will play 'traffic cop' between these 25 users' new records being saved/updated every few minutes concurrently. He described it like this:
Access sees the tables as one 'clothesline': the program is sending the records to the employees to view, and the employees are sending the updates down the same 'clothesline'. He described SQL Server as having multiple clotheslines. One separate clothesline is displaying the records to the employees (a copy of the dataset), and each employee is sending their update down their own, private clothesline, where it is updated into the original (display) clothesline.
Is this accurate, because if so, then I think I can convince him to purchase the pricey Enterprise edition because it will be an investment in the long run (I plan to add many modules to this program, for handling other employee-related details). What worries me, though, is the quote from your reply
"but chances are, the way your application is written, you will continue to have the same issues"
I admit, I'm new to database programming, and I might have messed up on the relationships between tables, but if the SQL Server doesn't play 'traffic cop' the way my boss assumes, then it is way too cost-prohibitive for something that only may solve our problem. I would appreciate any elaboration from anybody on this specific issue, and if anybody out there has had success in a similar situation.
thanks!
-
I would have to go with Kathy on this one. The fact is that if a 1 first user modifies a record while the 2nd user in using it, and then the 2nd user tries the save it, this user will get a warning (with acess anyways). In any case you'd have to rewrite some parts of the app to make this work.
I may suggest that you download MSDE (free) which is a smaller version of sql server. You could then see if sql server could solve that problem.
-
Hey Remi!
Where can I get it free? Also, the research I've done on MSDE says that performance degrades with >5 users (we have 25)...what's your experience been, or what have you heard?
-
http://www.microsoft.com/sql/msde/downloads/default.asp
There's also an express version of sql server 2005 that's gonna come out in november without the 5 users limit.
The 5 users is actually 5 simultaneous process. But the idea is too see whether you're still experiencing the problem with sql server, not to get a free solution.
-
Richard,
Check out this page, http://www.microsoft.com/sql/evaluation/overview/default.asp
and search SQL Server Central for discussions of Enterprise Edition vs. Standard Edition before pushing your boss too hard for Enterprise Edition. IMHO, your application doesn't warrant using it.
Greg
Greg
-
Thank you all, I'm going to follow your advice. I'll just take it one stage at a time starting w/Standard Edition and see where that puts us. I appreciate the help, and hope you guys don't mind too much if I need your help again in the future.
-
Not at all, maybe we'll need yours someday
.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply