Access Database is getting corrupt again and again

Please have a look at the post above

In a nutshell, what is the fix you are suggesting, which you have had four weeks of success with ?

I am still rolling back my users to Access 2013 32-bit, and so far, it's been good. I wish the rollback was going faster than it is, but with IT, you can't have everything. At least they're responding to my requests now, so that's a plus.

George I totaly agree with you.

But there is nothing left.

We are by our own. So either we are seaching for a job or we try to gain some time with custom fixes while searching to bail out from Microsoft ...

But I suggest to give it a try. Because it is the 4th week without any db corruption and the speed is skyrocket, by far faster even before the Server Fix ...

While I appreciate all the suggestions and possible solutions, this is Microsoft's responsibility to fix this issue. Developers shouldn't have to come up with workarounds which may or may not work. If MS is viewing Access as an obsolete product which they don't want to support anymore, then be honest about it and tell the Access community.

REQUESTING HELP FROM SOMEONE TO ALSO TRY THIS. 

Some Speed issues and a potential new solution:

Ok so up to now we have:

1. Disable Leasing solution on the server, with some variations aka “Server Fix” (Verified and Working from my customers on different setups)
2. SMB Registry Fix to client machines aka “Client Fix” (Verified and Working from my customers on different setups)
3. Change folder of the Backend ot a subfolder of the same folder that you had it already and relinking (Verified and Working for almost 1 week and then again corruption occurred so for me it is not a permanent solution)

Both 1 and 2 solutions (Server fix and Client Fix) are having some speed impacts. And these impacts are 100 % verified since when we remove the solutions, the speed is increased. So when do we have the speed issues, which one of these solutions are the best regarding speed and what can we do to increase speed ?

When do we have speed issues ??:

1. When we have continuous forms with more than 2000 records for displaying and with combo boxes (that are conditionally requered) or when some controls are conditionally formatted.
2. When we are performing action queries that are not properly filtered
3. When we have 2 or 3 dlookups running on the row eg when “On Open” or “On Current” of the heavy record loaded form.
4. When on these forms we are performing me.refresh or me.recalc. Mention that Me.refresh was the most speed consuming task on these forms !!!
5. When we have enabled BOTH server fix and Client Fix. I have made that mistake and the speed was terrible. If you choose the Server fix then please double check the registry of the clients machines because you might by accident installed also the client fix.

Which Solution was faster FOR THE ABOVE CONDITIONS ??:

3 times or more faster is the Server Fix. The client registry fix is MUCH SLOWER for the above conditions (please do not mess up with other conditions that might have a speed affect but cannot be observed).

Observations regarding speed issues:

So I stayed to a customer 2 whole days, with my front end on development mode (mdb), linked to the backend db (mdb again) that is established on a Windows 10 Server and opened the VBA project, put some Msbgoxes to stop the code running and check between the msgboxes the speed from some actions made (docmd.openquery of some action queries, refreshing form, requiring form etc). So I noticed that when something was running slow (eg an action query) for the first time, the second running was full speed !!!. So I figured out it there is a cache issue that is limiting speed

What can I do to increase speed for the above conditions ?

1. Limit me.refresh (or docmd.runcommand accmdrefresh) and me.recalc only to the times that it is necessary.
2. The Combo boxes that you have to be required, please make them requred only on the action “on click” of them
3. Increase MaxBufferSize and MaxLocksPerFile and Threads on each Client Maching that your Front End Application is running. By changing these settings speed has increased for the above Conditions but I am 100% sure that it will increase every Access Solution that you are implementing!!! How can you do this ?

Step 1: Click Start, and then click Run.

Step 2: Type regedit, and then click OK.

Step 3: Use the appropriate method:

In Microsoft Access 2000 / 2002 / 2003 that are running on a 32-bit Windows operating system, use Registry Editor to locate the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0

In Microsoft Access 2000 / 2002 / 2003 that are running on a 64-bit Windows operating system, use Registry Editor to locate the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0

