Solved

Access recordset not updateable

Posted on 2016-11-17
8
57 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
LVL 15

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

820 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