Lock a query to not edit its underlying table?

[NPeirce]

Good afternoon.

I'm trying to design a system in Access that exports with minimal user interaction/brainpower, but since there are more records to export than Excel 2003 can contain, the user will have to do some copying and pasting from a query.

While the user is doing that, I'd like to make sure they can't accidentally edit the query's data and therefore the underlying table's data.

Is there a way to design a query to stop the query from updating the table?

The solution would have to avoid changing the table to not be updatable because other things need to update it.

In searching these forums for an answer, I've heard the advice to use the DISTINCT statement in SQL for locking the query, but I'm not very good with SQL and I also can't have the side effect of the query having duplicates removed, which I think is the main point of the DISTINCT predicate.

If there's some easy way to get a query with zillions of records to export to XLS, that would be even better, but I'd still like to know for my own knowledge how to stop a query from editing a table.

Thanks.

-Nathan

 

[NPeirce]

I should mention, to make matters worse, I'm using Office 2007 in Vista, but the end user will be using Office 2003 in XP.

If necessary, I can get over to an end user's system occasionally.

-Nathan

 

[dhookom]

One simple method for making a query read-only is to create a table (or query) with only one record. Add this table to your query. Since there is no join on any fields, the records can't be edited.

Duane
Hook'D on Access
MS Access MVP

 

[NPeirce]

Thanks, but the output format is fixed and there are already too many database objects. Is there any other way?

Thanks.

-Nathan

 

[dhookom]

I'm not sure what adding another table to the query has to do with the output. You don't really need another table or query. Add any other table or query to your existing query and filter it to a single record/value.

For instance if you have a table "tblAnyTable" with a primary key field with numbers 1 - 100. Just add the table to the query, drop the primary key field into the grid and set its criteria to the value of a known value. Uncheck the box so it isn't displayed.

Duane
Hook'D on Access
MS Access MVP

 

[NPeirce]

Oh, I see.

Yes, that worked for the simplest queries, but when I try that method on the more complex ones that contain associations tables for many-to-many relationships, then it gives me the "ambiguous outer join" error.

Is there a "read-only" setting for a query?
That would be perfect.

Thanks.

-Nathan

 

[dhookom]

Click the Totals button and justs group by every column.

Duane
Hook'D on Access
MS Access MVP