SQL table

[mjdemaris]

Hey all,

I would like to create a table full of SQL strings.

tblSQL
ID
StringSQL

and use Enums to incorporate them into the code. I have pages of SQL in VBA in my first database, and would like to make it easier to save, edit, use the SQL.

Ideas?

 

[plog]

and would like to make it easier to save, edit, use the SQL.

So, you are reinventing Access with Access itself? You just blew my mind.

At first blush this sounds like a bad idea. Could you explain what these SQL statements are? Just SELECT statements? Or action queries (INSERT, UPDATE, DELETE)? How/why would you modify them?

 

[mjdemaris]

I've been looking at the Northwind db, and they use an enum list with values in a table full of string messages.

For my main search page, there are options to select which field to search in, which warehouse to search in, and which group of data to look at. So I am using form scoped variables to capture that info, and when the user starts to type into the search text box, there are a dozen or more SQL strings selected by the Select Case statement, depending on the variables' values, i.e. Search111, 112, 113, etc.

And as I make changes to suit the users' needs, I often need to modify the SQL. So, I figured that using a table to store the SQL strings might help make it easier.

Just a thought. I'm still learning here, plog. Please explain what you mean by reinventing Access.

 

[plog]

Access is a database tool that lets you easily save and edit SQL queries--via the Design View of Query Objects. Storing SQL in a table bypasses that great tool that is easy to use.

When you want to let users customize criteria of query, you usually do so via filters. You can apply filters to both forms and reports. So what you do instead of your method is you make a generic query, base a report/form on that generic query adn then when user's select criteria you build a filter string and apply it when you open that report/form.

For example, let's say you had an employee directory database and you wanted to let people open up just one department. You would build a query on all the necessary tables (e.g. Departments, Employees, EmployeeContactInfo, etc.). That query would include everyone and everything--no criteria. You would then build a report on that query which when opened by default would show everyone. Next you build a search form, on it is a drop down that lists every department and a button. The user would select a department and click the button, in the code on the button you would build a filter string and then use DoCmd.OpenReport (https://msdn.microsoft.com/en-us/library/office/ff192676.aspx?f=255&MSPPError=-2147217396) to open the report and apply the filter. The report would open up and show just the department they requested.