June 26, 2008 at 7:35 pm
Hi,
I have a requirement to develop an User Interface (to help few users run certain reports) that would fetch data from the SQL server 2000 database. Now, we think of using 'MS Access 2007' for the development of the frontend. I read through few articles reg the same which does not talk anything about the limitations of using Access as frontend. Could anyone give me some pointers in this regard - if such a design would have grt limitation?
Thanks in advance !!
Regards,
VJ
June 27, 2008 at 5:34 am
Using MS Access as the front-end application will probably greatly speed up your development process for initial deployment. You get a bunch of great tools for the UI development that would otherwise have to be created or purchased.
On the down side, you are going to possibly have some performance and scalability issues down the road. MS Access - even as a front-end application to a SQL Server does seem to scale up poorly.
Unless you write all of the data access code yourself (which would take away many of the nice features Access offers) you will find that it has a tendency to take optomistic or pessimistic locks on relatively large numbers of records. So, in a multi-user environment in which users may be viewing and editing the same record or records near each other in a table, you will find MS Access applications tend to have a lot of record locking conflicts. This is particularly prevelant in the nice feature of being able to edit multiple records in the list views.
Also, MS Access front-end applications tend to pull large amounts of data and not use it. This can be managed much better by using an adp rather than an mdb with linked tables (the access data project connects somewhat natively to the SQL Server rather than using the Jet database engine). Either way though, the defaults for creating forms are to choose tables and it will essentially be running a "SELECT *" at the data source whether you display all of the fields or not. This can easily turn into a bandwidth issue later.
I am sure you will get varying opinions, but I have never had good luck with an Access front-end application and a SQL Server back-end with more than about 2gb of data or about 15 users. At that point, I have found that the work I need to do in the access database forms to manage the scaling up issues has over-taken the amount of work it would have taken to write an application in C#. This will obviously be very different depending on the type of work being done with the application.
June 27, 2008 at 7:00 am
Hi Micheal,
Thank you so much for your inputs! could you give me any other suggestion (any other GUI developer) for our requirements ?
thanks in advance!
June 27, 2008 at 7:11 am
You have not really described your requirements in any detail.
So far, you need a GUI for users to run some reports. The first place I would direct you is Reporting Services and Report Manager (since your SQL license included it and it is a complete UI for running reports).
I assume you have other requirements. How many users do you have? Are they all on a single LAN, or are they in remote "semi-connected" sites? Do they need the ability to input information in the application, or is it just reporting? What other applications do they use (outlook? in-house software?)?
If you have not really though about these to this point, I would recommend you get some help from a consulting company for a couple of days. You could have someone come in and at least look through your systems and requirements and help you make a good technology choice. It will be way more expensive to fix a bad technology choice later.
June 27, 2008 at 12:32 pm
I used Access 2003 on an SQL 2000 database for 6 years for a small company. Worked great. Definitely go with the adp (project) option, rather than the mdb (linked tables) option.
An Access project can operate on SQL procs and functions, which can get you past the problems of excessive row locks and excessive data pulls.
The main issue I had was pushing out updates to the Access app, which I accomplished by having the shortcut on people's desktops go to a self-extracting zip file, instead of directly to the Access file. That way, if I put an updated version in the zip file, they got it the next time they opened the app. (I had a Dev Edition of MS Office, which meant I could redistribute the Access runtime. So we didn't even have to buy Access licenses for employees, just had to have the runtime on the image disk for setting up new computers.)
This was limited to a few dozen users in only two sites, but it could easily have scaled to a couple of hundred users (I tested that) without performance problems.
The problem with it is that Microsoft has said that they will "soon" cease to support this option. That means you may suddenly find yourself having to convert to .NET from Access, which can be a major undertaking. Because of this, I'd slightly recommend going with .NET apps and Reporting Services, instead of Access. Access is currently a workable option, and it does support very rapid development of forms and reports. It can be used to build very complex applications. But .NET will almost certainly end up being better in the long run.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply