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.

  • call stored queries – user96226 Oct 22 '14 at 12:46

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

| improve this answer | |
  • That was the original intent of this project. It has "temporary" written all over it and its doc but we never have budget (or time) allocated to it so it's a huge mess now. As for the queries, they are mostly listboxes with the content of the tables on the main database (for example a list of departments). I think they did this because they wanted to make sure that the database would never lock because of concurrent access but it's making the code that much more heavy. – ApplePie May 16 '13 at 2:12
  • If you can get a SQL server installed, the Access "upsize wizard" will go a long way to making the back-end work better. – DougM May 16 '13 at 2:19
  • And if you use a SQL Server backend, you can also make the queries into stored procs. – HLGEM May 16 '13 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.