Can I create a recordset that is global for each user on a multiuser application?

[gellerche]

This may not be the correct forum for this question. If not, I apologize.

I have a database on a drive that is available to multiple users. I want to do this:

A user selects options on a form and clicks Submit. This creates an SQL Query (via VBScript) that varies in length depending on the options selected by the user. The SQL query then populates a recordset that is global for each user's session (in other words, if two users have different queries, then each of their recordsets will be different, but they can access their recordset from any form or report). The recordset will be used as a datasource for a subform, so the user can interact with each record in the recordset.

I already know how to create the variable length SQL Query depending on options selected and open the form/subform afterwards. What I don't know is (1) how to make a recordset global, (2) how to make this recordset available to the forms/reports in the database, and (3) how to make the recordset so each user has their own (in other words, so one person's query doesn't affect another person's recordset).

Thank you for any help you can provide,

Steve Geller

 

[mrharvey]

Hi Steve,

If I correctly understand the problem, you should be able to use the "New" keyword to create a new instance of a form, then set the RecordSource property of that newly instantiated form object to the parsed SQL statement of your choosing.

Good Luck!

Regards,
Michael

 

[ElsVanMiert]

Recordsets are stored in the computer's RAM, consequently it can only be read on one computer.
You need to store this RS as a temporary table in the BE MDB in order to make it accessible for all users of your application.
Alternatively (and to avoid the bloating of the BE) you could write a CSV or ASCII-file containing the RS data.

 

[The_Doc_Man]

This can only be done by someone with appropriate rights. It also requires serious attention to make sure you don't have bloat or other types of problematic database situations.

When you create a QueryDef, you actually supply the SQL of that definition even if you really used the QBE grid. If the person creating the SQL string already has to run a VBA routine to build the SQL, they can take it one step more in that same code.

NOTE: If you intend to take this approach, the QueryDef must be marked as .Updatable = True or you won't be able to edit the SQL string. See later code for how you do this.

Also, the QueryDef must be in the shared database. If you used a shared back-end but private front-end method, this isn't going to work so well, or maybe even at all. I.e. this idea isn't for split database scenarios.

1. Set the properties of the query to be modifiable by your selected user who builds the query but ...

2. Set it read-only to folks who aren't allowed to change it.

You have to do steps 1 & 2 partly from the Security>>Permissions menu and partly from code. (See later suggested code below.) And you have to do it this way because the alternative, to delete the old query and add a new one LOSES THE PERMISSIONS.

3. Make the query have a fixed and predictable name. Make all of your other "things" that people use depend on this name.

4. When you build the new query string, update the .SQL property that is the actual definition of that query. You have to set aside a time when this will happen, or else you will run into object locking issues. Like, if the query is open read-only when your authorized updater tries to do his thing.... BANG ZOOM.

Now, here is the code we are talking about, assuming you already have the SQL defined. If the string is in strNewSQL and the query is qryGlobalQry, you might add this code at the end of the procedure that creates the SQL.

CurrentDB.QueryDefs("qryGlobalQry").Updatable = True
CurrentDB.QueryDefs("qryGlobalQry").SQL = strNewSQL
CurrentDB.QueryDefs("qryGlobalQry").Updatable = False

The mucking with the .Updatable property means that the query cannot be accidentally changed by any other means. Of course, you would not do the changes to the Updatable property until and unless you are confident that the update process cannot build you an impossible SQL string.

BE WARNED: If your SQL string is malformed, you disenfranchise everyone at the same time. So before you do this, be extremely sure you got it right. Like, be prepared to bet the house, the car, the wife, the dog, and the huntin' rifle on it. (For a Cajun like me, that is the ultimate bet.)

ALSO BE WARNED: Technically, this is somewhat ugly. However, it is one of the few ways I know to make this work correctly in order to generate a global query from a single source.

 

[RichMorrison]

A "global recordset" sounds like a table to me. You can populate a table with the result of an "SQL query". The table can serve as a data source for forms or reports.

The table can be temporary or permanent. If the SQL query returns the same column names whenever run, then a permanent table will work. The application would have to delete the old records and insert/append the new.

If the column names returned vary, then a make table query will work.

The table can be stored on a shared server or on each user's workstation.

Each user can have a separate named table or all user's data can be stored in a common table with a field that identifies the user. That assumes the same column names for all users and all queries.

HTH,
RichM