Undetermined Problems with an MS-Access front and back end

[Martyh]

I got the "Error #3326 - Recordset Not Updateable” but there seems to be no reason. Where do I go next? - I'm looking for suggestions!

Summary

• tried the app as a user on 2 different PCs in the office... one works OK although there are some long duration issues and 1 does not - got error "3326"
• not likely that the database software was at fault, but that it might have slowed down the software because of the heavy load (remote access with a 1.1 G server backend & ~50,000 KB remote front end) When the database runs WITHOUT the remote concerns, there no problem, although we had fewer people accessing it (2-3 people as opposed 5-6 people). Is this the problem, and if so where do I go to correct it?

Background
I wanted to understand the “Error #3326 – Recordset Not Updateable”. I determined that the recordset in question was actually a single table – tblDIRVersions. A reliable source has said that “A recordset is always updateable when it is based on a single table.” (1)

He also states that “causes of non-updateable recordsets are many and varied.” So this was not the problem.

Initially, I started the investigation by myself to determine how the database characterized the problem. The only problem that I had was that I could not get the database to display any of the characteristics.

I started by downloading a new version of the front end from the O:/ drive. I went to the DIR screen in question (FOLLOW UP REVIEWS) for all the DIRs and then retrieved the data for 1 particular DIR (1000511247). The detailed info came up and I changed the Comments, adding my initials and the current date. I then pressed the “Review Complete” button. The DIR info was added, and the “Last Modified by” was changed as well to reflect my initials. In other words, there was no problem (as reported earlier in the day by Sgt X)!

However, I went on to investigate how the problem was showing up in 2 situations with 2 separate individuals.

The first was Lt Y at about 10:30 am this morning.

We attempted many scenarios.

In the end we determined that only I could log in to the database, and he could not when I was logged in. The thing was that I could NOT always complete the transaction. Two transactions were completed while 1 was not.

The second was Sgt X at about 12:00 pm noon.

Again, we attempted numerous scenarios, with new frontend software.

At the beginning, only I could login and complete the transaction, while Sgt X got the error “Run-time error '3326' This Recordset is not updateable”.

On our 2nd attempt, however, most unexpectedly, we determined that both of us could login at the same time and complete the transactions. However, the process took a fair amount of time, approximately 2 minutes each. We verified that the transactions took place. We were operating on two separate DIRs (1000511247 and 1000455090)

(1) From Roger Carlson, (MVP), “Roger's Access Blog”, Friday, November 20, 2009, “This Recordset Is Not Updateable. Why?”

 

[jdraw]

See this for possible reasons: Allen Browne Read Only

 

[Martyh]

Hey J,
Thanks for answering so soon... I'm going to check out the permissions and the file attributes.

