473,382 Members | 1,479 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

How to prevent query/form from being read-only

beacon
579 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. Linked table -- F551450_USER_ROLES_656_VW
  2.      Field: Option Name
  3.  
Expand|Select|Wrap|Line Numbers
  1. My Table -- User Role Option Dictionary
  2.      Field: Option Name
  3.      Field: Option Description
  4.      Field: Option Path
  5.  
Expand|Select|Wrap|Line Numbers
  1. SELECT query
  2.  
  3. SELECT DISTINCT 
  4.      [User Role Option Dictionary].[Option Name], 
  5.      [User Role Option Dictionary].[Option Description], 
  6.      [User Role Option Dictionary].[Option Path]
  7. FROM 
  8.      [User Role Option Dictionary];
  9.  
Expand|Select|Wrap|Line Numbers
  1. APPEND Query - All Options
  2.  
  3. INSERT INTO 
  4.      [User Role Option Dictionary] ( [Option Name] )
  5. SELECT DISTINCT 
  6.      F551450_USER_ROLES_656_VW.OPTION_DESCRIPTION
  7. FROM 
  8.      F551450_USER_ROLES_656_VW
  9. ORDER BY 
  10.      F551450_USER_ROLES_656_VW.OPTION_DESCRIPTION;
  11.  
Expand|Select|Wrap|Line Numbers
  1. APPEND Query - New Options
  2.  
  3. INSERT INTO 
  4.      [User Role Option Dictionary] ( [Option Name] )
  5. SELECT 
  6.      [User Roles Unmatched].OPTION_DESCRIPTION
  7. FROM 
  8.      [User Roles Unmatched];
  9.  
Expand|Select|Wrap|Line Numbers
  1. UNMATCHED Query
  2.  
  3. SELECT 
  4.      F551450_USER_ROLES_656_VW.OPTION_DESCRIPTION
  5. FROM 
  6.      F551450_USER_ROLES_656_VW 
  7. LEFT JOIN 
  8.      [User Role Option Dictionary] ON F551450_USER_ROLES_656_VW.OPTION_DESCRIPTION = [User Role Option Dictionary].[Option Name]
  9. WHERE 
  10.      ((([User Role Option Dictionary].[Option Name]) Is Null));
  11.  
Thanks for the help...
Sep 4 '09 #1

✓ answered by 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?

11 4066
ChipR
1,287 Expert 1GB
From what I can tell, you are going to have to remove duplicate records from YOUR table. Wouldn't that solve the problem?
Sep 4 '09 #2
NeoPa
32,556 Expert Mod 16PB
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.
Sep 4 '09 #3
beacon
579 512MB
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?
Sep 4 '09 #4
NeoPa
32,556 Expert Mod 16PB
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.
Sep 4 '09 #5
FishVal
2,653 Expert 2GB
Hi, beacon.

Would you like to add index to your table disallowing "duplicate" records instead of filtering them out?
Sep 4 '09 #6
beacon
579 512MB
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.
Sep 4 '09 #7
beacon
579 512MB
@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.
Sep 4 '09 #8
FishVal
2,653 Expert 2GB
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.
Sep 4 '09 #9
NeoPa
32,556 Expert Mod 16PB
@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.
Sep 4 '09 #10
beacon
579 512MB
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.
Sep 7 '09 #11
NeoPa
32,556 Expert Mod 16PB
@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?
Sep 7 '09 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: entoone | last post by:
I have an html form, that has fields and when the submit button is pressed, the information is transferred to a php file. Which then inserts the data into my sql database. How can I go about...
9
by: Felix Wiemann | last post by:
Sometimes (but not always) the __new__ method of one of my classes returns an *existing* instance of the class. However, when it does that, the __init__ method of the existing instance is called...
6
by: P. Emigh | last post by:
By default in more recent versions, Access forms keep users' last sort request in the "orderby" property. That can slow things down considerably, especially when the last user has chosen a...
1
by: Wayne | last post by:
I've created a form that has many option boxes to help users choose items (I need option boxes vs. combo boxes because they can choose multiple items per category). I've created an update query...
6
by: deko | last post by:
Is there a way to set a custom property on Access tables and/or queries to prevent them from being overwritten by import wizards? Any Access database can be easily destroyed if a user mistakenly...
3
by: Damian | last post by:
Hi everyone I'm having an issue with one of our applications. I'm getting the following error when attempting to log in to the site: Server Error in 'xxxxxxxxxxxxxxxx' Application....
2
by: George | last post by:
Dear colleagues, I refer to your help with specific graphic problem. It is necessary to create a viewfinder in graphic application. It seems that the algorithm is simple: just draw lines in...
7
by: Fabio Cannizzo | last post by:
How can I prevent a few lines of code contained in the constructor of a Form from being executed by the Designer? Thanks, Fabio
4
by: Joe-Paul | last post by:
Hi: I'm running a simple query on an Access Table from VB6.0. The operator can make several different selections. Based on their selection, a different, specific SQL needs to be run. So, when...
3
by: Richard Hollenbeck | last post by:
I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.