How to prevent query/form from being read-only

[beacon]

Hi everybody,

I created a database that links one table from an ODBC data source. I saved my password and UID to the data source so neither myself nor anyone else would have to login each time (that's just bonus background info).

I take this table, which is read-only, and run an APPEND query that adds new items from the table into another table I created (that has two additional fields that will be updated by the user via a form). I also run another APPEND query with an UNMATCHED query to help keep my table in line with the linked table. I have a SELECT query that is based off of my table that I use as the record source for my form.

When I updated the table yesterday, I noticed that the keepers of the database that I linked to had added duplicate records that I don't have any use for. So, I went in and added DISTINCT to the SQL in my SELECT query to remove the duplicates. When I did this, it caused the form and the SELECT query to become read-only. I did some research and found that it's because I used the DISTINCT predicate.

I don't care if the duplicates are there in my query, but I don't want the duplicates to be visible to the user in the form or the report. Is there a way to filter out these duplicates? If not, I need to find a way to remove the duplicates from my query so that the form won't be read-only.

Linked table -- F551450_USER_ROLES_656_VW
     Field: Option Name
 

My Table -- User Role Option Dictionary
     Field: Option Name
     Field: Option Description
     Field: Option Path
 

SELECT query
 
SELECT DISTINCT 
     [User Role Option Dictionary].[Option Name], 
     [User Role Option Dictionary].[Option Description], 
     [User Role Option Dictionary].[Option Path]
FROM 
     [User Role Option Dictionary];
 

APPEND Query - All Options
 
INSERT INTO 
     [User Role Option Dictionary] ( [Option Name] )
SELECT DISTINCT 
     F551450_USER_ROLES_656_VW.OPTION_DESCRIPTION
FROM 
     F551450_USER_ROLES_656_VW
ORDER BY 
     F551450_USER_ROLES_656_VW.OPTION_DESCRIPTION;
 

APPEND Query - New Options
 
INSERT INTO 
     [User Role Option Dictionary] ( [Option Name] )
SELECT 
     [User Roles Unmatched].OPTION_DESCRIPTION
FROM 
     [User Roles Unmatched];
 

UNMATCHED Query
 
SELECT 
     F551450_USER_ROLES_656_VW.OPTION_DESCRIPTION
FROM 
     F551450_USER_ROLES_656_VW 
LEFT JOIN 
     [User Role Option Dictionary] ON F551450_USER_ROLES_656_VW.OPTION_DESCRIPTION = [User Role Option Dictionary].[Option Name]
WHERE 
     ((([User Role Option Dictionary].[Option Name]) Is Null));
 

Thanks for the help...

[ChipR]

From what I can tell, you are going to have to remove duplicate records from YOUR table. Wouldn't that solve the problem?

[NeoPa]

You don't explain what duplicate means in this context.

Is it a duplicate key or a full duplicate record?
Is there any way of determining which record to select when there are duplicates in the source?

Without this info it's hard to proceed.

You can certainly take the remove after append approach suggested above, but this may not be necessary, depending on aspects of the situation we have no knowledge of.

[beacon]

Let me see if I can answer your questions NeoPa.

On the linked table, I'm importing each of the four fields in the table - Facility, User Role, Option Description, and DSS. However, I'm only interested in the User Role field, which is what is being checked when I append (Please disregard the naming conventions in this table because they conflict with the naming conventions in my table...when I say User Role field in the linked table, I mean the Option Name field in all other tables/queries).

As far as I can tell, there is no primary key for the linked table. My table does have a primary key called ID as autonumber. Each ID gets associated with a record containing Facility, User Role, Option Description, and DSS from the linked table.

Because I'm only interested in the User Role field in the linked table, it doesn't matter to me which record out of the duplicates is selected as long as one, and only one are selected.

In my table, the User Role field becomes the Option Name field, and I added the Option Description and Option Path fields for the user to fill in. I guess in my original post I should have mentioned that having the User Role/Option Name field be read-only is okay because I want it to be consistent with the linked table, but the two remaining tables must be read/write, otherwise the database is useless.

Does that help out any?

[NeoPa]

I'm a little confused as none of your posted SQL seems to read from a field [User Role]. Nor, is any table referred to as the Linked Table. That may be buried in the explanation somewhere, but I don't really have the time to dig through everything on all the threads I'm working on.

Having said that, I do think I have a better understanding now than before, and unless I'm mistaken, you should be looking at appending only (one of) those items that don't already exist in the destination table (whichever that is). I suspect the DISTINCT predicate will fit happily into such a query without causing problems. After that, you won't need to use either the DISTINCT predicate OR the GROUP BY clause to review or amend your records.

I hope that's helpful.

[FishVal]

Hi, beacon.

Would you like to add index to your table disallowing "duplicate" records instead of filtering them out?

[beacon]

Sorry for the confusion, but yes the explanation was in the original post. I should have been more considerate of the fact that you have multiple threads to address and kept my post more concise.

That being said, I only append the one field (that I mistakenly was calling User Role, but I noticed is actually called Option Description, further compounding the confusion) that I need from the linked table with the Append - New Options query.

So, the course of events is:
(1) Open DB
(2) AutoExec Unmatched Query that examines linked table with my table
(3) Append unmatched to my table

If I open my table, I can make modifications to it, but if I open the query, which my form also happens to be based on and that I've included the DISTINCT predicate in, it won't let me type in the two fields that I need to update (Description and Path).

To hopefully undo some of the confusion in the SQL, F551450_USER_ROLES_656_VW.OPTION_DESCRIPTION is the equivalent field in the linked table to [User Role Option Dictionary].[Option Name] in my table and are the fields in question.

If I remove the DISTINCT predicate I can type in the two fields, either in the query or the form (which happens to be continuous, just for good measure). As soon as I add DISTINCT to remove the newly added duplicate records in the linked table, that's when everything becomes read-only.

[beacon]

@FishVal
I have an index in my table that is an autonumber. If I change my index from autonumber to be the Option Name, where the duplicate records are, will I receive prompts each time the Unmatched query finds a duplicate and tries to append it?

This sounds like it might be my best bet if it's not going to prompt the user during the append process.

[FishVal]

It is not going to prompt a user if you disable it using DoCmd.SetWarnings method.
And, there is no need to change any index, just add a new one.

[NeoPa]

@beacon
The idea is never to add duplicates into your table, thereby removing the need to include the DISTINCT predicate.

Fish's idea of an index is still possible, as each field can have an index set. I would suggest though, that it is preferable not to even try to add the duplicates in the first place. Your append query can simply ignore duplicates, and even select only items from the linked table which have no matches in the destination table. This makes what you're doing clearer. Always a bonus for maintenance.

[beacon]

NeoPa,

I'm not sure how to setup my append query to ignore duplicates. I know about the find matches query, but not exactly sure how I would use this to ignore the duplicates.

I think, in a roundabout way, is probably what my question should have been from the get-go.

[NeoPa]

@beacon
That would be what the DISTINCT predicate of the SELECT clause does for you.

Alternatively, if you look at the properties pane of the query in design-view, there are options to select only Unique Values & Unique Records (That would be the DISTINCTROW predicate instead of course).

Does that make it a bit clearer?