Corruption with a split database on a remote desktop

[KTrueblood]

I'm having some major access corruption issues. I'm just wondering if perhaps there is a better program or platform to do what I need.

Our access database is split into a front end and back end, and worked with no issues while we were all in the office on the same network. We didn't attempt to use it across different office locations. When we were all sent home (now permanently), we use a remote desktop (Citrix) which houses the back end on the network, and each user still has a copy of the front end on their Citrix desktop. This has worked well for a while, but now we are getting so many corruption issues. I compact and repair, and that seems to make it worse. The last issue created 1100 records (some of them duplicating important records, some of them pretty much empty). It also seems to lose the Primary Key on a lot of the tables.

I've researched a lot and we probably have ALL of the reasons a database can become corrupt, but especially different users on different types of networks, most likely some without a stable internet connection. There are only 6 people accessing it. I recently converted it from .mdb to .accdb, but that doesn't seem to have helped much.

I also had an issue with AutoNumber, which I've read has caused issues when using compact and repair because it can reuse numbers and cause issues. I've thought about programming a different field that isn't AutoNumber for those tables.



But I feel like I need a long term fix? Is there some type of web-based application that would be better than everyone trying to access a shared database from different networks? I don't work in IT, but if I need to get them involved, I guess I need to know what to ask them.

I'm pretty good with the basics of Access, but since it isn't my primary job, I haven't learned anything new in a while. Thank you for the help!

[ranman256]

we were getting a lot of corruption on shared network databases until a fix was found:

make a textfile , paste:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\LanmanWorkstation\Parameters]
"FileInfoCacheLifetime"=dword:00000000
"FileNotFoundCacheLifetime"=dword:00000000
"DirectoryCacheLifetime"=dword:00000000


save as: Access Reg Fix.reg
dbl-click to execute.

Every pc that got this never corrupts anymore.

[KTrueblood]

Sorry I'm a newb when it comes to network issues. Each user does this on their PC (outside of Citrix), or do they need to do this within the Citrix environment?

[ranman256]

not sure about Citrix. that fix is for Windows.

[KTrueblood]

Ok thank you! I'll talk with my IT folks and see what they think.

[CJ_London]

we use a remote desktop (Citrix) which houses the back end on the network, and each user still has a copy of the front end on their Citrix desktop.

one of the benefits or citrix (and terminal server) is that both front and backends are on the server, all that passes across the network is keyboard and mouse instructions one way and screen refresh information the other. So a flaky LAN, unstable internet connections or different types of networks is unlikely to be the source of corruption, since in the event of a loss of connection, the FE will still be running in the users personal domain - it would be similar to a user putting their machine to sleep. When it is woken up, all the apps that were open are still there.

And just to be clear, your BE is not approaching 2Gb in size?

I also had an issue with AutoNumber, which I've read has caused issues when using compact and repair because it can reuse numbers and cause issues.

that might have been the case for .mdb's, but not .accdb's.

Once corruption has set in, it is unlikely that a compact/repair will fix it. Think of it like a weed growing. Compact/repair might remove the top leaves but the root remains to grow again.

Recommend you do the following

1. Create a brand new db for your BE
2. import (not copy/paste) the tables - you might find at this point that one or more tables won't import. So having narrowed it down, you will need to inspect every value in the table - sorting A-Z and Z-A on each column one at a time may narrow it down to a specific column
3. compact/repair

Sometimes you have to do more - such as export each table from the old BE to to a .csv file, then import the .csv file to the new BE.