Weird save-data issue

[larryg99]

Hello All;
I have a weird situation for your review and thoughts on.
I have a db (Office/Access 2013) that is used by 5 users.
I have a VBA record write in a procedure under a Save button on a form that files data from the form into a table.
It's a split db with both components on a server.
Three of the users have no issues saving this forms data (or any other data saving issues).
Two of the users can save data in other forms, but not the form in question.
Stepping through the code, the procedure kicks out on the rs.Update line.
The error thrown is...#3049 Cannot open database. It may not be a database that yuour application recognizes, or the file may be corrupt.
In diagnosing the issue I placed a Debug.Print "Record Count: "; rs.RecordCount just after the Set rs = db.OpenRecordset(sSql) command, and on the troubled users system it does display a correct record count. That to me indicates there is no "database" or "Corrupt" file issues if their systems can read the file to count the records.
Also, the troubled users have NO issues saving data from other forms within the same instance of the db.
Given that all users are using the same db file, my thoughts lean towards a setting on their individual workstation cpu. But, given that these same troubled users have no issues saving data from multiple other forms within the db (using the same vba code lines), how could a local Windows settting allow saving with some forms but not others?
Code is as follows:
sSql = "SELECT * " & _
"FROM MyTable;"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSql)
rs.MoveLast
rs.MoveFirst
Debug.Print "Record Count: "; rs.RecordCount

With rs
rs.AddNew
rs![FirstField]=(Me.FirstControl)
(etc., etc., etc.)
rs.Update
End With
rs.Close
MsgBox "Data Filed successfully !", vbOKOnly
Keep in mind that the same basic code is used for multiple other forms, for which the troubled users have no issues with.
Any thought ???

 

[Ranman256]

Forms do not require code.
The user can type directly into the table thru a connected form.
Have you trie this?

 

[isladogs]

The BE MAY not be corrupt now, but at some point it will be with your current setup.
You NEED to give each user their own copy of the FE database on their own PC as a matter of urgency.

The reason why the behaviour is different of some users may be a reference issue e.g. due to different versions of Access on each PC

If the form is bound to the table, the data will be saved automatically so as Ranman said, no recordset code will be needed.

 

[missinglinq]

Just reinforcing what Colin has said...having multiple users, even just two or three, sharing a single Front End, on a network drive, is a surefire way to repeated episodes of corruption, as well as speed and timing problems, and all manner of strange, odd and curious behavior, possibly including your current problem!

Being in forced retirement, I've spent 8-10 hours a day here and on other Access forums/newsgroups, and over the past twelve years and have seen literally dozens and dozens of reports of split databases, using links to a single Front End, causing all kinds of problems, as well as massive data loss! The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

You've simply got to place a copy of the Front End on each users PC.

Linq ;0)>

 

[larryg99]

Just reinforcing what Colin has said...having multiple users, even just two or three, sharing a single Front End, on a network drive, is a surefire way to repeated episodes of corruption, as well as speed and timing problems, and all manner of strange, odd and curious behavior, possibly including your current problem!

Being in forced retirement, I've spent 8-10 hours a day here and on other Access forums/newsgroups, and over the past twelve years and have seen literally dozens and dozens of reports of split databases, using links to a single Front End, causing all kinds of problems, as well as massive data loss! The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

You've simply got to place a copy of the Front End on each users PC.

Linq ;0)>

Thanks for the replies...

As a test, I did place a version on the end-user's C-Drive. There was NO change. All users have the same version for Access on their desktops.

Also, these are UNBOUND forms. No forms in the db are bound.

Db has been working fine for all users for over 2-years. Just last week did this issue surface for only 2 of the users (and no code changes were made). The issue does not rotate between users, it is constant with 2 of the users. Again, these two users save data constantly all day long using other forms and they experience no problems. The issue is with only one infrequently used form. The form is used once daily for all users, but almost never at the same time, while other critical forms are used constantly during the day (by multiple users), and there are NO issues with any user, or any other form... i.e. only these two users are having this issue, and only with this form.

While I can appreciate the thoughts on conceptual designs of server/desktop issues, I don't think, based on the historical performance of THIS db, that this issue is related to the location of the front-end file.

Can anyone think of any Windows settings that would/could affect data saving? Also, I think next step is to recreate the form itself as perhaps something in the form itself has become corrupted. However I hold out little hope for that because only 2 users are experiencing the issue, and all use the same form.

Thanks...

 

[isladogs]

You should still check for missing references.

Why are all your forms inbound? This makes development work much harder and take much longer. It is almost always totally unnecessary.

I doubt the issue is a windows setting. Possibly an Access setting but unlikely especially if other forms work fine for those users.
Have you checked whether the FE compiled on each PC? If not, please do so.

And to repeat, there may be no issues yet with multiple users, but there WILL BE in the future.
Think of a shared FE file as being like having several drivers driving your car at the same time. At some point the car will crash and the results could be catastrophic.

 

[larryg99]

Thanks for the replies...

As a test, I did place a version on the end-user's C-Drive. There was NO change. All users have the same version for Access on their desktops.

Also, these are UNBOUND forms. No forms in the db are bound.

Db has been working fine for all users for over 2-years. Just last week did this issue surface for only 2 of the users (and no code changes were made). The issue does not rotate between users, it is constant with 2 of the users. Again, these two users save data constantly all day long using other forms and they experience no problems. The issue is with only one infrequently used form. The form is used once daily for all users, but almost never at the same time, while other critical forms are used constantly during the day (by multiple users), and there are NO issues with any user, or any other form... i.e. only these two users are having this issue, and only with this form.

While I can appreciate the thoughts on conceptual designs of server/desktop issues, I don't think, based on the historical performance of THIS db, that this issue is related to the location of the front-end file.

Can anyone think of any Windows settings that would/could affect data saving? Also, I think next step is to recreate the form itself as perhaps something in the form itself has become corrupted. However I hold out little hope for that because only 2 users are experiencing the issue, and all use the same form.

Thanks...

OK...to my surprise... apparently it was something in the form itself. I created a new form (actually just made a copy of it) and that resolved the issue. For the life of me I can't figure out why a corrupted form would only affect 2 users when they are all using the same db file. Anyway... issue resolved.

Thanks for all the replies !

 

[isladogs]

Glad you sorted it on this occasion.
Suggest you use this as a wake up call & give everybody their own copy of the FE on their own PC