0

In order to migrate a local Access database and make it available on-line I'm considering two solutions:

  1. Migrate the database to SQL and build a custom ASP application in order to add/edit/delete data;
  2. Create a Web Access Database and host it on SharePoint.

Requisites:

  1. Support multiple users at the same time;
  2. Must be available outside the office environment;

Current state of the Access app:

  • Working with need of custom changes when data needs to be imported from plain files. The data files have changed along the time and the app didn't(CSV files)
  • Users are familiarized with the current database "interface"

Problems that I anticipate:

  • After the database is on-line and available, the client needs will change and he will require more functions and features;
  • Access limitations.

In order to have an app that can be easily modified in the future, what is the best solution?

All thing consider, in terms of costs what option can become cheaper in a long time period of time?

4
  • 1
    It depends, do you have the time & budget to start from scratch? How much code is behind that Access Database now? 1300 lines of VBA, or 13,000?
    – RubberDuck
    May 19, 2015 at 10:32
  • More to 1,300 lines of code than 13,000 and the sooner it becomes online the better. My reservation about using Sharepoint is not knowing much about it. For example, if have some code in C# can it be integrated in that platform?
    – cap7
    May 19, 2015 at 10:42
  • Would hosting it on a virtual desktop that is accessible from the outside be an alternative?
    – JeffO
    May 19, 2015 at 19:06
  • Virtualization is not an alternative.
    – cap7
    May 21, 2015 at 13:33

1 Answer 1

1

We don't know all of your requirements and resources, so I can't possibly say what is best here. However, there are some things to consider.

Resources

  1. Do you have the knowledge to build this as an Asp application? Does your staff know enough to build and support it?

    If you don't have any professional devs on staff to support this, Access may be the way to go.

  2. Do you have access (no pun intended) to the technology you'll need to build and support it. By this I mean a good IDE, the ability to choose the libraries and testing frameworks you want to work with, etc.

    We have a rather large Access "database" application where I'm at just because no one was willing to pay for a couple of copies of visual studio. We use a SQL Server as a back end and only use Access as a front end. It works rather well, surprisingly.

Extensibility and Maintainability

  1. There is no VBA for an Access Web Database. You'll be limited to using Macros for everything. This limits what you'll be able to do. Customization will be hampered and limited.

    This means you'll also need to translate that ~1300 lines of VBA into macros. This is most likely possible to do, and reasonable for the amount of code you say you have.

  2. It is hard to maintain Access databases built this way. Everything turns into a jumbled mess of queries and macros. Documentation is possible, if you're smart, but certainly not as easy as commenting some code.

    Been there, done that. Maintenance will be a nightmare past some critical mass. If you picture this app growing beyond anything trivial, don't build it in Access. It sounds like this application will grow, so if you do go with Access, I'll bet my hat someone ends up porting it later anyway.

  3. You can unit test your ASP project. The same won't be possible for a web database. The ASP way would be much more maintainable and resistant to change and breakage over time.

Performance

Access is slooooowwwwww. Unbearably slow for some things. How critical is performance for your application? If it's expected to be highly responsive and fast, go with ASP.

Even if performance isn't considered to be critical, you need to take it into consideration when calculating your return on investment.

Time and Budget

It's likely that you could port your existing Access app to a Web Database very quickly and with minimal effort. If short on time and cash right now, a web database could be the right option. It will give you the most bang for your buck in the short term.

However, if you have the time and money to start from scratch and build it in ASP, then go ahead and do it. Play the long game and it will save you in the long run. It will take you more up front to get this done, but will pay its dividends down the road as features are added and old functionality changed.

2
  • We have a rather large Access "database" application where I'm at just because no one was willing to pay for a couple of copies of visual studio. We use a SQL Server as a back end and only use Access as a front end. It works rather well, surprisingly. Was in the same situation at a previous employer. 50+ users in the DB all day, each had his own copy of the Access front end. Worked surprisingly well.
    – FreeMan
    May 19, 2015 at 12:32
  • You're welcome. I hope it helps you come to a decision.
    – RubberDuck
    May 21, 2015 at 13:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.