Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

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.
0
Dale Fye
Asked:
Dale Fye
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Jim Dettman (Microsoft MVP/ EE MVE)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
 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now