VBA Database upgrade

Posted on 2014-04-11
Medium Priority
Last Modified: 2014-04-14
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.
Question by:kirk_shaw
LVL 61

Accepted Solution

mbizup earned 750 total points
ID: 39993669
Web-based with 250+ users... I'd recommend keeping the SQL Server back-end and rewriting the front end/User interface in  .Net (either c# or VB.Net - it doesn't matter).

Access forms and VBA do not convert cleanly into .Net, so this would be a complete rewrite.
LVL 66

Expert Comment

by:Jim Horn
ID: 39993973
>We have approximately 250 users accessing this database across 7 countries
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.
LVL 38

Expert Comment

by:Jim P.
ID: 39994101
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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 39994166
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.
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 750 total points
ID: 39994217
>The database has confidential data
This reason alone would normally mean Access would not be an ideal solution, but that's water under the bridge.

>The current developer ... knowledge does not goes past VBA.
I have a hammer, everything looks like a nail.  Most of us developers are guilty of this to some extent.  

>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.
(1)  You guys will need to do a very good job documenting the business (i.e. not technical) requirements of what this app does, so that the new developer can build 'from scratch' based off of that document, and not based on them having Access VBA skills and observing what the current Access app does.  

The more detailed this doc is, ultimately the lower and more precise the development estimate will be.

(2)  Treat (1) as 'the bible' of what you want built, so that an incoming developer doesn't 'go nuts' with their version of 'his own mould'.

>and his knowledge does not goes past VBA.
Very well.  All the more reason for (1) above.
LVL 40

Expert Comment

ID: 39995159
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.

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question