upgrading access db

Posted on 2013-08-30
Medium Priority
Last Modified: 2013-09-03
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;
- 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,
Question by:hotelguest
  • 4
  • 2
  • 2

Assisted Solution

OllarConsulting earned 800 total points
ID: 39453025
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.

Author Comment

ID: 39453099
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?

Assisted Solution

OllarConsulting earned 800 total points
ID: 39453343
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.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 86

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 total points
ID: 39454880
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.jstreettech.com/cartgenie/pg_developerDownloads.asp (The Best of Both Worlds)

Author Comment

ID: 39455894
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.

Author Comment

ID: 39455923
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,
LVL 86

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 total points
ID: 39458012
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.

Author Comment

ID: 39458593
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,

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question