16

Background: I have an Access-software (~30 simultaneous users) with split front- & back-end, both UI & DB running on Access-files. Most important features for me are reports & printing, with emphasis on fast development and deployment to the users (updates 1-3 times per week, using as little time as possible). The environment is Win7 + Access 2010. Updates are handled with .BAT-batch file. The database might be upgraded into SQL Server Express or something later this year.

Problems: The Access development UI is getting slow, mainly due to linked database tables, which are files located in network drives (and one sharepoint list). Also, I'm aware that Access is not ideal database for multi-user stuff. Then there is cosmetical stuff like hiding the main Access window. I have code for it which works great in WinXP, but in Win7 the background no longer stays hidden. Overall, the whole software is not very professional.

What I need: I need to be able to do fast development and deployment without any admin rights in Windows 7 environment. Clients need to be able to update their UI with minimal (if any) effort, meaning no installers for them.

Obviously Visual Studio might be logical next step, but I have so little experience with it I don't know how fast can the development be there? Then I've heard of things such as AppJS, but does that work well with devices (printers)? Also, all data is confidential, so everything should work without outside servers and dependencies (future database server will be inside the building). Otherwise, I would love web-browser environment, since it could be used and developed from anywhere.

What options do I have and why, or should I stay with MS Access?


[Edit #1] Client-side javascript It would seem one option would be to run client-side javascript and use it to connect into SQL Server. However, I'm hearing that though it's possible, it's also bad practice due to the security concerns. While unsecure connection strings and source code isn't a big problem in company's internal use, it's still something the company's IT-rules might not allow. Also the UI development would not be as rapid as with Access, though it's still great if skilled enough with JS. I will propably try some prototypes with this.

[Edit #2] 8 years later I just wanted to comment for other readers that we migrated the database to SQL Server and it has been smooth sailing. Currently we're using the latest MS Access as UI and SQL Server 2019 as backend database with some encrypted columns too. Later we will be migrating to Azure database. New applications are mainly using PowerApps in Office365 with Sharepoint Lists as database (with also the option of migrating to SQL Server). Highly recommended.

4
  • You say: "Otherwise, I would love web-browser environment...", but have you considered that is not a mutually exclusive set of options? Wouldn't a local intranet site solve the confidentiality concern and give you your development environment of choice at the same time?
    – Caleb
    May 1, 2014 at 15:51
  • Yes, but wouldn't a local intranet site require a running web server somewhere in the building? Currently I only have shared network drives and user computers which will be turned off at night.
    – Stackmerre
    May 1, 2014 at 16:25
  • 2
    Does't your system already rely an having something turned on? Are your shared network drives on the client machines or central? You could conceivably distribute the software itself just as html/js/css to be run opened with file paths in a browser (you don't need a web server for that if you only use client side processing), which just leaves your database. Don't you have a system acting as a database server already though?
    – Caleb
    May 1, 2014 at 19:09
  • I do not have database server already, since Access-database is file-based. However, I may have forgotten that JS pages indeed doesn't need webserver, so maybe I should do some research on that.
    – Stackmerre
    May 6, 2014 at 6:52

1 Answer 1

6

Maybe not the greatest solution ever, but the cheapest one if measured by required effort is conversion of your Access database to SQL Server, preferably to Microsoft SQL Server.

In Microsoft Access'97 and 2000 there was even a tool called Upsizing Wizard doing exactly this thing.

Most important facts:

  • MS SQL Server is free until it runs single CPU core and until it is not used to serve its connections to users outside the company (to clients), i.e. in form of hosting service. It remains free under same conditions if you sell client-server solution to someone else.

  • Transition to MS SQL Server is least breaking one (if you are migrating from Microsoft Access), changes in your existing SQL code are minimal. Other SQL servers can be used, too, but the "code distance" you need to go is greater.

  • Basically you can leave your Access client apps as they are except of changing connection string and adjusting incompatible SQL commands

Later you can rewrite your client app.

The point is, you are not being pushed into big breaking change on both client and server side at once.

I have gone this way with one application about 12 years ago (it was Access'97, SQL Server 2000). We first migrated the data, few months later customer decided to rewrite the clients. There were no serious obscatles in the way.

Since that time I was maintaining/extending one well-written large professional enterprise web application, now I'm back at writing binary clients with classic windows and forms. My subjective outcome is that with web application, development productivity is lower (browser compatibility issues, unwelcome Back button in browser, more difficult debugging, UI controls are very limited) partially outweighted by better accessibility you can gain (if browsers allow). So my subjective opinion would be NOT to go way of web app as you might need much more energy to reach comparable results. But if you already got heart for it, I still wish you good luck with your new app.

Reagrding your request for "very rapid development": I believe it is possible for very small solutions. For larger ones you need to carefully write your bases (unless you are copy-paste programmer) to manage growing complexity of your app. Writing them has often nothing to do with "rapid development" as laying solid foundations doesn't immediately produce new screens, functionalities or other results. I have seen an app written using rapid development to large extent, but now it is waiting for rework, because code sanity was sacrificed to "quick results" and you will find the same code with minor modifications copied and pasted 100 times. It's programmer's hell, very difficult to maintain. (If such code needs change or bugfix, you need to apply it to 100 places.)

1
  • Thanks for your reply, this is propably what I will do. The company already has SQL Server databases so it's very likely I will get access to one during this year. Hopefully it will also speed up the design view of Access, since SQL Server is much faster than file-based databases on slow network drives. I have started on tweaking my app too by cleaning code, making smarter functions and optimizing queries. I agree with you on the web application too. While there are good components for UI design, it can never be quite as easy as Access there, not to mention the background code itself.
    – Stackmerre
    May 8, 2014 at 4:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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