Solved

Access recordset not updateable

Posted on 2016-11-17
8
46 Views
Last Modified: 2016-11-18
OK, this is strange (A2007)

I have a form which has a recordsource of query (qry_frm_Families).  I define the SQL of this query at runtime based upon several criteria entered on menu form, but the SQL of this query looks like either:

SELECT * FROM qryFamiliesSelected        <= this one has a couple of field criteria
or
SELECT * FROM qryFamiliesSelectedAC   <= this one has some additional criteria

For some reason, this form/query indicatwa that the Recordset is not updateable.  However, if I simply open either qryFamiliesSelected or qryFamiliesSelectedAC, or set the RecordSource of the form to either of the queries mentioned above, the form/recordset is updateable.

There are no aggregations, DISTINCT, DISTINCTROW, or anything in this query.  I even went so far as to explicitly enumerate all of the fields instead of using the * in the SELECT statement.

To be fair, the BE for this database is SQL Server, and the tables used in these queries are all linked tables.  But I cannot figure out why the source queries are editable, but a query based upon them, where all fields are selected and no additional criteria are defined is not.
0
Comment
Question by:Dale Fye (Access MVP)
8 Comments
 
LVL 75
ID: 41892433
How many tables in qryFamiliesSelected  ?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41892439
>There are no aggregations, DISTINCT, DISTINCTROW, or anything in this query.
How many JOINs?  Last I remember there is a list somewhere searchable of what makes an Access query updateable or not, and one of the criteria was something like three or more JOINs, or maybe any JOIN with a one to many relationship.
0
 
LVL 75
ID: 41892440
If you have more than one table (aka joins), then you probably need to set the following on the Form as shown below:

Recordset
0
 
LVL 30

Expert Comment

by:hnasr
ID: 41892474
May you supply the necessary script to recreate the issue including a simple database that access the sql tables. I was unable to recreate the issue with my current setting.
I have SQL table A, linked to Access A table. A query A_q(SELECT * FROM A) and a form A_f created based on A. The form's recordsource cleared.
A_f recordsource is added at tuntime in Open Event.
Table A is updateable.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41892542
Maybe a problem with Primary Key on one of your linked tables
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 41892773
Dale,

 Do the tables have a TimeStamp column or no?   Also, have you included only the primary key of the table being updated in the queries output?  Is any of this based on a view?

 I think Joe has your answer since the queries are fine outside of the form itself.

 There are a few reasons why a query might not be updateable, but none really seem to apply here.

Jim.
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 41892803
Thanks, Jim.

Turns out there is an Aggregate view used in the query.  I thought it was a table, but the original developer did not preface her views with a "vw_" to indicate I was dealing with a view.  I'm going to have to modify the way the user selects the appropriate record for this form.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 41892831
Jim,

Also had to do with one of the tables in the query not having a TimeStamp column.  Thanks, again, buddy!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how the fundamental information of how to create a table.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now