Back-End corruption with split database on LAN

[ItsMe]

No, not a Dr Watson or Windows error log. Access will create tables and store data in them when it fails to complete something. Sometimes info can be gleaned from hidden system tables within Access but the tables created in major exceptions will not be hidden. Paste Errors in an import procedure would be an example of a table that Access will create.

[ItsMe]

I think we are still dealing with an issue of an Indexed field that does not allow for duplicates. Beyond that I would be looking at the form that has all of the Joins and fields from multiple tables. Maybe there is a way you can segregate some fields/controls onto a small and updateable recordset, while other fields are managed by subforms with separate recordsets. Use a toggle control and VBA to manage updates and enforce referential integrity.

Additionally, I am still curious about your Domain function to create PK values. If someone had this form with the mega-recordset open and then tried to run a Domain function, the Domain function could posibly take a back seat to other processes.

[jwreding]

OK, I know what you mean now. When the issue was happening last month, there was real corruption and it was creating an MSysCompactError table. Now, nothing is being created.

I'm not trying to be stubborn about the indexed field, but I'm just curious why it would be breaking down now and not in the past 3 years? We used to have up to 30 people using the FE at once...now it's down to 12.

I removed most of the foreign fields from the form and nearly all of the controls that grab foreign values are locked.

The domain function runs on a separate form. They cannot create new records with the form that we've been talking about. I have a "new load" form that only accepts the bare minimum info and then the save button runs the domain function and then opens the other form up filtered to the newly created record. Here is the code (please be gentle, I know it's a little ugly):

Private Sub cmdSave_Click()

    Dim strProNum As String
    
    On Error Resume Next
    
        Me.Refresh
        
        Me!PRO = DMax("PRO", "Loads") + 1
        strProNum = Me!PRO
        
        Me!CustomerID = Me!cboShipper.Value
        Me!FleetID = Me!cboFleet.Value
        Me!VendorID = Me!cboVendor.Value
        Me!ConsigneeID = Me!cboConsignee.Value
        Me!CarrierLH = intCarrierLH
        Me!CarrierFSC = intCarrierFSC
        Me!Expense = intCarrierLH + intCarrierFSC
        
        Me.Refresh
        
        Me.Dirty = False
        DoCmd.Close , "", acSaveYes
        DoCmd.OpenForm "frmDailyOpsLoadDetail", acNormal, , "[PRO] = " & strProNum
    
End Sub

As you see, it grabs the PK value at practically the same instant that the record is written. I guess it would be better if I put the function at the very end just in case, but it's a matter of milliseconds at most.

[ItsMe]

..just curious why it would be breaking down now and not in the past 3 years?...

I don't have a genuine reason. It is only that I am trying to cover the bases and fill in the blanks, all at the same time. I brought it up again because of my personal rules. I might create a rule that I will follow and within a year or so forget the underlying reason why I created the rule in the first place. I am simply analyzing the situation. The fact that virtual memory demands may have increased at the client caused me to worry. It seems this is not an issue though. Your explanations sound reasonable enough.

Unfortunately, an explanation as to why there is a problem eludes me. Obvious stuff has been eliminated and we are now left with how code behaves with the workflow.

Specifically with what you posted here, there are a couple of things I might try.

Rather than
On Error Resume Next

I would build out an error trap that wrote an error to a table in case one occurred. This code is mission critical and you want to know if something runs amuck.

I do not really see a need for .Refresh. What matters here is the .Dirty property of the form. Save the record ASAP so the other users see what is going on at the table level. Maybe even have a separate procedure to retrieve and or verify the value of strProNum. My approach to this would be not to use the Domain Function.

I have few instances where I need to generate a PK, manually so to speak. What I do is use a separate table with two columns. One column is AutoNumber and the other is Long Integer. The User retrieves a PK by writing a sessionID or some sort of Unique Identifier to the Long Integer column. The act of writing to the table creates a PK in the AutoNumber field. This PK is used as a PK in my target table, the table that does not have an AutoNumber field.

The only other thing I can think of right now is the User could possibly remove the filter and edit stuff in other records. Not to say this is what is happening. It does not add up if the FK's are not editable either, as you stated.

I am referring to this
DoCmd.OpenForm "frmDailyOpsLoadDetail", acNormal, , "[PRO] = " & strProNum

A more secure way would be to pass strProNum as an Open Arg and use the Load Event for frmDailyOpsLoadDetail to add a WHERE clause to its RecordSource. Really nitpicking here.


Just brain storming here....

[habidat]

Hi jwreding,

Have you resolved your problem? If so, would you mind sharing it?

