Avatar of cynx
Flag for India asked on

Sustainability of excel (VBA) - SQL server 2008 App for large number of users

Currently we have a vba application with Excel front end and sql server 2008 database which performs complex calculations and loads/saves large amout of data to-from sql server backend.

Currently this is being used by approx 150-200 users within same country.

We are now planning to roll out this application to larger group (approx 1000 - 2000 users) across large geography (accessible within our VPN) .

1) I am no database expert, hence need few suggestions on what to be considered (technical aspects) on database and front end side for above roll out?

2) Also on other note, if we have luxury to rewrite this app in other technology what would be the best replacement (for eg. aspx - sql ?) considering this app has large number of records to be imported and processed from excel files.
Microsoft ExcelMicrosoft SQL ServerVisual Basic Classic

Avatar of undefined
Last Comment

8/22/2022 - Mon

First of all i don't see any problem in scaling out your application from 200 users to 2000 users. But considering that it is excel and vpn, please check:
1. Network bandwidth. As you are using a lot of excel rows to import/export, it may be a hinderance when working across the globe on VPN. Though, you can not do much in this aspect.
2. Current Architecture. If you can get the current locking/blocking status with 200 users then you can anticipate the locking/blocking that may cause in 2000 users. This might gives you some idea about changing the architecture of current system.

If you don't put something like blue-moon benefitis, no-body would allow to re-write the application. But still if you get some chance, my idea is to go with ASPX~SQL or VB~SQL. Being the microsoft technology, much of your current VBA code can easily be re-written in the new technology.

Thanks for the information. In addition to above any tuning required on database side (which is hosted on a poly server) ?

My justification for aspx-sql over vba-sql would be that a web app would use the server to process the calculations over vba front end which depends more on user's local system (which usually results in slow performance if sys is installed with low memory). Would you agree on this ?

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy