3

General question regarding the approach to take with a database I'm constructing. I plan on having a few forms that will allow DB business users to enter criteria, restrict their search via radio buttons, etc. and then run a query based on their selections.

For simple queries, where there's one or two filters, it seems easy enough to associate the data field's criteria to the respective form element in the query design view, but this approach fails (and seems very hard to read/edit) when dealing with nested if statements (e.g. If A then x, if B then y, if C then z, Else q).

I came across an article that described creating a separate table in the DB where one would store query names and corresponding SQL strings, which are created in VBA that runs after an onClick event, or some form-based trigger. The logic for determining the query criteria is all in the VBA, and once the code executes the system is left with a clean SQL statment, which is stored in the aforementioned table and then used to execute the query. I believe that each time the form is accessed/modified and a query requested, the SQL string in the table would be overwritten.

As this is the first serious DB I've worked on, I'm looking for some guidance regarding the approach to take. Is what I just described correct or a fairly standard/common way to handle the situation? Are there any major concerns? The one that immediately jumps to mind is what happens if two DB users are trying to run the same query, with different criteria, simultaneously? It should be noted, my user base is small -- perhaps 5 users that will only be accessing the DB in passing, for ad-hoc reporting and such.

Thanks in advance!

Edit: Here's the forum post that I was referring to re: SQL strings in a separate table

Edit 2: As a general example, let's say my DB has a table as follows:

ID......Sale_Date......Category

1....... 1/1/2013........Foo
2....... 1/3/2013........Bar
3....... 1/1/2013........Bar
4....... 1/7/2013........Bar

Now on my form, I'd have text boxes where the user can specify the date range, and this is filtered on the query as

Between [Forms]![myFrm]![FromDate] And [Forms]![myFrm]![ToDate]

which works just fine.

As for the category, I'd like to have checkboxes or some other form element where the user can specify the categories for inclusion/exclusion. This is where I ran into problems. I tried:

IIf([Forms]![myFrm]![Cat]=1,([tbl_data].[Category]) In ("Foo","Bar"),IIf([Forms]![myFrm]![Cat]=2,"Foo","Bar"))

...with Cat=1 representing 'All' and 2 and 3 representing Foo and Bar respectively. Access gives me an error that the query is too complex, but if I strip out the nested if (thus ignoring the option to search for both categories), it works.

Now, clearly this is a greatly simplified example, but it got me thinking about how to handle the form-driven queries as the DB grows and more canned queries are baked in. The thought with the VBA is that the code would run, evaluate the form data, and then construct the SQL as

SELECT...
FROM...
WHERE IN("Foo", "Bar")

Hopefully this helps clarify things a bit. Please excuse my ignorance... I'm still learning a lot of this as I go along. Thanks.

Improve this question

4 Answers 4

Reset to default
3

Regarding this point in your question ...

"what happens if two DB users are trying to run the same query, with different criteria, simultaneously?"

In a multi-user Access application, you should split the db into front end and back end db files. The BE db should contain the tables, indexes, and relationships. The FE db should include your queries, forms, reports, etc. (basically everything your application needs other than the tables) and links to the BE tables.

Place the BE db file on a network share accessible by all your application's users. Each user should receive their own copy of the FE db.

In that situation, each user can create and run their own custom ad-hoc queries without stomping on other users' queries.

If you need to store query criteria or entire SQL statements somewhere, you can store them in a local table in the FE or store them in a common BE table but include a field, perhaps user ID, so that each user's queries may be stored separately.

Beware that if you decide to store locally for each FE user, their saved settings would risk being discarded when you need to deploy a new FE version. In that case, a better approach is give each user an auxiliary db file where they can store their custom settings. And then you would be able to preserve their settings when you deploy new FE versions.

I found it difficult to get a handle on the "big picture" of your question. But I think you should consider it in the context of a split application.

For general "search form" suggestions, see what you can re-use from Allen Browne's detailed example: Search criteria

It occurs to me you might be able to use a simple approach.

Create a separate form for the search results. Based that form on a query which includes all the fields you want displayed. If you need a WHERE clause in that query, limit it to only the conditions which should apply to all search variations ... perhaps Active = True. If you can't identify any conditions which will apply to all searches, don't include a WHERE clause.

Then in your search form, evaluate the user's search criteria from the click event of the "Search Now" command button. Build up a WHERE string without the word WHERE and use that as the WhereCondition option to DoCmd.OpenForm.

If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere = " AND date_field >=" & _
        Format(Me.txtStartDate, "\#yyyy-m-d\#")
End If
If Not IsNull(Me.txtCategory) Then
    strWhere = strWhere = " AND category =" & Me.txtCategory
End If
If Len(strWhere) > 0 Then
    strWhere = Mid(strWhere, 6) ' discard leading " AND "
    DoCmd.OpenForm "frmSearchResults", WhereCondition:=strWhere
