kirk_shaw
asked on
VBA Database upgrade
Where I work, we currently have an in house database system that is the heartbeat of the company. The system is written in VBA (Access) with an SQL server database. This database system is now approaching 20 years old and while it has served us well, our external developer is retiring in the not too distant future.We have approximately 250 users accessing this database across 7 countries along with various web based dashboards and a PDA system that links into this as well.
We thought this would be a great opportunity to redevelop the database into something more up to date and current for various reasons, as there have been multiple complaints over the last 6-12 months with the current system. My problem is that I have to recruit a new developer and was wondering in which direction I should be going along (potentially web based solution) and with what languages I should be recruiting for. This system is only going to grow, so need something that will last for another 20 or so years.
I realise this is a loaded question, but my knowledge of programming is pretty non-existent. So any advice would be welcome.
We thought this would be a great opportunity to redevelop the database into something more up to date and current for various reasons, as there have been multiple complaints over the last 6-12 months with the current system. My problem is that I have to recruit a new developer and was wondering in which direction I should be going along (potentially web based solution) and with what languages I should be recruiting for. This system is only going to grow, so need something that will last for another 20 or so years.
I realise this is a loaded question, but my knowledge of programming is pretty non-existent. So any advice would be welcome.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My problem is that I have to recruit a new developer
... but my knowledge of programming is pretty non-existent.
I agree with Jim H. Be careful. And maybe you can leverage your current programmer to help you hire his replacement. Maybe even have both on the payroll concurrently so that he can look over the new person's shoulder and/or help him get the system all flow charted.
... but my knowledge of programming is pretty non-existent.
I agree with Jim H. Be careful. And maybe you can leverage your current programmer to help you hire his replacement. Maybe even have both on the payroll concurrently so that he can look over the new person's shoulder and/or help him get the system all flow charted.
ASKER
Thanks for the reply, The database has confidential data, archival requirements, images, documents.
The thought process behind it was to have the existing developer looking after the current database while the new person creates the new system in his own mould. The current developer is rather reluctant to assist in the recruitment process and his knowledge does not goes past VBA.
The thought process behind it was to have the existing developer looking after the current database while the new person creates the new system in his own mould. The current developer is rather reluctant to assist in the recruitment process and his knowledge does not goes past VBA.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So far what all the responders have missed is that the BE is SQL Server and so the Access app is not constrained by the number of concurrent users. You can have thousands of concurrent users as long as the BE is robust enough to support them and it has nothing to do with Access. If the BE were Jet or ACE, you would be looking at a hard limit of 255 users but a practical limit of around 50 depending on how heavy the minute by minute access is. With a RDBMS BE, the sky is the limit. The only infrastructure issue is version compatibility and distribution of the FE.
If you like the way the Access FE currently works, you will almost certainly hate the way a browser based replacement works. You will find it flat and clunky and slow. People push web because they like playing with new toys. Web apps certainly have their place and many client/server apps need a web facing sub-system that can be used by folks outside of your domain. But for users inside a domain, a client/server application will almost always provide a richer, more flexible user experience.
Be prepared to spend at least 5 times as much for a web app as for an Access app and be prepared for it to take 3-4 times as long as they promised. Access is a rapid application development (RAD) tool and it is tightly focused on creating data-centric applications. That is ALL it does and that is why it does it so quickly and easily. Other platforms are more generic and more powerful and so people think they are "better" but "better" is really what ever is the best tool for the job you need to have done. If you need to pound a nail, you probably want a solid hammer not a Swiss army knife even if it does have something that looks like a hammer. So before you decide to switch platforms, take a hard look at what you need to do that Access can't do for you. I can't in good conscience at this time recommend Access Web apps. They are too limited for complex, fully featured applications. Plus there is no development path. Going to an Access web app would be a complete rewrite since nothing but the data can be ported and even that can't if you made the mistake of using any of the data types introduced in A2007 that are not supported by SQL Server. So going to ASP or some other web platform would be just as easy and produce a more feature-rich application.
Rarely do systems that evolve over time have adequate documentation so I agree with Jim, documenting the business process is critical. You can't have the new developer flailing around trying to figure out what the Access app is doing. Yes, some things can be determined by outside observation but I'm in the middle of one of these projects myself. My client has a FoxPro system that they absolutely love. It does everything they want and does it the way they want to do business. Sadly their developer passed away last year and so the task of replacing it fell to me. I am using Access and SQL Server but there is really no documentation available and without the programmer around to answer questions, I'm stuck. I am not familiar with FoxPro and I can't find anyone who is who can interpret for me so I am using trial and error to figure out what the selection criteria is for various reports, etc and the business rules behind each form. Yes, I have a template, I can see what they look like but that is only part of the requirement. The user only knows when it doesn't work but isn't much help proactively telling me what needs to be done.
If you like the way the Access FE currently works, you will almost certainly hate the way a browser based replacement works. You will find it flat and clunky and slow. People push web because they like playing with new toys. Web apps certainly have their place and many client/server apps need a web facing sub-system that can be used by folks outside of your domain. But for users inside a domain, a client/server application will almost always provide a richer, more flexible user experience.
Be prepared to spend at least 5 times as much for a web app as for an Access app and be prepared for it to take 3-4 times as long as they promised. Access is a rapid application development (RAD) tool and it is tightly focused on creating data-centric applications. That is ALL it does and that is why it does it so quickly and easily. Other platforms are more generic and more powerful and so people think they are "better" but "better" is really what ever is the best tool for the job you need to have done. If you need to pound a nail, you probably want a solid hammer not a Swiss army knife even if it does have something that looks like a hammer. So before you decide to switch platforms, take a hard look at what you need to do that Access can't do for you. I can't in good conscience at this time recommend Access Web apps. They are too limited for complex, fully featured applications. Plus there is no development path. Going to an Access web app would be a complete rewrite since nothing but the data can be ported and even that can't if you made the mistake of using any of the data types introduced in A2007 that are not supported by SQL Server. So going to ASP or some other web platform would be just as easy and produce a more feature-rich application.
Rarely do systems that evolve over time have adequate documentation so I agree with Jim, documenting the business process is critical. You can't have the new developer flailing around trying to figure out what the Access app is doing. Yes, some things can be determined by outside observation but I'm in the middle of one of these projects myself. My client has a FoxPro system that they absolutely love. It does everything they want and does it the way they want to do business. Sadly their developer passed away last year and so the task of replacing it fell to me. I am using Access and SQL Server but there is really no documentation available and without the programmer around to answer questions, I'm stuck. I am not familiar with FoxPro and I can't find anyone who is who can interpret for me so I am using trial and error to figure out what the selection criteria is for various reports, etc and the business rules behind each form. Yes, I have a template, I can see what they look like but that is only part of the requirement. The user only knows when it doesn't work but isn't much help proactively telling me what needs to be done.
This usually is higher than what Access can comfortably handle, depending on how much data is in this thing. You must have had a pretty good developer.
>that is the heartbeat of the company.
Please expand on this. Confidential data? Online sales? Archival requirements? Images? Documents? Real-time processing? The answer will dictate much of the future architecture if you're looking to rewrite. Miriam's suggestion of SQL back-end and .NET front-end is an excellent start.
>and with what languages I should be recruiting for.
Not enough information to answer that one, other than SQL Server (and likely other db's) have collation/language support for multi-language applications.
>My problem is that I have to recruit a new developer
>... but my knowledge of programming is pretty non-existent.
Be VERY careful here. There's lots of amateurs, and if your db is mission critical than their ablity to eff up your world is huge, so you'll want to hire more of a rock star architect that will still develop, and one that can talk with competence about what your needs are and how that translates to design.