Convert Access2000 to vb front end. 1

[AppStaff]

Anyone have any good white papers, tools or experiences to relate on converting an application from an Access2000 front end to a Visual Basic front end. Also, Any tips or white papers on improving performance using SQL Server would be helpful.

 

[thornmastr]

I’ve done this a few times and like it less and less since VB is VB is VB and now in all truth the only real difference between VBA in Access and VB6,0 is forms, and let’s be brutally honest about this, Access beats the hell out of VB 6 when it comes to form design and form integration. In fact, today the only powerfull point VB 6 has over even A97 is distribution and the fact that VB still produces a relatively tight executable and Access produces a giant; other than that….forget it. The days of VB being better than Access are long gone. For that matter, the white papers are gone as well.

There are a lot of VB developers who will admit they are developing more in Access these days simply because the development time schedule in Access is far less than in VB, but if you want to convert from Access to VB6 you certainly can, provided you have a few top flight VB form experts on your team because converting those forms is a real nightmare.

The general consensus is that if you are using VB only, you will need to use ADO, but if you are still enmeshed with DAO, and a lot of us heavy duty Access developers favors DAO over ADO, you certainly can use the DAO libraries with VB and I find the results in terms of speed to be very good. My preference is to keep the back end using SQL SERVER, but that is just my preference, and there are some good white papers in that arena.

So while I do not think I gave you what you asked for, yes, you can go from Access to VB and suffer only from the deficiencies of the VB forms interface which is putrid. My primary reason for not liking this task is that in general, designing and coding in VB when compared to using Access, the time expenditure is almost 2/3 grater for a VB project; which, if you are a private consultant is at least profitable.

Good luck.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[AppStaff]

Thanks for you response. My primary motivation for this conversion is that someone sold a customer on the performance improvement. The sql database has a couple tables that are 300,000 records. With this type of database do you think a vb front-end would be noticably improved in speed over the access front end?

I'm inexperienced with VB and SQL Server though very experienced in ACCESS. If you think it better to stay with access then what can I do to the access/vb legacy system that would prove a performance increase to the custom? I know this is a question that sort of begs specifics but any general direction or reference material is appreciated.

 

[thornmastr]

Perhaps he did a performance analysis between VB6 and Access 2.0 of eight years ago. I don’t think even the most die hard VB enthusiast went down that road after A97; however, given that he gave him a real ride with little real proof, you are stuck with the result.

You do have ONE tremendous advantage in that this appears to be an application for in house use and you are not trying to find performance enhancements for off site locations.

A 300,000 record set is nothing for Access to get upset over. However, if you are dealing with all 300,000 on one form you’ve already found your problem. Not even the staunchest VB enthusiast would claim high speed here. My first suggestion to you is to find some time to sit down with your boss and find out from him exactly what is slow about the Access application. In most cases he is going to say, with a nasty grin on his face, this particular form loads as fast as his dog squats on a cold morning. Encourage him. You are trying to gauge his perceptions. Once you know what to him are the horrid results, in most cases you will find two or three forms, about twice that many reports, and one or two free standing queries.

In at least nine out of ten cases he is absolutely correct. Usually in almost all medium to large scale Access applications, there is at least one form that loads so slowly you can drink a cup of coffee and smoke two cigarettes waiting for it to finish. Spend a bit of time finding out which is the worst of the worst. Take that one and closely look at the underlying record source. In most instances, a user only wants to see between three and ten records; not three hundred or ten thousand. I have seen applications that pull back 10,000 records from a SQL SERVER database and then filter out 9,999 of them to display the one the user really wanted. Pour over that query. Break it apart. Create at least 5 queries that do the same thing but each one faster than the last, and each one bringing in far fewer records than the previous version.

I have not seen a record source that returns more than one record that cannot be improved or made faster. Realistically, Access developers cause the negative comparisons between Access and Visual Basic because of the quality of their results.

The best approach to take is to get a user assigned to test your work, and his /her one criterion should be comfort with the product. Is it easy to use, is it fast to use?

The only real reason many users feel that VB applications are faster than the Access equivalent is because since it takes on average three times as long to do the VB project, developers tend to spend much more time refining the product.

This should certainly get you started. It could possibly solve your dilemma.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[ppkgoldengun]

Having done some development in Access I certainly would like to improve my query performance. Can you recommend any "general" guidlines in developing quicker queries?

 

[AppStaff]

Thanks again for your comments. I was really hoping to find a technical process or white paper that addresses some specifics on sql server/access 2000 performance.

 

[thornmastr]

The following articles located at the MSKB will certainly provide you with information on query optimization under JET and under SQL SERVER. These should also be of help to
“Goldongun”.

Q209126
Q208858
QQ209551
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com