Access Database is getting corrupt again and again

So for a mere $2000 I can convert 50 users to an unnamed Cloud Desktop Service ?

I think I'll pass and convert my backend database to SQL Server if all options fail. However, I will do a rollback to Access 2013 first.

Hi All

I had my backend tables on a drive inside the server PC . . HP Proliant ML350 Gen 10.

It was set up with SSDs for fast data access.

A few months ago i was getting daily corruption of the tables db . . mostly in the morning as staff were logging in.

I also have an external NAS data server attached. ( our main data storage device )

In desperation I moved the tables to the NAS. Since then I have not had a single issue with tables corruption. 

I still do not know why I had issues with the in-server setup. Maybe it was software or hardware . . but now it is fixed.

Cheers . .PaulG

This is good advice, but not really new information unless I am missing something. Any remote desktop implementation will give the same results assuming the back-end database is on the same server as the front-end, meaning it is accessing the back-end via a local drive and not a network drive or UNC. It does breathe life into the application and makes it accessible from many devices and platforms which is great. I guess the new information is the company that you found. I assume it is $40/user/month? When you do the math it is not a cheap solution, but definitely one to have in your arsenal. I am constantly keeping watch hoping to find a truly affordable and scalable solution with a reputable and reliable company.

I have been referring my customers to Right Networks for years and for the most part it works well. The only major downside is the cost. Our program is relatively inexpensive and to add the monthly cost of a desktop hosting service is not very well received. I also would be very interested in who this company is that you mention and what makes them unique.  Another consideration is whether or not your application integrates or interfaces with any programs outside of your Access runtime environment like Word/Excel/Outlook or QuickBooks. This can make it necessary that you install those apps on the remote desktop host as well which can further increase complexity and cost.

I have also been following this thread from early one and have lived through the pain and the Microsoft inability to solve this (which is truly remarkable and devastating to so many).  I have used the client side registry changes suggested in this thread and it virtually stopped this problem in its tracks (about two years ago). You HAVE to make sure that the change is made on EVERY Windows 10 client that uses your application or this will not work. We have many, many customers over two years that have implemented this with success. We also offer the option of the server change to disable leasing with the caveat that it can cause performance degradation or even break certain applications. It also requires someone else get involved to implement the server change, so it cannot be included in a client side setup program. So I have found the client side change to be the better option. The other suggestions of changing the folder of the back-end database or rolling back to a previous version of Access do not make sense to me other than perhaps as a temporary solution. But if it works for you, that's what matters.

TO MICROSOFT IF YOU ARE LISTENING..... I am encouraged you still have this problem open and are working on a permanent solution so we don't have to do these workarounds we should not have to do. PLEASE do not give up on this. You can see all these developers (and their customers) are paying a huge price for this problem and lack of a permanent solution for two years now. The price is incalculable and what you are hearing in this forum has to be a drop in the bucket. We cannot wait another two years. Unless you are driving people away from Access (and Microsoft) on purpose, you need to fix this problem once and for all ASAP with no more excuses.

Some Speed issues and a potential new solution:

Hello from Greece and sorry for my bad English. My company is relied on Access development as also on other MS Products such as Visual Studio etc. So this long lived issue almost destroyed my company, my temper and my life, without any respect to our loyalty from MS. I used to be a hard supporter of MS. Now I am not. It is obvious that this company is now far away from the Supportive and Trading ethics that the founder of this company (Bill Gates) established.

I had some previous posts regarding speed issues related to the 2 solutions. So I would like also to contribute to this MeSs, after long testing of both solutions to many customers as also some new tests that I am performing.

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