We have having the same issue - an Access FE/BE app that is installed at about 30 sites, running fine everywhere, and about 2 weeks ago at one site we started getting corruption in the back end database, just exactly as you describe, we've had 4 instances, everything from a few hosed records to what looks like giant copy-and-paste operations gone awry.

Our client's IT team is reluctant to consider that this may be related to their environment, although the weekend before the corruption began, they upgraded the server on which the data file resides.

We also provided them a new FE release before the corruption, but the only change in that release was a new report, and some other very minor changes. Other sites are using this release as well with no issue. We are prepared to roll back our release to see if this stops the corruption, but they really need this report, and I can't think of anything in this release that could cause corruption like this. I've been developing Access database applications since Access 97, and I've never seen a case where corruption wasn't related to a network or other hardware issue, but I suppose there is always a first time.

Our app is an Access 2000 file format released as an MDE. We have sites using Access 2003 - 2013. This particular site uses Access 2013.

Any thoughts, or ways to narrow the possible causes?

Thanks in advance for any help.

[jwreding]

The problem seems to have resolved itself. I was going to hop back on here and update the thread, but hadn't yet had the chance. I think I was finally able to isolate the problem to one workstation. Even though I tested network cards and our LAN in general and didn't find any problems, the circumstances led me to finally decide this was the case.

During this time, we had been upgrading users' workstations from very old systems to brand new laptops. I had been fighting the problem all week; sometimes it would happen 2-3 times in one day. The beginning of the next week, it just stopped happening altogether. It was fine for 3 days and then started happening again with a vengeance. I just happened to realize that the day it started happening was the day one particular user came back after being out of the office all week. Lo and behold, I put her next in line to receive the upgrade and have not had the issue since (this was about 3 weeks ago).

Your deployment is on a much larger scale than mine, and I am also the IT team so I have full flexibility on our network. I'm not sure how friendly you are with your IT guys, but you could see about moving the BE file to a different server to at least rule out that problem. Now, is the BE shared across all 30 sites, or is there a separate copy of the BE at each site?

[habidat]

Thanks for the response! I'm glad your situation was resolved.

Our client's IT team now seems to be thinking the corruption was related to the server upgrade preceding the first corruption instance (from Windows 2008 R2 to Windows Server 2012) and they are moving the shared data file to another server still running 2008 R2 to test. We are quite friendly with the IT person we usually work with, by the way, but it is a very large organization and we are a pretty small fish!

One of the techs spotted this article, and they suspect SMB3 as the culprit:
http://social.technet.microsoft.com/...winserverfiles

However they are not at all interested in disabling SMB3 on their brand new file server just for our little shared Access database used by 3 people in one department. It's not a option I want to be responsible for either, so we are trying to find out if there are any other settings that could resolve the problem (I asked them to check opslocks, but I don't think that has any effect on SMB3).

Meanwhile, we are hoping to upgrade this client to our SQL Server based version, which will obviate the corruption issue entirely.

It is worrying that it appears that the latest MS server OS will absolutely corrupt a shared Access (or other ISAM?) database. It puts us in a very difficult situation for our other clients who are still using Access as a back-end and plan to upgrade their servers.

I'll post back with a final resolution, for anyone with a similar situation.

[jwreding]

That would be strange, as we were on Server 2012 in a virtual environment for nearly a year before our problem happened.

[habidat]

Hmm.

Is your Server 2012 using SMB3 or SMB1?

I'm not entirely clear on what you mean by "in a virtual environment", but maybe that is a factor?

[jwreding]

Sorry for the delayed reply. By virtual environment, I mean that our servers are all VMs. From what I can gather, we are using SMB 2.1 and SMB 3, as we have 2012 servers and a mix of Win7/Win8 workstations. Strangely enough, the Monday after I posted my most recent reply, the database was corrupted first thing in the morning. I ran several repairs on it, then released it for use again. Only 5 minutes later it was corrupt again. I repaired it again and this time rebooted the server. Now it has been 4 full working days and not happened again. This doesn't help much to move toward a solution, but it's additional info. You might ask the IT department if they can reboot the server it is hosted on. Perhaps it hasn't been done in weeks or months.

[habidat]

Thanks for keeping me updated, and I hope you figure out what is going on.

The IT group has moved the back end database to a server with 2008/SMB 1 and so far no corruption reported, or put it this way: I haven't heard from anyone in a few days, so I think they haven't any corruption yet.

I'll check in with them in the next few days to see what they are thinking.

[habidat]

Reporting back that it has been about 2 weeks with no corruption after the back-end database was moved to a 2008 windows server, so we are taking the SMB3 incompatibility with Jet databases to be the cause.

This is an interim solution until we can migrate this client to our SQL Server-based product. It looks to me like a shared Access database application is no longer a reliable option under current Windows servers.

[ItsMe]

This is interesting information.