• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

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
0
write2avir
Asked:
write2avir
  • 5
  • 3
  • 2
  • +3
2 Solutions
 
BitsqueezerCommented:
Hi,

Access as ADP and SQL Server are a dreamteam in speed and stability if the programming of stored procedures, views and frontend modules is correct. The only problem could be future OS, Windows 7 64 bit for example because of problems with ADODB and some ActiveX like TreeView. If your application is compiled on Win7 it should also be no problem.
If you want more flexibility then .NET/WPF ist the best alternative to work with data offline. But you need a lot more background in programming and you are not so fast in creating applications - on the other hand you have the most freedom because you can also create your own controls if you want. No limitations like in Access.
It's up to you and your environment/knowledge what you want to do.

Cheers,

Christian
0
 
mbizupCommented:
We have upgraded many applications to VB.Net/SQL Server.  I love the combination from a developer's standpoint, and from the users' standpoint, they are very fast.  It has eliminated the need to deploy Front-ends to all of our users machines (users of these application are at a variety of remote sites).

We also use Access Front-ends on some of our SQL Server applications.  These similarly do not have any issues with speed, but we generally prefer the .net approach when working with users at remote locations.

<We love the speed of access applications>

If the speed of Access applications has been working well for you in your environment, why do you feel it is necessary to change to a different platform for your front-end, instead of optimizing your existing Access front end?  Access and SQL Server play very well together, and if you are set on upzising to SQL, it seems like you could save a lot of time, effort and money by simply improving your Access front end rather than replacing it.

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I second mbizup's comments regarding VB.NET. If you want fast, portable code then you'd be hard pressed to beat a VB.NET frontend hooked into a SQL Server backend (unless, of course, you are experienced in C/C++ or C#).

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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
BitsqueezerCommented:
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
0
 
GugroCommented:
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 )

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
0
 
BitsqueezerCommented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 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.
0
 
BitsqueezerCommented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

 
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
BitsqueezerCommented:
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
0
 
write2avirAuthor Commented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now