Solved

VBA Database upgrade

Posted on 2014-04-11
6
224 Views
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.
0
Comment
Question by:kirk_shaw
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
Comment Utility
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.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kirk_shaw
Comment Utility
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.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
Comment Utility
>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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now