Else
    MsgBox "nothing to search for"
End If

If one of the search criteria will be 1 or more items from a set of choices, present those as a multi-select list box and loop through the list box's .ItemsSelected property to build a string such as some_field IN ("a", "b", "z"). If you have trouble with that piece, post a new question and we can work through it.

Examine Allen Browne's example I linked earlier. As I recall, he used similar techniques.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Very helpful re: FE/BE setup. I'll give the Allen Browne link a read a little later. FYI I added a link to the forum post that described the setup I referenced. As for the big picture, I'm really just looking for the preferred approach to form-driven queries, when the underlying critera selections become more involved (i.e. using case statements rather than nested IFs, criteria B being dependent on Critera A, etc). Seems like VBA is a cleaner approach, but that may just be my lack of Access/SQL familiarity leading me to that conclusion. Sorry if I'm not being clear...
Hi. I don't think it's so much a lack of clarity exactly. Rather the scope of the question is so broad. I skimmed the link you added to the question. But I'm unclear about why you're leaning towards storing anything in a table for this. If you prefer to tackle the search issue by altering a saved QueryDef, then opening the revised QueryDef to display the search results ... you can alter the QueryDef without storing anything in a table.
But when each user has their own FE file, they can each alter their own copy of the QueryDef without affecting the changes other users make to their copies of the same QueryDef.
Perhaps it would help to show us an example of the complex query criteria you have in mind ... so we can see whether or not they might be satisfied with an approach similar to Allen Browne's example.
0

Hi as I see it you can separate the data from the user by using linked table so you won't have to worry about what happens when multiple people use the database at the same time.

As for the research I use something like that.

Set MyDatabase = CurrentDb
strQuote = Chr$(34)
Set MyQuery = MyDatabase.QueryDefs("YourQuery")
I = 0

SQL_String = "SELECT T_Project.YourID, T_Project.Project_Title " & _
             "FROM (T_Project LEFT JOIN T_Groupe ON T_Project.Code_Group = T_Groupe.Code_Group) LEFT JOIN T__Contact ON T_Project.Code_Employee = T__Contact.Code_Contact "

If Not IsNull(Me.R_YourID) Then
    MyCriteria(I, 0) = " YourID "
    MyCriteria(I, 1) = Me.R_YourID
    MyCriteria(I, 2) = "="
    I = I + 1
End If

If Not IsNull(Me.R_Project_Title) Then
    MyCriteria(I, 0) = " Project_Title "
    MyCriteria(I, 1) = " " & strQuote & "*" & Me.R_Project_Title & "*" & strQuote
    MyCriteria(I, 2) = "like"
    I = I + 1
End If

If I <> 0 Then
    SQL_String = SQL_String & " Where " & MyCriteria(0, 0) & MyCriteria(0, 2) & MyCriteria(0, 1)
    j = 1
    Do While j <> I
        SQL_String = SQL_String & " and " & MyCriteria(j, 0) & MyCriteria(j, 2) & MyCriteria(j, 1)
        j = j + 1
    Loop
End If

MyQuery.SQL = SQL_String & " ORDER BY T_Project.YourID " 

Refresh
Forms![Menu_Principal]![List_Project].Requery

Hope This help you

Improve this answer

Comments

0

There is an official Microsoft answer to this: http://support.microsoft.com/kb/304302

Basically it hinges on realising that you only need the word 'where' right before the first filter condition in the query. The subsequent filter conditions, if they are used, will all start with the word 'and'. From there, you can dynamically build the SQL query string with VBA, as per the MS KB article above.

Improve this answer

Comments

0

Keep in mind that this is what Access already is and already does: if you select "filter by form" it throws up a form to help you build a filter for your query or form, and it helps you to automatically build reports.

So you need to identify what it is that you are building that is different and better than what Access has on offer.

Normally, that means that you are going to filter a report, and that you are going to make the filtering simpler by offering only limited options.

You do need to think about the first criteria. Given that you filter queries, and forms, and that you can automatically build reports, maybe your users just need to be pointed at the features which already exist in MS Access.

Since filtering reports is common request, you can use existing code: OpenGate is one example of a commercial report builder: google around and see what others, including free and demo software you can find.

If you do want to make your own filter builder, here's some tips:

1) Don't use form references. If I go to the trouble of a special filter builder, I build the actual data values into the sql, avoiding the problems you had. A reference to a field on a form is what you use when you don't want the complexity of a special filter builder form.

2) You can use a form as a report. Just make the background white and make the layout look like a report. This give you a filterable report. It doesn't work exactly the same as a report: sometimes that is an advantage.

3) Most people don't want to play with their filters. They want a particular query/report setup, and saved, and then they just run that every day/week/month/year.

4) If the user wants to pull out a particular data point, they probably don't need to have it look exactly like a single line from a bigger report. Don't go fancy with the radio buttons on the configuration: just pull up a different version of the form/query/report.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.