Upsize Access database?

March 10, 2009 1:39 PM

I have been asked to make a proposal to upsize an Access database to allow for a few more concurrent users (currently 10) and ideally improve the overall performance of the system. Am I getting in over my head?

To give an idea as to the size of the project the current system has 35 tables, and on the order of 1000 queries and 1500 macros defined. Not even the original developer (the client) knows which ones are actually in use. There are on the order of 200,000 records in the current live system. The client is not willing to spend the money to have the system completely re-built (which I would estimate at being at 3-6 months’ work) but they would like some input as to how the current system could be improved, and they are probably willing to spend enough to cover two to three weeks of my time.

I have done some tests, successfully upsizing the tables to MySQL, but even a single insert from one of the simpler entry forms fails. I figure that with my basic knowledge of SQL that I could probably get a lot of it working (if not very efficiently), but would MS SQL be a better bet for compatibility and performance issues? I have a fair amount of experience with Access, having built customer management and billing systems from scratch, but am no SQL/RDBMS guru. I could use this work, but am I doomed to fail?

posted by cbrody to technology (11 answers total)

I'm just going to throw this out here: SSMA for Access.
posted by tracert at 2:08 PM on March 10, 2009

So basically your job will be to tweak the db to improve performance, nothing else? Even if this is a high risk project, you could probably pull in of in 2-3 weeks if you got the know-how and experience.

But migrating to SQL Server? No, don't do this. Using unfamiliar tech is one of the classic reasons why IT projects fail. You will never have the time to migrate the system and learn enough SQL server to pull it of.

Instead, do the minimum amount of work to cover the requirements. Nothing else. When you're done, explain to the client why their system is doomed and, if possible, suggest that you can build a solution made of of-the-shelf products. It will be cheap and support their business requirements in a much better way than the crap they're using right now.

Also, details about what the system actually does would be very useful :)
posted by Foci for Analysis at 2:15 PM on March 10, 2009

Hmm, if only there were a SQL and database performance expert you could sub-contract. Maybe even one who answers lots of SQL and database questions on askMefi. Where could such a person be found?

But to answer your question: yes, either MySQL or MS SQL Server would be better, as they better support multiple users (in several ways).

Yes, you could use the OBDC drivers to connect the existing Access app to the new database, thus preserving the current investment in Access forms and widgets. MS SQL won't be much more compatible than MySQL, and you should strive to use ANSI Standard SQL anyway. I wouldn't insist that teh client use one or the other, but I'd point out to him that MySQL is cheaper.

You say that your MySQL insert fails, but you don't say with what error, or what the subsequent "show warnings" showed.

1000 queries? Even if some of these are views, I'm guessing that somebody didn't know to about stored queries or other forms of variable replacement; in truth, you probably have only on the order of 100 queries that are interestingly different.

200,000 records is small. Not tiny, but small. 10 users is tiny. 35 tables is small. You should be able to make this work pretty trivially easily.

What can be optimized? Ask users what's slow, then run a show query and see what's wrong.

Finally, yes, if you can't answer these questions about databse optimization, you're in over your head. To me, guessing based on your description of a system I haven't seen, this would likely be a three to six day job, and mostly cut and dry.
posted by orthogonality at 2:19 PM on March 10, 2009

Before you go to far with MySQL, make sure you understand the license. In particular:

Distributors of commercial products that combine GPL-licensed MySQL software with commercially licensed software (i.e., software not licensed under a FOSS license) must comply with the terms of the GPL. This includes use and distribution of the GPL-licensed MySQL database server and MySQL Client Libraries. The FOSS License Exception does not apply with respect to products licensed under any license other than the FOSS licenses listed in the section above titled “FOSS License List.”

If I'm reading that correctly, you can only use MySQL server/client libraries in your commercial project if the commercial project itself is open source (whether GPL, BSD, Artistic, etc). If you are selling a closed source software product to your client, then you must either buy a MySQL license, or choose another database vendor.
posted by sbutler at 2:21 PM on March 10, 2009

sbutler: the key word is distributors. I suspect this ain't a distributed project, given that it supports only 10 users, but an in-house app. (Of coure I could be wrong. Maybe the client sells this.) And the OP would be doing work for hire, not distributing software. Indeed, in a scenario where he's telling the client to download MySQL and then run his database-building script, the only MySQL distributor is MySQL AB (or I guess Sun, now).
posted by orthogonality at 2:30 PM on March 10, 2009

orthogonality is right, this is an in-house app, and I don't think there are any plans to sell it as it's completely customised to their own use. The client is a real-estate broker and the application is used to manage their property, landlords, tenants and prospects and to keep records of all financial transactions. I'm quite tempted to subcontract (if I get the job), but also like the idea of getting my hands dirty with this project.
posted by cbrody at 2:57 PM on March 10, 2009

Agree- isn't the whole point of a SQL server that it doesn't matter what the front end is, because the backend and the frontend are separate from eachother?
posted by gjc at 3:02 PM on March 10, 2009

gjc writes "isn't the whole point of a SQL server that it doesn't matter what the front end is, because the backend and the frontend are separate from eachother?"

Yeah, until you get into middle tier. The idea being, let's have these "business objects" that get data fom a "dumb" database, and transform them acording to our "business rules" before sending them to the front end. And have the front end communicate data to the middle tier, which will transform it and store it in the dumb database.

And then you don't have to care what the dumb database is, or even if there is one or many of them.

That's the theory, and the theory has merit. In practice, you get "Enterprise Java" (J2EE), and a middle tier that does nothing except the wholly trivial.
posted by orthogonality at 3:30 PM on March 10, 2009

You can't answer this question until you know how tied they are to any Access-only (or MS-only) features or functions. Do they use forms? If even one of them needs a form, you're done. Three weeks simply isn't nearly enough time to handle all the tweaking and bullshit that comes along with Access Forms. But if you aren't choking on the MS teet you might have some limited options with regards to a database overhaul. 30 tables and 200,000 records is nothing. Of course, that's if you know what you're doing already. If you don't, and you are instead asking for advice from random folks on the internet… well, you might want to stick with something simpler.
posted by Civil_Disobedient at 5:28 PM on March 10, 2009

@Civil_Disobedient: The whole application is built around Access forms (c.850 defined, of which about 15 provide the primary interface) and reports (c.80). There's no question of moving to another front-end at this stage. And you aren't just random folks, you're Mefites!
posted by cbrody at 6:22 PM on March 10, 2009

Is your Access application already split into a frontend / backend arrangement (ie, all tables in the backend, all forms, queries, macros, modules, reports in the frontend)?

If so, upsizing the backend to another, more 'enterprisey' data store shouldn't be too onerous. Access has a built-in wizard to do this targeting MSSQL, which (at least in my experience) generally doesn't cause too many problems along the way.

Ideally, you'd also convert your view queries into server views, and some or all of your backend business logic into stored procedures, but steps 1 to 3 would be:

1. Get the tables into your server datastore
2. Relink your tables back into your frontend Access application
3. Remediate any issues caused by performing steps 1 and 2

From there, push development where it makes the most sense. Use the server when the work is data (updates, inserts, deletes etc), use the frontend where the work is presentation or user experience.

One suggestion: make a copy of your Access databases, push a couple of tables up to your data store, relink into your development copy of your application, check to see how much pain, if any, these steps have performed. If little or none, go ahead and do the same for your remaining tables.
posted by planetthoughtful at 11:46 PM on March 11, 2009