All the rest of the reasons by Browne have already been discounted. There is only 1 table involved and it is very straight forward with no fancy stuff... besides it works some of the time (and I'm not sure how the permissions and the file attributes relate)!!

Anyways, the only thing left is permissions and the file attributes.
Thanks,

Marty

 

[Isaac]

So what exactly IS this 'table' ?

 

[arnelgp]

on all situation, whether single user or multi-user, when you Add or perform Update on a table,
msa Locked that whole table first before committing the changes.
msa does this on it's Own timing.
so it is better approach to have some mechanism, a Semaphore flag, telling that the table
is being locked for update.
the other person pc who at some split-second, send also his update then received a Boolean (false)
that the table is locked and should wait until the locked is removed.

also it is advised to Force commit any update to the db by immedately issuing the command:

dbEngine.Idle dbFreeLocks

on the AfterUpdate event of the Form.
this forces msa to flush all updates pending on its queue.

this is but a recommendation and may not be the real cause of your error.

 

[Martyh]

Thanks pat and Arnel for your advice...

Pat I think that your no 4 is going to get my attention first ... if the IT department agrees!

Arnel, is the "whole table" locked or is it just portions of the record ie "pages" or even just a single record? I really have never understood the exact procedures in Access for locking... Perhaps you could explain!

This has been most informative!!

Thanks,
Marty

 

[gemma-the-husky]

@Martyh
You keep mentioning transactions. Are you using transactions to apply updates? Why, out of interest?

 

[Martyh]

No I am not using transactions to apply the update... this is just one table that I am trying to have updated. I think the problem may be in the permissions. But I've compared 2 DBs in the same directory (remote -- O:/ drive) and I can't seem to find a difference in the permissions, but the one allows me to make the changes in the table and the other one does not!! Also, now after playing around with the data, when I go to open it up it tells me that the data is read-only! This is new. Can you tell me what causes this to occur?

 

[Isaac]

We still don't know the exact nature of the table itself...is it a local access table, a linked access table, or something else.

 

[Martyh]

Hi Isaac,

It is a linked table located in the backend. The front end only contains UI (forms, queries etc) ... in the last email I was speaking about the backend entirely ... and I was circumventing the front end entirely.

Marty

 

[Isaac]

Thanks Marty. Is the table linked to an Access database, or some other type of link?

 

[Martyh]

No ... nothing fancy ... just an Access Backend (2013) I've done this at least 50 times before with success. However, COVID forced the people operate from home and there is the difference (i think). and i think that Pat intuition about being about the WIFI may be correct. I am trying to contact every one right now.

Regards,
Marty

 

[Isaac]

just an Access Backend (2013)

Okay. Thanks for confirming.

 

[Martyh]

Hi Pat,

You say "WiFi cuts out which it does frequently in many places"

How will the FE-BE config, with say Citrix or Remote Desktop be able to better support remote users? How does Citrix (or Remote Desktop) prevent the WiFi cut out?

Best Regards,

Marty

 

[Isaac]

If I may. I think the following is true: Because the wi-fi cutting in and out will only interrupt the connection to Citrix/RDP - thus essentially only interrupting, or causing to flicker, the screen painting. Rather than the actual database itself - which is wholly inside Citrix/RDP.

 

[Martyh]

Thanks for clarifying Issac and Pat!!!

Marty

 

[NauticalGent]

How will the FE-BE config, with say Citrix or Remote Desktop be able to better support remote users? How does Citrix (or Remote Desktop) prevent the WiFi cut out?

I came here looking for an answer to the same question. The current job I'm in uses this set-up with Thin Client PC's. Most of the workers, myself included, work from home and although the performance isn't blazing, it isnt really all that bad.

With Citrix/RD, the FE and the BE are both on the same server. There is no network connection involved between FE and BE. Citrix and RD are usually faster than a LAN app. The only communication sent over the "wire" with Citrix/RD is screen images from the server and keystrokes and mouse events from the client. No data ever gets sent across the internet except as keystrokes. As Isaac said, you might loose your connection to the server but the FE doesn't loose its connection to the BE. You just reconnect to your session and pick up where you left off.

Excellent explanation, thank you. As I said, the performance isnt stellar, but I am certain I can improve it with better form design and more efficient queries.

Really appreciate everyone who contributed to this thread. If anyone has any tips and tricks to working with a Citrix/Thin Client setup, I'm ALL ears (eyes)...

 

[The_Doc_Man]

The issue of "intermittent success" can sometimes be caused by permissions issues - not ONLY with the back-end file, but with the back-end folder. Any user of a native-Access back-end must have MODIFY (broad-brush) permissions on that folder and PASSTHRU (fine-grained) permissions on all folders that are parents, grandparents, etc. of the shared folder. The problem comes about when the back-end lock file cannot be properly manipulated by Access because of incorrect folder permissions. These will cause transactions, updates, inserts, etc. to fail when multiple users are involved.

Therefore, when checking permissions, don't limit yourself to the BE file. Check the folder too.

 

[Martyh]

Hi Doc,

Thanks for replying to this issue. I don't really understand the following...

>> issue of "intermittent success" can sometimes be caused by permissions issues

My understanding of permissions is that once they are set, only physically resetting them can change them. i.e. they are NOT changed by typical software. How can it cause "intermittent" success (or failure)?

Regards,

Marty

 

[The_Doc_Man]

The intermittent success occurs based on who got there first if the permissions are wrong. That is because in self-defense, Access opens something in Exclusive mode if it can't deal with the lock file. It is the only way to protect the DB contents.