In Microsoft Office Access 2007 that is running on a 32-bit Windows operating system, use Registry Editor to locate the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE

In Microsoft Office Access 2010 that is running on a 32-bit Windows operating system, use Registry Editor to locate the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE

In Microsoft Office Access 2007 that is running on a 64-bit Windows operating system, use Registry Editor to locate the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office*12.0*\Access Connectivity Engine\Engines\ACE

In Microsoft Office Access 2010 that is running on a 64-bit Windows operating system, use Registry Editor to locate the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office*14.0*\Access Connectivity Engine\Engines\ACE

Etc, according to your version of access: 

Step 4: In the right pane of Registry Editor, double click MaxBufferSize. On the Edit DWORD Value dialog box, click Decimal. Modify the value of the Value data box as required, and then click OK.  I have changed it to 50000. Do the same for MaxLocksPerFileto the value of **15000.**Also change Threads to the value (demical always) to **10.**So the demical values have to be:

MaxBufferSize = 50000

MaxLocksPerFile = 15000

Threads = 10

<REBOOT THE CLIENT MACHINE>

Have a look at my Registry Setup:

4. Another thing that has increased speed is to keep open the links to all of the backend databases that your Front End software is handling. So the entire time that your application is running you can have them with just a short code to be open all the time. This code was found at Total Visual SourceBook and goes like this:

How to call it: Call Now_OpenAllDatabases(True)

When to Call it for opening the permanent link: Create a Form that opens together with your front end application (or alter the one that you already have and put it “on open” of the form

**How to change back and close the permanent open links:**Call Now_OpenAllDatabases(false)

**When to Call it for Closing the permanent link: “**On close” of a form

The source Code:

Public Sub Now_OpenAllDatabases(pfInit As Boolean)

Dim x As Integer

Dim strName As String

Dim strMsg As String

' Maximum number of back end databases to link

Const cintMaxDatabases As Integer = 2

' List of databases kept in a static array so we can close them later

Static dbsOpen() As DAO.database

If pfInit Then

ReDim dbsOpen(1 To cintMaxDatabases)

For x = 1 To cintMaxDatabases

' Specify your back end databases

Select Case x

Case 1:

strName = “eg \ServerPC\Test\Database1.mdb” ‘This is the Backend Database

Case 2:

strName = “eg \ServerPC\Test\Database1.mdb” ‘This is the second Backend Database, in case that you have linked to more than one backends etc

End Select

strMsg = ""

On Error Resume Next

Set dbsOpen(x) = OpenDatabase(strName)

If err.number > 0 Then

strMsg = "Trouble opening database: " & strName & vbCrLf & _

"Make sure the drive is available." & vbCrLf & _

"Error: " & err.Description & " (" & err.number & ")"

End If

If strMsg <> "" Then

MsgBox strMsg

Exit For

End If

Next x

Else

On Error Resume Next

For x = 1 To cintMaxDatabases

dbsOpen(x).Close

Next x

End If

End Sub

AND HERE COMES THE GOOD ONE …

By doing all these changes to the heavy record loaded continuous forms together with registry changes and established the above permanent links form my front end apps to my backend databases that are located on the server, speed was increased dramatically. All the changes were made step by step to check the speed impact of all these. So each one of them contributes to the speed increase. The speed increase both for Server Fix and Client Fix (double checked with both solutions). But as I have already told you, the Server fix is always faster. So I choose the Server fix.

BUT (don’t worry it is a good one)

After doing  all these I removed the temporary solution Server Fix (I did not remove the client Fix because I am not using it). **So I removed Server Fix and for 15 days there is no Corruption !!!**So perhaps we are dealing with a new Solution so I am asking you to help and check it. I do not know which one from all these is working. The registry changes ? The permanent link ? It might not work for everyone. So please have a look at it.

Thank you

PS

MICROSOFT ... shame on you

No more excuses