Linked SQL tables, Access front end, subforms - what am I missing?!? (1 Viewer)

spudchick

New member
Local time
Today, 18:42
Joined
Oct 30, 2015
Messages
6
I feel ridiculous having to ask this question but I've been struggling with it for too long now and have tried/changed so many things I feel like I'm lost in the woods.

I am working with SQL2014 for the first time. Our shop is on Access 2010. The back end databases have been set up, all boolean fields are set with defaults, rowversion/timestamp fields have been added (programmatically created as rowversion but for some reason showing as timestamp). The SQL databases are v120 (2014). I'm using ODBC Driver 11 for SQL Server. The clients are Win7 Pro or Enterprise, 64-bit. Office is 32-bit.

The SQL project uses a main utility db containing user credentials/configurations and lookup tables/configurations shared by all localities. Then there are locality DBs that are all structurally identical, with the tables storing data for the localities.

The Access database checks logins, links any utilities db objects as needed, decides which databases the user can access, and allows them to connect to localities one at a time, linking the locality tables when the data form opens and killing the links when the data form closes. The locality data form displays the records from the main table, and 8 related tables. The main form has 8 subforms plus a display-only listbox that shows the results of a union query (unique surnames for the record).

I'd thought I'd done this sort of thing before without any problems, but looking back I see that none of my projects seem to involve SQL linked tables and lots of subforms off a main record, so I haven't really come across this before. And my other projects were born in previous versions of SQL/Access and migrated forward. So, I can't tell if this is Access/SQL version-related or not.

The problem I can't resolve:


I can add, update, and delete records in the subforms, and the changes show immediately on the back end. I can delete or edit a record, and then delete or edit records in that subform and other subforms, or in the main record, without any problem. But after adding a record to a subform, I cannot edit anything in that subform, even if I scroll to a different main record! The new subrecords will load fine when changing main record but the subform is ruined for any further edits, only additions. Any attempt to edit a record in a subform ON ANY parent record once a record has been added results in the record fields showing #DELETED and the following error appears:

"The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record. "

If I then hit F5 in the subform, the data displays normally again, but I still cannot edit records. I can continue adding records, which also appear immediately on the SQL side. I am not prevented from deleting records but the records don't delete, I just get a message that the record has been deleted (which does not appear normally when deleting a record in a subform, only when it gets stuck in this loop).

Based on disconnected bits of advice found elsewhere, I've set the recordset types in all the forms to Dynaset (Inconsistent), forced a requery and refresh on both the edited subform and the parent after each update (tried it with subform first and also parent first, no difference). I've tried changing the client-level options to use row locking on open or not, and altering the Refresh and ODBC refresh intervals. NONE of it helps.

Anyone have any ideas on where to look?

Thanks,

spud
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 22:42
Joined
Sep 12, 2006
Messages
15,614
Do you have autonumbers in the tables.

There can be an issue with inserting records to SQL tables when the tables have autonumbers. I don't remember exactly now, but I had immense problems with this when trying to move some records from access to SQL Server.

I kept getting a problem similar to the one you describe.

set_identityinsert may help, although I couldn't get it work the way it was described on line.
 

spudchick

New member
Local time
Today, 18:42
Joined
Oct 30, 2015
Messages
6
Thanks, Dave. Yes, all the subform tables have an autoincrementing identity field that is the primary key on the SQL side. It assigns the ID fine after inserting (visible in the back end), and I can see the id back on the Access side after the form is refreshed.

In design view in Access, the linked tables show the ID fields as autonumber primary keys.

I'm using bound forms, so I'm not using code to insert/update/delete the records in the subforms.
 

spudchick

New member
Local time
Today, 18:42
Joined
Oct 30, 2015
Messages
6
I finally figured out the problem; it was an issue with required fields and unique keys, but there didn't seem to be an error to trap on the front end. Anyway, all is well now, and I was able to remove a lot of code and also the timestamp fields. Thanks for your thoughts!
 

spudchick

New member
Local time
Today, 18:42
Joined
Oct 30, 2015
Messages
6
Thanks, Gina. This is already a SQL back end, though--using Access 2010 as a front end only. I think I would have found the problem sooner if I hadn't been sick for practically a solid two months! Brain function began to return at the same time normal human color did :) I'm in beta testing now, so far so good.
 

GinaWhipp

AWF VIP
Local time
Today, 18:42
Joined
Jun 21, 2011
Messages
5,901
No problem... and I knew you had an SQL Server back end but thought there might be some tips in there that would help target the problem.
 

Users who are viewing this thread

Top Bottom