upgrading access db

Good day,

This time I need strategic advise, because the things are coming to the critical point.
1. Background.
Access application designed since 2006 (MS Acc2003) for personal use to make the life easy.
2. Recent events.
Company did like the application a lot and asked to deploy it not for me only. I have split the database to FE and BE+.mdw with passwords and access rights.
Both FE and BE are deployed in the same directory on the server (there are no PCs, there are workstations=monitor+keybord). Means, all users are addressing to the same FE file.
3. After 8 month of multiuser operation ( 4 persons) database was quite reliable. Growing of the BE file about 12Mb per month. Now the complete database is BE=220MB(about 130 tables) + FE= 128Mb(more than hundred forms+ extra reports, qrys,macr etc.)
4. Now some more users want to join (theoretical maximum- 32 users);
5. The amount of changes I'm doing (new tasks/tables forms etc.)is decreasing compared to previous months, but the modification of the forms is constant.
6. Also there is an idea to give an acces to certain parts of the db for  some remote sites, which are not always online (means uploading or synchr or I dont know).
Considerations (I simply can not know this):
- Stability of the database in case of bigger number of the users;
- Maximum size of the BE declared as 2 GB , which is not an extraordinary figure; (there is a tendency to ask for big memo fields);
- Speed of the database in case of many users and bigger BE size. (some sql applications here are simply slow).
- From time to time, there are complaints that memo field text formatting options are poor;
- A lot of docs are linked to the db(OLE). Any trouble, will damage the links= tragedy;
Summary:
- I have a feeling that Access itself will soon become not enough;
- I am investing huge amount of time to this db (I'm not a professional programmer, I am  supervising engineer. Thats my job actually).
May be stop spending time with Access and start digging something else? Lifting something new from scratch will need time also.
If the decision will be taken, it will have to last years and years. Limitations with further development can hardly be accepted.
Any ideas which way to turn? Or maybe continue with Access? One time I can afford to loose data but only one time and we must be sure that the platform will not be changed in general in reasonable future.(better not to loose of course).

Best regards,
hotelguestAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OllarConsultingCommented:
The "easiest" ( I use that work conservatively ) 2 phase route would be the following:

PHASE 1:  convert the backend to SQL Server.  It is a pretty straight-forward process and that would give you plenty of options for the long term.  You can use conversion tools to essentially import the back-end into a SQL Server database and then just update the links in the front end.

PHASE 2:  after phase 1 is stabilized, you can look at how to handle the front end.  The fact that you may have an "offsite" requirement complicates things considerably.  The initial thought would be to create a web-based front end for users and then spin-off that remote,offline requirement into an entirely different development effort.
0
hotelguestAuthor Commented:
First of all thanks. Actually, FE does not bother me a lot at this stage. I fact, my nice coloured forms is the last thing i want to touch. Others did like the db because of handy forms finetuned during years.
What about 30 users online?
0
OllarConsultingCommented:
I encountered this exact situation ( minus the remote/offline requirement ) a couple of years ago with a client.  They had an Access DB ( split between front end and back end ) that started with good intentions, but needed to scale beyond its ability.

Our solution for them was to leave the front end in place and migrate the back end to a SQL Server database.  The migration was extremely straight-forward and the result allowed the client to scale to a much larger number of users and also resulted in a faster response times when using the application.

It obviously is going to depend on the hardware upon which you are running SQL Server, but scaling to 30 users and beyond shouldn't be an issue.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The issue you'll have is 30 users sharing the same FE. 4 users with split FE is generally quite good in terms of performance. 32 users sharing the same FE will most certainly NOT be that good, even with a move to SQL Server.

If you must support remote users you might consider a Remote Desktop setup, where users log into RDP sessions and run the software. This tends to work well, and requires the least amount of changes to the application. You will need the proper equipment and licensing to do this, of course, and that get a little pricey.

As OllarConsulting mentions, migrating to SQL Server is a good idea, but there can be some growing pains associated. See these articles for information:

http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp (The Best of Both Worlds)
0
hotelguestAuthor Commented:
Thanks to all. Noted from my side and much appreciated. From above posts understood tw main things:
1. I should not start anything new from scratch (something like Oracle project l have no idea about yet).
2. Upsizing of be to Sql is inevitable.
0
hotelguestAuthor Commented:
Thanks to all. Noted from my side and much appreciated. From above posts understood tw main things:
1. I should not start anything new from scratch (something like Oracle project l have no idea about yet).
2. Upsizing of BE to Sql is inevitable. This is better because sounds more realistic (although i have never did it before either=exsiting study ahead).
Few more details:
Im not sure about remote desktop terms, but im loging in remotely via citrix when at home or travelling. Moreover, login  process both from home and from office is identical (for me it means we all are using a kind of remote desktop all the time no matter from where).
Also understand that support of off-line users is problem. To be clear enough we are speaking about the ships sailing at sea and periodicaly uploading some technical data (some of them have internet access, some not. Satcom mail only). Now above data is retyped manually for further analysis. 3rd party software is not a solution: we need at least dozen of specialized soft to cover complete task. Bulky, expensive and not offer what we need exactly.
Thats all what is nice to have from remote sites.

best regards,
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sounds like you're already using Citrix (which is RDP on steroids), so I think you're okay with that. I'd contact the IT people to insure you have the necessary permissions and such to do what you want, of course. Citrix and RDP can be locked down to single applications, for example, and if you build "helper" programs they may not run correctly.
To be clear enough we are speaking about the ships sailing at sea and periodicaly uploading some technical data (some of them have internet access, some not. Satcom mail only).
While I've not dealt with that scenario personally, I think you would be much, much better off exporting the data to an XML file (or flat file, or something of that nature) and transmitting that, and then create routines on your end to move your data into the "master" database. You can use standard checksum methods to insure that your file arrives intact.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hotelguestAuthor Commented:
Thanks. Noted. Clear in general but not in details. But more than enough to start digging. Main thing that l dont need to start anything from scratch.Hopefully, our admin will be able to explain all these Rdp details.
Import/export stuff(second part ofthe question) is more clear. We are using such approach with some sql applications (exchange with export/import batches).
Thanks again. Will mark multiple solution in few hours. (Writing on the go).

Best regards,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.