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.
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.