Store large text resource in VBA project

Question

In a VBA project, I have a large SQL query that I retrieve into a string variable to form part of an operation to retrieve data from a remote database.

At present, I am storing this in plain text in a file, and I retrieve it from that file when needed.

Whilst this makes development/updates easier because I can readily edit the query in the file, it does present an exposure because a user could edit the file, possibly maliciously (limited opportunity for damage, but an unacceptable security exposure nonetheless).

The options to mitigate this risk that I am considering/have considered are:

• store the text inside the project, as a global variable. Good because it's not exposed to the user, but unfortunately this means every time I need to make changes to the query, I have to transform the query into chunks, escape quotes, use line continuation, add line feeds and concatenate each chunk together. Yuk.
• store the text in a module wrapped in directives not to compile it, then use VBE methods to retrieve and parse the text in from the module lines. Better than above but requires VBE to be enabled by end-users. That counts this option out.
• keep the text in an external file, but obfuscate it (e.g. Base64 encode it). Adds some (acceptable) overhead for edits to the file, but requires a base64 decoding method in the project (does VBA have one?).
• as above but use encryption of some kind - again, needs a decrypting method in the project.
• store the text in a securable file-based format (eg .mdb or .accdb) and code credentials into the Visio project to retrieve the text.

Any recommendations or experiences? Have I missed some straightforward option that VBA offers?

Answer 1

May I suggest another solution? Keep in the "very hidden" sheet.

Copy the SQL query to a sheet (one row, or many, up to you); then go to the VBA (macro editor, IDE, however you call it) and in the project menu select the sheet; finally in the properties window (press F4 if you cannot see it), select:

Visible = 2 (xlSheetVeryHidden)

That way a user will not see the sheet and the code, and as long as you protect the project with a password, the user will not be able to access it.

Answer 2

Why not store the query string in the remote database and retrieve it when you need it? You can maintain the string in that database and use the database [read-only] protection to prevent ordinary users from altering it.

The table could look like TABLE QUERYSTRINGS (QueryName, QueryString).

The query could be "SELECT QueryString FROM QUERYSTRINGS WHERE QueryName='MyQuery';"