Recordset Type

[LB79]

Hello,

I have a list box with SQL as its row source.
I keep editing the properties of this SQL (in qry editor), to show Recordset Type Snapshot, but for some reason if I close then open it again, its set back to Dynaset.
Does anyone know whats going on?

Thanks

 

[DCrake]

Silly question, but one I have never attempted to change, but does it make any differrence whether it's a snapshot or a dynaset?

Addition:
Had a quick play and if the rowsource is pure SQL it cannot apply the recordset type. If you save the query as a query and refer to the query by name in the rowsource then the recordset type will remain as per setting.

 

[LB79]

Thanks DCrake.
I wanted to set it to Snapshot so that multiple users could view the data at the same time. I guess ill have to create a seperate query and link it.

 

[DCrake]

If you are simply populating the contents of a list box with the underlying query then I can't see no reason for the snapshot type.

Or are you thinking that user A has the listbox open on the screen and User B changes something that would affect the underlying rowsource results. And such changes are (not) immediately reflected in the listbox.

 

[LB79]

It seems to be that if User A has a form open with a lstbox populated by a query), then if User B tries to open the same form (from a copy of the database), it hangs until user A closes the form. The underlaying tables are in a master database and using snapshot seems to ease this problem.

 

[Banana]

Snapshot and Dynaset has nothing to do with controlling concurrency - users will be able to view data equally fine. It's the locking aspect that determines how user can view and edit data. But what snapshot and dynaset actually means is that with a snapshot, you are downloading a full set... as if you were taking a snapshot. Once it is downloaded, there is no further communication with the source of that recordset. This usually is a good candidate for binding to comboboxes, listboxes, and report. Dynaset, on the other hand, just downloads the keys (usually primary keys or unique indexes but for a badly designed query or table, as many columns needed to uniquely identify a row in the resultset), and fetch the actual rows only when you navigate to those rows. This is good for forms since you can be sure you're getting the most current version of the row as you navigate through the rows and if set up correctly, you can see others' edits (but not unfinished edits).

But either has nothing to do with whether one is allowed to view other's edits, when it can be viewed, and when one can edit without conflicts. This is determined in LockType and Options property.


I wonder if you split your database? Users should not be sharing the same file.

 

[DCrake]

Just a minute, you are using a FE/BE setup aren't you?

 

[LB79]

Thanks for the details.
Ive split to FE/BE yes with master tables in the BE.
When I first had this problem I looked through all the options and found Snapshot. when I used it, it solved my "more then 1 person viewing" problem. Perhaps a fluke?
I only need multiple people to view it - not edit the data.