Access recordset not updateable

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.
LVL 49
Dale FyeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
How many tables in qryFamiliesSelected  ?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
hnasrCommented:
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
 
John TsioumprisSoftware & Systems EngineerCommented:
Maybe a problem with Primary Key on one of your linked tables
0
 
Dale FyeAuthor Commented:
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
 
Dale FyeAuthor Commented:
Jim,

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

From novice to tech pro — start learning today.