write2avir
asked on
Converting Access 2003 LOB application to a more stable platform
Hi there!
Few years ago we developed a line of business application in Access 2003. We continued adding many modules to our application and feel that the application is bloated now. We wish to remake the whole software in a more stable application. For back end we don’t mind if it SQL server, but we are not sure which platform is most suitable for front end. We love the speed of access applications. Can somebody please recommend which front end software is perfect for making desktop applications with blazing fast speed in moving from one window to another?
We tried developing a web based software, but failed miserably because of slower speed, we prefer desktop interface only.
Thanks,
Avir
Few years ago we developed a line of business application in Access 2003. We continued adding many modules to our application and feel that the application is bloated now. We wish to remake the whole software in a more stable application. For back end we don’t mind if it SQL server, but we are not sure which platform is most suitable for front end. We love the speed of access applications. Can somebody please recommend which front end software is perfect for making desktop applications with blazing fast speed in moving from one window to another?
We tried developing a web based software, but failed miserably because of slower speed, we prefer desktop interface only.
Thanks,
Avir
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi LSMConsulting,
as A2010 also supports ADP using SQL Server 2008 as highest possible server I think this will be OK for some years. If A2012 also supports ADP again I think this is a good way for the future - moreover there is no real reason to switch to a newer Access greater than 2007 and SQL Server 2005 in the most cases of enterprise databases, maybe A2010 and 2008 but there must be something really innovative in future versions to have a real reason to upgrade. A lot of enterprises are still using A2003 and SQL Server 2000 and are happy with this.
The only thing I see is the 64Bit problem with Win7 and higher, the different versions of ADODB dlls there which make it not backward compatible and the rumour that there is no MSCOMCTL in Win7 until now (I do not have Win7, cannot say if this is true). These are the only reason why I recommended to switch to .NET in my job environment and to leave the Access way. I also think that support from Microsoft is really no reason for programmers to go away from a path - did you ever need support from Microsoft? In 99% of all cases you find a solution for a problem in the Internet or by yourself.
Cheers,
Christian
as A2010 also supports ADP using SQL Server 2008 as highest possible server I think this will be OK for some years. If A2012 also supports ADP again I think this is a good way for the future - moreover there is no real reason to switch to a newer Access greater than 2007 and SQL Server 2005 in the most cases of enterprise databases, maybe A2010 and 2008 but there must be something really innovative in future versions to have a real reason to upgrade. A lot of enterprises are still using A2003 and SQL Server 2000 and are happy with this.
The only thing I see is the 64Bit problem with Win7 and higher, the different versions of ADODB dlls there which make it not backward compatible and the rumour that there is no MSCOMCTL in Win7 until now (I do not have Win7, cannot say if this is true). These are the only reason why I recommended to switch to .NET in my job environment and to leave the Access way. I also think that support from Microsoft is really no reason for programmers to go away from a path - did you ever need support from Microsoft? In 99% of all cases you find a solution for a problem in the Internet or by yourself.
Cheers,
Christian
If you are interested in a modern software development platform which offers you the possbility of testdriven object orientated softwaredevelopment with a lot of 3rd party controls then go for .NET
But be warned, it's a long and hard way to learn it as you have different frontend frameworks ( WinForms, WPF, Silverlight) and different frameworks for accessing your data. Looking up for e.g information about displaying your data in a grid and you will find examples in Winforms but you are trying to use WPF and they wont work...
But if you are fine with the Access environment and your only issue is the "feel that the application is bloated now" I would recommend to buy Access 2010 and refactor your application and your application will be running smooth and stable again in only a fraction of time compared to rewriting the app in .NET
( Third option: Start looking at MS Lightswitch, maybe it will be ready for proffessional softwaredevelopment in one or two years )
But be warned, it's a long and hard way to learn it as you have different frontend frameworks ( WinForms, WPF, Silverlight) and different frameworks for accessing your data. Looking up for e.g information about displaying your data in a grid and you will find examples in Winforms but you are trying to use WPF and they wont work...
But if you are fine with the Access environment and your only issue is the "feel that the application is bloated now" I would recommend to buy Access 2010 and refactor your application and your application will be running smooth and stable again in only a fraction of time compared to rewriting the app in .NET
( Third option: Start looking at MS Lightswitch, maybe it will be ready for proffessional softwaredevelopment in one or two years )
By "support", I meant bug fixes, enhancements, etc etc - not direct support from MSFT. As to ADP support for the newer servers - you can work with those servers, but you cannot design objects in Access the way you could with earlier versions of ADP's. You can certainly connect to a 2008, and you can do so from an ADP - but you don't have many of the advantages of working with the ADP format - so what's the point? Just used linked SQL Server Tables and be done with it (at least in my opinion).
Hi LSMConsulting,
ah, OK, then I misunderstood that.
But also I think if Microsoft leaves the support in case of bugfixes in most cases a product is already that old that this is no theme (or you find workarounds for it for such old products). Security fixes will be released also if support has already gone for a while.
The second thing leads to the discussion what's better, linked tables or ADP - I work with ADP the last years only because linked tables have too much in between, to many conversions which leads to problems and so on. I worked with this way with the same database before and then decided to switch it completely to ADP and I would never go back again (when working with SQL Server as backend). But I don't want to start this old discussion here, that would be off-topic.
In both cases Access is a good front-end for SQL Server but my next target is to completely reprogram the mentioned database to .NET and what I learned so far until now is that this opens a lot of possibilities I never would have with Access as front-end - but I also think that it will take longer at the beginning until I have built some basic libraries again - and I cannot use the same as in Access as for example a recordset is completely different to a dataset and so on...
But I also think .NET is worth to learn it and I would also say if someone starts a new .NET project then forget WinForms and only start with WPF. It's the only frontend which allows vector-based GUIs and easy use of advanced techniques like 3D graphics, speech recognition, gestures and so on. Silverlight is a "light version" of WPF which is targeted to Web applications so this is only a choice if someone plans to create a Web application only. For a desktop application I think there's no way better than WPF. As far as I know also Win7 is programmed with WPF in wide parts and Microsoft also says that WinForms is an outdated technology now.
Cheers,
Christian
ah, OK, then I misunderstood that.
But also I think if Microsoft leaves the support in case of bugfixes in most cases a product is already that old that this is no theme (or you find workarounds for it for such old products). Security fixes will be released also if support has already gone for a while.
The second thing leads to the discussion what's better, linked tables or ADP - I work with ADP the last years only because linked tables have too much in between, to many conversions which leads to problems and so on. I worked with this way with the same database before and then decided to switch it completely to ADP and I would never go back again (when working with SQL Server as backend). But I don't want to start this old discussion here, that would be off-topic.
In both cases Access is a good front-end for SQL Server but my next target is to completely reprogram the mentioned database to .NET and what I learned so far until now is that this opens a lot of possibilities I never would have with Access as front-end - but I also think that it will take longer at the beginning until I have built some basic libraries again - and I cannot use the same as in Access as for example a recordset is completely different to a dataset and so on...
But I also think .NET is worth to learn it and I would also say if someone starts a new .NET project then forget WinForms and only start with WPF. It's the only frontend which allows vector-based GUIs and easy use of advanced techniques like 3D graphics, speech recognition, gestures and so on. Silverlight is a "light version" of WPF which is targeted to Web applications so this is only a choice if someone plans to create a Web application only. For a desktop application I think there's no way better than WPF. As far as I know also Win7 is programmed with WPF in wide parts and Microsoft also says that WinForms is an outdated technology now.
Cheers,
Christian
FYI, Microsoft has already stated that ADP's are dead and the approach one should use with Access when working with SQL Server is linked tables via ODBC.
Jim.
Hi Jim,
yep, but they stated it also long ago - they also stated that ADO is "in" and DAO is "out" and now DAO is "in" and ADO is "out" - if I would base my choices of what I want to do on statements of Microsoft....
Moreover I also heard directly from Microsoft people itself that this is a neverending fight between departments at Microsoft, where some group want to let the ADP way open and others not - so as I said, this is really no basement for a choice. And: I can also create ADPs in 10 years using Access 2007, SQL Server 2005 and Windows XP, if the environment let me do so.
Cheers,
Christian
yep, but they stated it also long ago - they also stated that ADO is "in" and DAO is "out" and now DAO is "in" and ADO is "out" - if I would base my choices of what I want to do on statements of Microsoft....
Moreover I also heard directly from Microsoft people itself that this is a neverending fight between departments at Microsoft, where some group want to let the ADP way open and others not - so as I said, this is really no basement for a choice. And: I can also create ADPs in 10 years using Access 2007, SQL Server 2005 and Windows XP, if the environment let me do so.
Cheers,
Christian
<<yep, but they stated it also long ago - they also stated that ADO is "in" and DAO is "out" and now DAO is "in" and ADO is "out" - if I would base my choices of what I want to do on statements of Microsoft....>>
Of course they won't flat out break because Microsoft makes a statement, but to Scott's point no new work will be done in that area. DAO is a good example; record locking for JET doesn't work in DAO unless you establish a ADO connection first.
The more we move forward, the more things like that will start occuring with ADP's as well since Microsoft no longer actively supports it.
Jim.
Of course they won't flat out break because Microsoft makes a statement, but to Scott's point no new work will be done in that area. DAO is a good example; record locking for JET doesn't work in DAO unless you establish a ADO connection first.
The more we move forward, the more things like that will start occuring with ADP's as well since Microsoft no longer actively supports it.
Jim.
The root of the troubles like this is that different bits and pieces of these things are "owned" by different groups as MSFT, and in many cases the needs of one group don't mesh very well with the needs of another. ADP is a classic case, I'd be willing to bet, in that it would require a very tight coupling of Access and SQL Server in order to really move that platform forward, and you can bet that the LAST thing on the mind of the SQL Server team is making concessions to Access.
Sharepoint services is a classic example of this. The Sharepoint integration is a LOT better in 2010 than it was in 2007, both from a performance standpoint and a usability standpoint. This is due, in large part, to the teams working together to a mutual goal, as is evidenced by many blog posts and such from the MSFT folks.
The chances of that happening with the SQL team and the Access team are quite remote, at least in my opinion.
<I can also create ADPs in 10 years using Access 2007, SQL Server 2005 and Windows XP, if the environment let me do so.>
And I can create applications using Access 2.0 and text files. Does that mean it's the most appropriate platform for my clients? I would highly doubt it, and I think you'd agree that doing so would be less than professional. My clients expect me to use modern technology where they can reasonably expect to have support from the owner of that technology. As such, it would be prudent to follow the indicators from the "owner" of that technology (Microsoft). In this case, those "indicators" are pretty blunt - MSFT has specifically stated that new Access + SQL Server projects should be built with linked table technology and not ADP.
Sharepoint services is a classic example of this. The Sharepoint integration is a LOT better in 2010 than it was in 2007, both from a performance standpoint and a usability standpoint. This is due, in large part, to the teams working together to a mutual goal, as is evidenced by many blog posts and such from the MSFT folks.
The chances of that happening with the SQL team and the Access team are quite remote, at least in my opinion.
<I can also create ADPs in 10 years using Access 2007, SQL Server 2005 and Windows XP, if the environment let me do so.>
And I can create applications using Access 2.0 and text files. Does that mean it's the most appropriate platform for my clients? I would highly doubt it, and I think you'd agree that doing so would be less than professional. My clients expect me to use modern technology where they can reasonably expect to have support from the owner of that technology. As such, it would be prudent to follow the indicators from the "owner" of that technology (Microsoft). In this case, those "indicators" are pretty blunt - MSFT has specifically stated that new Access + SQL Server projects should be built with linked table technology and not ADP.
Hi,
sure, nobody would create an Access 2.0 database these days - but if you compare an old database, let's say from Access 2000 or 97 and see what developers produce today - do you really see a BIG difference in the result where you can say:Hey, that was worth to upgrade to Access 2007 or 2010?
We go on using bound forms which crashes if I set my laptop to sleep mode. We go on fighting with static event functions to find out how to reach what we want - no big evolution here. We go on having reports which are not able to work correctly with printer lists and printer driver without crashing Access. We go on having VBA which is not much better than the one in 97 - but hey, we have a fine macro editor now and start to program table triggers with macros (!!!). Do you really see an evolution in newer versions of Access? We have some nice new editors which make the life for programmers easier, yes (like layouts or anchoring for example). That's good for us programmers. But on the other hand we do not have the simple possibility to create a context menu with Access 2007 and higher. But we can create Web databases with Access 2010 - oh...but we cannot use VBA then, we must again go a step back and use macros(!!).
But all with what we are fighting - what is that what the end user or customer wants? He is not really interested in having round buttons now in Access 2010 - but he is very interested in having a stable database application which makes his daily work easier. And if you only use standard methods in Access without any tricks like external controls, kernel dlls and so on - what do we have now? There is as good as nothing new in newer Access versions which REALLY help the user. Things like a calendar chooser - any "old" Access programmer has an own one.
And that's the point what I meant: The newest technology doesn't automatically means better performance or better useability. You can go on creating good databases with older versions and if you wouldn't see the Access splash screen I bet the most people wouldn't see a big difference if the graphic is freshed up a little bit.
In fact I see so many questions from people still working with A97 or with SQL Server 2000 - not because they do not know that newer versions are maybe better but often because of a lot of investment. For example licences in SQL Server 2000 which are not cheap as you know. So why should they upgrade to 2005? They will not get a better stock management database or invoice database.
And so, to come back to ADPs - there is no real reason to use linked tables because there's always JET in between and you often get in troubles with this approach (simplest example to only pick one: You must delete the table links and relink them again if you simply change anything in a table structure on the server - in ADP it's not necessary as I work on the server). I personally prefer to directly work with my database server and not with the SQL that Access JET has - to have the full possibilities of T-SQL. And finally a last question: Microsoft has stated not to use ADPs since A2007 - but ADP is also available again in 2010 and upgraded to SQL Server 2008. The SQL Server team is not the department behind that, but another one which is only responsible for ADP section in Access (I can only say what I heard from these people, of course I do not have any insight into Microsoft structure).
I think the warning against ADP has only one target: Microsoft of course wants to push the advanced technologies like .NET, Silverlight, WPF and so they hide ADP very good in the product (in A2010 again better hidden than in 2007) because this is a possible (if not the only) alternative to .NET. Don't understand me wrong: I do not meant that the fantastic possibilities .NET opens for developers are in any way comparable to what you can achieve using ADPs with Access.
I only want to say: If you want to start a new project and you do not have the time you NEED to invest into the big learning curve with .NET, you have your working environment like Win XP (which many enterprises do not want to change until now), Access 2007 and SQL Server 2005 - why do you want to upgrade? My good old Windows 95 is still able to work without Microsoft support and will go on to do so. If it's enough for the purpose you want to achieve - why not? You also don't need the newest hardware, same thing. It's modern to follow the upgrade hype, but I think this makes only sense if you get enough back for your money. For you as developer and for the customer who wants to get his business completed.
Cheers,
Christian
sure, nobody would create an Access 2.0 database these days - but if you compare an old database, let's say from Access 2000 or 97 and see what developers produce today - do you really see a BIG difference in the result where you can say:Hey, that was worth to upgrade to Access 2007 or 2010?
We go on using bound forms which crashes if I set my laptop to sleep mode. We go on fighting with static event functions to find out how to reach what we want - no big evolution here. We go on having reports which are not able to work correctly with printer lists and printer driver without crashing Access. We go on having VBA which is not much better than the one in 97 - but hey, we have a fine macro editor now and start to program table triggers with macros (!!!). Do you really see an evolution in newer versions of Access? We have some nice new editors which make the life for programmers easier, yes (like layouts or anchoring for example). That's good for us programmers. But on the other hand we do not have the simple possibility to create a context menu with Access 2007 and higher. But we can create Web databases with Access 2010 - oh...but we cannot use VBA then, we must again go a step back and use macros(!!).
But all with what we are fighting - what is that what the end user or customer wants? He is not really interested in having round buttons now in Access 2010 - but he is very interested in having a stable database application which makes his daily work easier. And if you only use standard methods in Access without any tricks like external controls, kernel dlls and so on - what do we have now? There is as good as nothing new in newer Access versions which REALLY help the user. Things like a calendar chooser - any "old" Access programmer has an own one.
And that's the point what I meant: The newest technology doesn't automatically means better performance or better useability. You can go on creating good databases with older versions and if you wouldn't see the Access splash screen I bet the most people wouldn't see a big difference if the graphic is freshed up a little bit.
In fact I see so many questions from people still working with A97 or with SQL Server 2000 - not because they do not know that newer versions are maybe better but often because of a lot of investment. For example licences in SQL Server 2000 which are not cheap as you know. So why should they upgrade to 2005? They will not get a better stock management database or invoice database.
And so, to come back to ADPs - there is no real reason to use linked tables because there's always JET in between and you often get in troubles with this approach (simplest example to only pick one: You must delete the table links and relink them again if you simply change anything in a table structure on the server - in ADP it's not necessary as I work on the server). I personally prefer to directly work with my database server and not with the SQL that Access JET has - to have the full possibilities of T-SQL. And finally a last question: Microsoft has stated not to use ADPs since A2007 - but ADP is also available again in 2010 and upgraded to SQL Server 2008. The SQL Server team is not the department behind that, but another one which is only responsible for ADP section in Access (I can only say what I heard from these people, of course I do not have any insight into Microsoft structure).
I think the warning against ADP has only one target: Microsoft of course wants to push the advanced technologies like .NET, Silverlight, WPF and so they hide ADP very good in the product (in A2010 again better hidden than in 2007) because this is a possible (if not the only) alternative to .NET. Don't understand me wrong: I do not meant that the fantastic possibilities .NET opens for developers are in any way comparable to what you can achieve using ADPs with Access.
I only want to say: If you want to start a new project and you do not have the time you NEED to invest into the big learning curve with .NET, you have your working environment like Win XP (which many enterprises do not want to change until now), Access 2007 and SQL Server 2005 - why do you want to upgrade? My good old Windows 95 is still able to work without Microsoft support and will go on to do so. If it's enough for the purpose you want to achieve - why not? You also don't need the newest hardware, same thing. It's modern to follow the upgrade hype, but I think this makes only sense if you get enough back for your money. For you as developer and for the customer who wants to get his business completed.
Cheers,
Christian
ASKER
Dear Christian and Mbizup,
Many thanks for your insightful comments.
I am not a programmer myself, but I wanted to research a bit before I make up my mind. From your comments it is clear we should think of upgrading only if access is not able to fulfill our requirements. It has been working fine and we just need to improve the front end to access our data more quickly. I think for our current requirements we can just make some improvements in our application and can keep .net application plan in the pipeline.
Thanks again,
Avir
Many thanks for your insightful comments.
I am not a programmer myself, but I wanted to research a bit before I make up my mind. From your comments it is clear we should think of upgrading only if access is not able to fulfill our requirements. It has been working fine and we just need to improve the front end to access our data more quickly. I think for our current requirements we can just make some improvements in our application and can keep .net application plan in the pipeline.
Thanks again,
Avir
I'd be leery of ADP, given the direction that Access 2007/2010 has taken. While there's no official word, the general consensus among those the MVPs is that the ADP format is a dead-end, and while there may be some minor enhancements, you'll not find support for the newer versions of SQL Server to be forthcoming.