Defaulting to SNAPSHOT recordset for Linked Tables

[Catmando]

Hi. We operate in a data warehouse environment and we have some users who
use Access and link tables.

We would like to have it so Access would not default to the Dynaset record
set because most (if not all) of the tables they will use will be read-only
for them. We don't need the ability to update or add to those.

I know I can edit the properties of each query to change the recordset, but
that seems tedious and prone to error.

I also found this Article#292783 which describes a method to kind of do what
I want but the problem is that when I use this method, it doesn't default the
query to be Snapshot, it just disables update/inserts into the table (even
when primary key is specified).

Anyone have other ideas?

 

[Mary Chipman [MSFT]]

Instead of using linked tables, use pass-through queries which always
return read-only result sets. You can base forms and reports on them
as well as manipulate them programmatically using a DAO QueryDef
object.

--Mary

 

[Catmando]

Mary, Thanks for the response. That wouldn't work real well in our
environment because we have essentially "open" access to our database (1000+
analysts). The reason some use it is because of their familiarity with
Access query design tools (they dont' write SQL nor would they be able to
maintain it if they cut and paste). Anyway, most of the analysts do
write/use SQL and other tools so I was just looking to see if I could solve
this problem for the few dozen Access users we have.

However, your idea may be a good one for other more traditional shops.

 

[Rick Brandt]

Mary, Thanks for the response. That wouldn't work real well in our
environment because we have essentially "open" access to our database
(1000+ analysts). The reason some use it is because of their
familiarity with Access query design tools (they dont' write SQL nor
would they be able to maintain it if they cut and paste). Anyway, most
of the analysts do write/use SQL and other tools so I was just looking
to see if I could solve this problem for the few dozen Access users we
have.

However, your idea may be a good one for other more traditional shops.

Mary wasn't suggesting that your users write passthrough queries. She
was suggesting that YOU put one passthrough query per table in the
database instead of links. The users then use those queries as if they
were tables.

The SQL of each query would simply be...

SELECT * FROM TableName


I think you would find though that using passthroughs as the inputs to
local queries might perform badly if joins are involved.

Since this is a server back end (you didn't really say that but "data
warehouse" implies it), why don't you just deny update, insert, and
delete permissions on the server? Then the links will be read only. You
could also create read-only views on the server and link to those instead
of the tables.

Data warehouses are typically read-only anyway except for the process
that populates them.

 

[Catmando]

BTW: We solved the underlying problem. Oracle 10g switched how it handles
the NUMBER(38,0) data type which was causing a function to be written around
the primary key and invalidating the index.

That was solved by enabling a workaround in the Workarounds tab of the
10.2.0.3 client (or higher).