Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Regarding this point in your question ...</p> <p><em>"what happens if two DB users are trying to run the same query, with different criteria, simultaneously?"</em></p> <p>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.</p> <p>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.</p> <p>In that situation, each user can create and run their own custom ad-hoc queries without stomping on other users' queries.</p> <p>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.</p> <p>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.</p> <p>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. </p> <p>For general "search form" suggestions, see what you can re-use from Allen Browne's detailed example: <a href="http://allenbrowne.com/ser-62.html" rel="nofollow">Search criteria</a></p> <hr> <p>It occurs to me you might be able to use a simple approach. </p> <p>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 <code>WHERE</code> clause in that query, limit it to only the conditions which should apply to all search variations ... perhaps <code>Active = True</code>. If you can't identify any conditions which will apply to all searches, don't include a <code>WHERE</code> clause.</p> <p>Then in your search form, evaluate the user's search criteria from the click event of the "Search Now" command button. Build up a <code>WHERE</code> string without the word <code>WHERE</code> and use that as the <em>WhereCondition</em> option to <code>DoCmd.OpenForm</code>.</p> <pre class="lang-vb prettyprint-override"><code>If Not IsNull(Me.txtStartDate) Then strWhere = strWhere = " AND date_field &gt;=" &amp; _ Format(Me.txtStartDate, "\#yyyy-m-d\#") End If If Not IsNull(Me.txtCategory) Then strWhere = strWhere = " AND category =" &amp; Me.txtCategory End If If Len(strWhere) &gt; 0 Then strWhere = Mid(strWhere, 6) ' discard leading " AND " DoCmd.OpenForm "frmSearchResults", WhereCondition:=strWhere Else MsgBox "nothing to search for" End If </code></pre> <p>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 <code>.ItemsSelected</code> property to build a string such as <code>some_field IN ("a", "b", "z")</code>. If you have trouble with that piece, post a new question and we can work through it.</p> <p>Examine Allen Browne's example I linked earlier. As I recall, he used similar techniques.</p>
    singulars
    1. This table or related slice is empty.
    1. POIs there a preferred way to create dynamic, form-based queries in MS Access?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USHansUp
    1. USHansUp
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COVery 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...
      singulars
      1. PO
      1. USPSUlion01
    2. COHi. 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.
      singulars
      1. PO
      1. USHansUp
    3. COBut 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`.
      singulars
      1. PO
      1. USHansUp
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload