Corruption problems.

[Transdiv]

Hi, I'am an Access newbie, in a job I have, there is an old Access application (2013); We was thinking to update the App but a colleague of mine said me that Access databases are very prone to corruption; I comment that well we can backup daily the DB so we only lost one day job if corruption get's in (It's not a time critical App); But he said me that sometimes the corruption in Access can be very subtle and initially not noticiable, that the users can continue to use the App and the problems show up later when they use a not so used option. It's that true?; What's your experience in this regard?




ThxInAdv.
TransDiv

[Micron]

Most issues with Access are caused by the developer. For example,
M$ documentation says to not try to use Access over the cloud, (or IIRC, sync with the cloud either) nor to use it over WIFI, yet people do.
Inexperienced dev's design tables like spreadsheets, which they're not.
They also don't split db's into back end tables and front end portions. Or they do and share one front end on a network with all the users.
And so on.

I've built db's that I'm told are still being used today, but I've been retired now for 12 years.

[dblife]

I have come across something very similar but as Micron mentioned, it was caused by the initial developer.
The database was in a location on a shared network.
To fix it, I split the database to make the front end (forms etc) separate from the data tables.
I then kept 'locked' a copy of the front end superficially by disabling the navigation pane and right click options.
I created a small batch file (in Windows command line) in the same directory as the locked f.e. and gave each user a shortcut to the batch file on their desktop.
When they clicked the shortcut, it copied the latest 'locked' front end to their user Documents directory and opened from there to force them to use a local copy.
They only ever click on a shortcut to a batch file, never the database itself.
Finally I made sure the tables were all related in the correct way, so records HAD to be completed the way the relationships needed them to be so any queries based on the records showed correctly.
A couple of tweaks to include error handling in the VBA to make sure the code didn't show if something went wrong and hey presto, no more corruption.
Solid table relationships, good f.e. and b.e. separation and some well targeted error handling and you should be a good start.

[ArviLaanemets]

I prefer setup, where back-end and every user's front-ends are placed on directories on network server (especially after latest developments, where user's profile in local profile may be put into cloud instead keeping it on hard disk). Users open their front-ends through shortcuts in their desktop, or even better from shortcuts in their desktop of their Terminal Server profile. In case user needs to work with database outside of local LAN, the only working option will be:
he/she logs into LAN remotely, logs in into Terminal Sever, and starts the DB from there. And the latest policy is, that remotely can connect to network only those employees, who do this from company's laptop, and from profile, which is specifically allowed to connect remotely.

All database data changes are moved between servers only. Between user's computer and opened database instance, only the info about keyboard/mouse strokes, and the desktop picture, are moved.

[NoellaG]

Access is known to corrupt when it shuts down unexpectedly. When placing the database on a server this is less likely to happen. Or when a user leaves the connection open before going home, you can get into trouble.
If you have problems with this, or you have more than 10 concurrent users, you can leave the front end application in Access but move the backend tables to a database system.

[Transdiv]

Thx for the answers; A maybe silly question because I'am a newbie in Access; From what I have read it's important to separate the frontend from the backend; In other languages (VisualFox, CSharp, etc...), it's obvious, but in Access is not the Forms, Reports, VBA, etc... embebed in the same file with the data (.accdb)?; Can I create two Access files and one of them have the Forms, Reports, etc... and the other one, the data, and the first file access the second one? Or you are talking about using a SQL Server and accessing it from the first one that have the frontend?

[moke123]

Use the wizard to split your db Make a back up first. That will split your db into a front end with the forms,queries, modules,etc. and a back end with just the tables.

[ArviLaanemets]

Thx for the answers; A maybe silly question because I'am a newbie in Access; From what I have read it's important to separate the frontend from the backend; In other languages (VisualFox, CSharp, etc...), it's obvious, but in Access is not the Forms, Reports, VBA, etc... embebed in the same file with the data (.accdb)?; Can I create two Access files and one of them have the Forms, Reports, etc... and the other one, the data, and the first file access the second one? Or you are talking about using a SQL Server and accessing it from the first one that have the frontend?

Yest to both of your questions! You can have back-end as Access DB, as SQL Server DB, or any other database which allows Access front-end to link tables in it.

In back-end you keep all tables used by all users of your app. In case there are tables e.g. containing info specific to current user/instance, it is preferable to keep those tables in front-end.
In case you have saved queries which are used by all users of database, you can have them in back-end too, and link the query results in front-end the same way as you link tables, or you can have them in every user's front-end (and when you use SQL DB and want to use views defined there there in your DB, having them linked from back-end into front-end will be the only way) . What to prefer, depends on several factors.

[Transdiv]

Thx 2 All for your answers.