Enterprise application with lots of SQL queries

Question

Context:

I'm working on a fairly large Access database (which I inherited) that has its interface and tables separated. In order to make this work together, we have a lot of SQL queries (dozens). Some pointing to that database and some pointing to our datamarts. The code at this moment is somewhere around 12k-14k lines which is not huge by software development standard but not so short that I can just rewrite it. Additionally, that database is fed by a form which is distributed to all our internal clients.

My question:

I'm wondering what's the best way to deal with this. I'd like to know if there is a better way to manage all those queries than to have them in code in string variables. I'm sure it's possible to use files but then we have to distribute those files and that can't happen (i.e. I don't want users to have SQL queries wide open to them).

Is there some clean (in terms of design) way to manage SQL queries that still has a decent amount of security (access to those queries) ?

Just to be clear, we are using Access 2007 with VBA.

Answer 1

It looks like you're using an Access-to-Access setup, with the forms, reports, and queries in one *.accdb file and the tables themselves off in another.

If you want better control of the queries, move them -- ALL of them -- into the back-end accdb. The queries belong with the tables, and the only exceptions should be exceptional, with clear and distinct reasons to not be on the back-end.

Were it my project, I would plan on a migration away from Access-to-Access over to something like Access-to-SQL (or even Access-to-SharePoint) and then, if appropriate, you can slowly introduce other front-ends to supplant access, such as C#-to-SQL or Web-to-SQL.

Access is a desktop context, not a web context, and if you don't have to pretend to be a website you shouldn't try and make it so. I've seen excellent things done regarding direct SQL server connections. Not the least of which is that you can use integrated security and per-object permissions on the server level.

(And that "if appropriate" is important. If access works and fits your current and expected scale, it's not worth abandoning just to have a "real" program.)