June 1, 2008 at 6:14 am
Hi,
I have an access 2002 database application. I have a front end database (.mdb) with all my forms, reports, etc andn I have a back-end database (.mdb) with all my tables. The front end is used by about 20 users, and the front end connects to the backend through my LAN.
Due to performance issues, I would like to convert this database application so that it uses MS SQL Server as the back-end instead of Access. I know there is an upsizing wizard that will allow me to convert all of my tables to SQL Server. However, from what I have read, if I simply convert my tables to SQL Server and just re-link my Access front end to new versions of my tables in SQL Server, I will not solve any of my performance issues. By having Access forms bound to SQL server tables, any time I open up one of these forms, all of the table data will travel through the network and Access will still be doing all the work.
I understand that I need to optimize/rewrite my frontend and create views and stored procedures in the SQL Server backend to take advantage of the power of SQL server and limit the amount of data that travels through my network. I am just getting started with this. I was wondering if anyone can recommend any reading material on this subject. I have found several books on using Access as a front end and SQL server as a backend, but most of the ones I have found were written three or four years ago. As I am doing this mostly as a learning experience, I would like to go about it in the way that is most currently accepted/preferred. Also, unless anyone tells me it is a terrible idea, I would like to upgrade the frontend to Access 2007 before starting my work. Any books, articles, or examples that anyone could point me to would be greatly appreciated.
Also, a few specific questions:
1. Af few years ago, it seemed Microsoft was pushing ADP as the best way to create frontends in Access when you want SQL Server to do most of the work and limit the data travelling through your network. Is this still the case? Or is there a different method that is now preferred?
2. Should I even be using Access? Being it sounds like I will have to do alot of work anyway, would it be better if I look into using VB.net or something different to re-create the front end?
Sorry this email has been so long...any advice will be greatly appreciated.
Thanks,
Paul
June 1, 2008 at 7:12 am
Paul,
Regarding changing your front end to something other than Access, it is going to depend on two factors:
1. How much time you have to convert against how many forms and reports you have to convert
2. How complex the business logic is on the front end of your Access
In the long term, if this is an application that is going to grow, and is critical to your business, you would probably be justified in taking the time hit to convert. You have a lot of options, and I'm sure there are a lot of people who have gone through the process in a lot of different languages. I work primarily in Sybase's Powerbuilder, and just creating a bunch of input forms and reports would be relatively easy and doable in about a week assuming a relatively small set ( maybe 10 / 20 forms and 10 reports, or a mix in there ).
Visual Studio has a lot of wizards that can help you generate data entry quickly; I'm not sure how it is for reporting; I've always had the impression that reporting was more tedious than entry, but that is a very uneducated impression that goes back to the VB6 world, in which it was very tedious; I i know that the boys and girls in Redmond have done a lot since then.
If you are going to convert to a different front end, I wouldn't recommend going through the 2007 conversion unless there is some compelling reason ( easier conversion, wizards, etc. ).
Good luck and please let us know what you decide!
metawizard2
June 2, 2008 at 3:23 am
Hi Paul
Good advice from metawizard2. Using a "properjob" development environment is always going to be more scaleable and easier to debug, but there is a much steeper learnig curve!
I suspect though that you want general advice on how to avoid the network bloat caused by Access.
In general, always design your interface so that only the pertinent bits of information are displayed for the task in hand.
What I mean by that is say, for example, you had a command button that opens an Orders form, then the new form should just list a summary of that person's orders. Then another command button for each order on the next form could display just the details for that order. You get the idea.
The best way to implemement this is via stored procedures which take parameters that dynamically change depending on the circumstances.
The only way I know of doing this in Access is via VBA code making judicious use of events.
Designing your interface in this way has many benefits alongside reduced network traffic.
For example any mods to the design of the query can usually be made without having to alter the interface. Also, if you ever want to convert your application to say web-based then most of the T-SQL work is already done.
I think I've given you enough to get started on!
Colin
June 2, 2008 at 6:55 am
Do not underestimate the amount of effort to convert an Access app to the .NET platform. In my experience, this can easily cost $100K US and take 6 months or more depending on the complexity of the app. Not to mention the time for proper QA testing, for which you will need a time commitment from the users (not an easy task either).
There aren't any wizards or tools to convert an Access app to .NET, and most Access apps are poorly-documented, if at all, so you will need to analyze the app & develop documentation for it, before you can even begin conversion. In addition, many Access apps were written using inappropriate table & field names using spaces, hyphens etc. and/or without proper relationships, which will need to be corrected & the data cleansed.
I would begin by migrating the data to SQL Server, using the SQL Server Migration Assistant for Access (SSMA) and then converting all the reports to use stored procedures. That will yield a demonstrable improvement right away. Then, convert any bound forms that are not editable to also use stored procedures. At that point you'll only have to deal with bound forms where users can add update etc. If you have a modern server and gigabit LAN you should be OK until you can do a re-write of the specific forms that have performance issues.
June 3, 2008 at 7:37 am
It depends on the size of your Access application and it's intended customers/users. Using Access as a front end should really only be used for small projects or ones that are in-house. If this is going to outside customers or is a large project, take the hit and use VB or .Net.
If you don't want to take the full .NET hit, there is (or at least there WAS) a utility called ACCESS to VB or something like that. I used it around 2000 or so, it converts an Access application to VB6, but only about 30-50% of it. There is still a lot of coding that needs to be done, but depending on your app it may be better than rewriting everything in .NET. BUT, if you can the move to .NET would probably be best if it's in the budget and time constraints of your project.
June 3, 2008 at 11:13 am
We have a software product called MUST at http://www.upsizing.co.uk which is designed to assist with upsizing Access databases and conversion of SQL, and creating .net template applications, a trial download is also available from the site, together with lost of articles.
OK, after the plug, some more advise. I have been building Access front-end to SQL Server backends for over 10 years, and continue to do so in a large number of applications.
If you have designed forms which just open on large data sets and browse or search, then it is true that large amounts of data will be sent to the front-end. However, this is realy a client-server design issue, and not just Access specific, although Access encourages this.
You need to design your application so that data volumes are restricted using filters and prompts to users, and then only display filtered results. This approach will be required regardless of the front-end tool.
When you open forms with filters and run queries in Access you will be pleasantly suprised by the performance in Access and running the SQL Server profiling tools is valuable in finding out exactly what Access is doing.
In my experience, if you are planning conversion, do it stage by stage. First migrating the structure and data, then optimising poor SQL as views or sp's, and then possibly converting all the SQL to SQL Server and finally converting the forms to .net and using report services after converting the Access reports. As has been pointed out this can be a large undertaking, and good tools help to do a good job.
I hope this helps.
Andrew Couch
Office Access MVP
June 10, 2008 at 3:06 am
Just to note. I converted my Access XP backend to SQL Server and if you just convert the tables it is relatively painless and it brings a great improvement. If you then convert your queries (you may need to do this manually) then you can have an even greater performance boost at very little effort. The wizard struggled at converting queries, ADP looks like a lot more hard work and may involve changing any coding from DAO to ADO.
Once I had SQL Server Express installed on my machine, I copied my access database, and then did a full convert. This converted very few queries, but helped me to understand them.
Then I looked at my slowest queries and rewrote these in SQL Server (okay that was a bit of a learning curve, but if you can do Access you can do it), and you can test the new query results against the old which helps. The forms, code, reports and the rest did not need to be changed.
I'm in a large organisation so to get it out of the test environment will require them to give me an SQL Server database access (you could just use SQL Server Express) and then I will give everyone a separate copy of the front end installed on their machine. This should overcome the performance problems, and is an enterprise level solution.
At that point I would not recommend doing anything further unless you have a lot of time, and are keen to learn something new. On another project I am working on going straight in with Visual Studio Web Developer Express, and at the moment it is more of a stopper than an express.
Access front ends are easy and do the job, there are better solutions but for many tasks they do the job.
June 10, 2008 at 7:08 am
Hi,
Thank you all for the advice. I will dive in and upsize my backend to SQL Server. Of course, I will do this on a test system so I am not under too much pressure to get it to work right away. For the time being, I will stick with Access as my front end and do what I can to move processing from Access queries to SQL server stored procedures and to redesign my forms so that I don't have large, bound, unfiltered recordsets. I'm sure I will come up with some more specific questions as I do this, but your advice has been great for getting me started.
After I get this working with an Access front-end and SQL Server backend, and I feel like I have done everything I can to optimize performance with this setup, then maybe I will evaluate the advantages of possibly switching to a different front end.
Thank again,
Paul
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply