Upsize from Access to?

[neelsfer]

I have an application that is used to collect medical records and it consists of a few 100k records already. It works great, thanks to the great assistance i have had from Neopa, Adezi and the other clever guys.

My question: in the next year i will have to roll out to many more sites and all the data collected will be exported at site, and imported onto a central server, with perhaps +-10 persons having access to the data, via a local network.
Should i just upgrade the Backend database to a sql server, or does the front-end currently also in Access 2007, needs to be rewritten in another "language" to prevent an early death of my "applicatio n"?

My worry is that Access will not be able to deal with millions of records.

Any suggestions please?

[zmbd]

The split database will be good for good while -the back-end will handle 2GB of data Access 2007 specifications. As to how many records this will hold, that depends on the number of tables and links and the like.... I had a link to site that had some rough numbers, lost it, I'll see if I can find it later today.

With that said, up-sizing to something like SQL-Server with a split database will involve some pain; however, not as much as you would expect: Move Access data to a SQL Server database by using the Up-sizing Wizard

Mind you this only scratches the surface and you're not stuck using SQL-Server, you could conceivably move the back-end to an Oracle server, just a few tweaks to the ODBC drivers and connections within the code - and you'll have plenty of help here no matter which way you go for the backend.

[neelsfer]

Thx zmbd. i have been thinking what would be the best software to learn from scratch, when i rewrite the FE - ie VB.net or C++ or whatever, and then obviously the BE to link to. Migration of the current data from Access to "whatever" is important however.
Any suggestions?

[NeoPa]

Changing the BE to almost any grown-up database server should be straightforward enough. Changing the FE however, is another matter entirely. Access isn't as popular as it is for nothing. The effort involved in reproducing the logic in another language and without all the bells and whistles built in as you have with Access, will be a lot more than starting Access from scratch. I would urge serious caution before taking any such step. Possibly get to know what you're dealing with inside a few other, less important, projects first. You will probably find you need a great deal of work just to provide the basics that you're used to having available when working within Access.

[NeoPa]

As luck would have it, there's a new thread which discusses the difficulties of switching from VB6 to VB.NET (Going from VB6 to VB.Net is hard), which is a bit like half the jump you're considering with the FE change ;-)

[Taaner]

We have no problem with 10+ users on Access applications, but to avoid problems we always copy the FE to local drive of the user. I think this is a must-do in this case.

This week I noticed an application with a BE of +1 GB and it works smoothly over the LAN. But we have performance issues with an application with a BE of only 40 MB. It depends on how the application and data is designed, and how intense the use of the data is.

[neelsfer]

Thx for the comments its appreciated.

The current application is a pure data collection tool with basically one main form and subform to capture data, as well as a number of queries and reports to display what information is required.

Medicines issued to patients,are captured onto the system in the rural areas here in South Africa, where no networks (or internet) exists.
The data is then exported onto a memory stick and imported into the "main Access server ", at the regional office.
Its straight forward and uncomplicated, but it will expand to millions of records eventually when it is rolled out.

Should i not perhaps start over using different software from scratch again? I can still change over, as the "pilot period" has been successfully completed using my trusted old friend, Access. Decisions now has to be made, taking the future into account. Is Access the solution, or what would you do?

[zmbd]

neelsfer,
You missed the point.
Keep the MS Access front end for so long as it serves you.

So long as the backend being ACCDB or MDB more than likely 2GB at the local level will surfice for years - especially if the records are purged or archived after so many years... that depending on your medical requirments. I have one such database at work with 400MB of data that has a ton of entries, I'll take a look this morning and edit the number records here....><

However, your main database may eventually need to move to a "grown-up" server. Your MS Access front end will be just fine with most of the grown up servers acting as the backend, just an adjustment here and there to connect.

Now I know I've simplified things a tad; however, even NeoPa as made this same type of comment in post #4

[NeoPa]

Neels:
I can still change over, as the "pilot period" has been successfully completed using my trusted old friend, Access.

I would suggest you wouldn't win too much trust with your client (or employer) if you completed the pilot with Access then looked elsewhere for the main project. They would be expecting a much, much quicker turnaround if they've been used to Access, than they could ever possibly get using other resources. One of Access' main claims to fame is that it's by far the foremost RADS (Rapid Application Development System) for db work.

[zmbd]

I just pulled one of the older databases at work:
- It is used daily. In the past this was one of the main dataentry db for the lab.
- It is not normalized (no one working on db knew anything then about good database design. Surprised this wasn't done in excel... oh, yea, it was! Then someone needed a report... and guess what... excel isn't a database!)
- The records go back to 1997 in 6 of the 25 tables.
- Adding the total number of records in all of the tables is close to 90,000 (yes, ninety-thousand) most of the tables have around 15 fields, some more, some less, average is 15 with a mix of text fields (all at defaults I'm sure) and numeric.
- The size of the database is 54MB - This is an all in one database with three forms and barely any VBA and these forms are only because I added them due to a request to make the data entry easier!

Now, as the TV ads will tell you in the infomercials... your experience may vary, I hope that the performance of this poorly designed database will give you some confidence in what you have done with your project with the benefit of knowledge that those working on this ancient db didn't have in house have back in '97!

[neelsfer]

Thx Zmbd. I currently have about 130 000 records in one of the main tables and it runs 100% on my main network. I will use the above suggestions. Thx to all the contributors