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' ?

 

[Pat Hartman]

1. make sure that each user has his own separate copy of the FE. FE's should never be shared.
2. the FE links to the BE which IS shared so it is stored on a network drive that all can see.
3. make sure that no one is opening the database in exclusive mode.
4. make sure that all PC's have a wired connection to the LAN. WiFi and Access do not work well together. You will end up with corruption if the connection to the BE drops because the WiFi cuts out which it does frequently in many places.

 

[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

 

[Pat Hartman]

newer versions of Access implement page locks. That will be multiple records depending on the record size but Access doesn't raise an error. It retries.

Access with Jet/ACE BE should not be used over a VPN. It is way too slow.

If you need to support remote users, the best options are Citrix and Remote Desktop.

 

[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.

 

[Pat Hartman]

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.

When using Citrix/RD, it is still critical to not share a FE so instead of downloading the FE to the client's C: drive, it is generally installed in the user's personal directory. The batch file to distribute the FE is slightly different since it needs to pick p the user's credentials to navigate to the correct directory.

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
%USERPROFILE%\DwgLog\DrawingLog.accdb

 

[Martyh]

Thanks for clarifying Issac and Pat!!!

Marty

 

[Pat Hartman]

You're welcome