Networks

Ask something
Discussion Locked

Next step up from VBA/SQL Server Thick client solution (.adp)

0 helpful
3 replies

by cg7780_123

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
All, looking for some suggestions on technical architectures that would be a good natural progression from a VBA (MS Access Frontend) and SQL Server backend Thick-client app (aka client server), which is a file extension of .adp (.ade for executable). Currently the business unit that I am a part of utilizes this architecture but there are concerns around this software busting at the seams so to speak due to business volume growth (i.e. failed load testing). At this point I'm looking for what would be the next step up in terms of architecture if we want the same flavor of client/server. Looking for a 6-12 month "software rebuild" Is VB.NET worth it, Java? Web applications etc? Again I'm brainstorming at this point, thanks for all your input!! If you want me to provide more specifics please indicate that.

3 replies

by tony hopkinson

If it's your backend database, replacing the front end is n't going to do crap for you.

Kill two birds with one stone. Pick the flakiest function you have in the current implementation. Write a simple app ( form and db connection) and then use it for a stress/load test.
You get an idea of the amount of work and whether it will do any good. If you don't have any inhouse guys to do it, hire an experienced consultant, shouldn't need more than a couple off weeks initially if you make a good choice. Don't assume someone who can do VBA/Access, can learn to use Java, C# VB.Net or ASP.net well enough quickly enough to give you an accurate assessment.

VBA is generally a bag full of bad habits, and access puts up a hard fight to do things that fall into place quite naturally in a fully fledged programming environment.

Get someone in to teach you best practices for writing Client server database CRUD 'raw'

by cg7780_123

Thanks Tony, at this point is a project coming down the pipe so the specifics aren't in yet as they relate to failures. Primarily the idea for the rebuild is scalability, in other words, can the technology handle exponential growth of useability among internal and/or external clients. I agree, the database is the key here and the fact that our data is in SQL server is a huge advantage over file-based data structures that access provides, so you point is well taken that the front end can be pretty much anything depending on the business needs (i.e. ability to deploy the solutions). So again, the main goal here is scalability, flexibility (RAD customization etc.) Thanks again for your input and I'm eager to hear more!

by tony hopkinson

Server side you have volume, number of concurrent users and number (and complexity) of transactions.
SQL server certainly can cope with those, clustering for instance, but tuning/optimising for it is very subjective.

Client side no matter how you implement the client, there are certain rules and strategies. From the appallingly obvious like all tables have a primary key to choices around optimistic and pessimistic locking. Reporting and analysis through an optimised copy for the data as oposed to intefering with basic data collection.
Managing that with replication, mirroring, snapsots, timed data transfers etc.

Facilities with in the software to ease and add to flexibility, adhoc configuration , due top taking on more customers with significantly disimilar needs, and then managing all of that. That's going to be a b'stard whatever you use.

Localisation/globalisation for instance, major nightmare that is, even with the best tools you can find.
A fully fledged programming language and to be honest given your current tech background. .net is a reasonable choice will give you far more options. Perhaps too many.

Your first step has got to be analysis of your current performance. It's likel;y that anything not working too well now, is going to be completely crap when you scale up.

This is a good site.

http://www.sql-server-performance.com/

This discussion is closed to new replies.