Solved

Access recordset not updateable

Posted on 2016-11-17
8
38 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

759 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

19 Experts available now in Live!

Get 1:1 Help Now