Setting a Query or Querydef to Snapshot Recordset Type

[billschu]

Hi,

I have an application where users can query my database through linked tables. The SQL for the queries are dynamically built in the interface when the user selects the report and various criteria, and then the query is created and opened. I didn't build a bunch of forms because there are many different queries and it is a lot simpler to just display the query.

The problem is, I recently noticed that a user could update the data in the query and this actually updates the source table also. I was pretty shocked as I had no idea you could do this through a query. I found out that if I set the query's Recordset Type property from Dynamic to Snapshot then it will prevent the data from being updated - acts kind of like locking a textbox in a form.

I can't quite figure out how to modify this property through code, since Dynamic seems to be the default.

Here is what I have so far, which didn't work:

Dim qdf1 As QueryDef
Dim prp As Property

Set qdf1 = CurrentDb.QueryDefs("Job Performance")

Set prp = qdf1.CreateProperty("Recordset Type", dbText)
prp.Value = "Snapshot"
qdf1.Properties.Append prp

Thanks,
Bill

 

[Remou]

Hi
I am not exactly sure what it is you wish to do, but here are a couple of thoughts:

[tt]DoCmd.OpenQuery "Query1", , acReadOnly[/tt]

[tt]Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)[/tt]

 

[PHV]

To make a query not updatable simply add the DISTINCT predicate or use an outer join.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[billschu]

The acReadOnly is exactly what I needed. Seems so obvious now!

Thanks,
Bill