UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Bad Data Corruption, Access 2003    
 
   
youngb
post Nov 10 2017, 09:30 AM
Post#1



Posts: 552
Joined: 2-September 03
From: Galway, Ireland


Hi,

I have been using access now for about 20 years for a large range of application, and the amount of corrupt data and records would be rare,
and would be only one record in one table but recently I have had three cases of data corruption like I have never come across before, and just one more this morning,
The client is using Access 2003 runtime on Windows 7 for the last 5 years with no problems before this,
I was not able to attached the data file because they were too large over 50 mb, and the only way to reduce the size is too compact and repair.

This morning it was a case of data corruption of 66 records in one table see attached jpeg, and on this occasion I can't even do a 'Compact and Repair' even after I have remove the offending table.
Now I do have a back up from this morning, but my question is does anybody have any idea of what would cause that kind of corruption, in a backend data file,
The application has individual frontends on the C drive of each computer link back to a server which has been set up by a professional

In my experience the most common source of date file corruption is when some user stops the process in the middle of a write to the data by shutting down the pc or using 'Control Alt Delete' which can be repaired by a 'Compact and repair' the other source could be the 'Net Work' connection but it is really hard to figure what would cause this kind of large scale corruption, on the previous occasion I have since done a compact and repair on the data and the users have re-entered the data the lost about 120 records but on this occasion I can't even do that.

Thanks in advance for any suggestion.

Brian
Attached File(s)
Attached File  Corrupt_Deliveries_1.jpg ( 456K )Number of downloads: 28
Attached File  Corrupt_Deliveries_2.jpg ( 453.5K )Number of downloads: 26
 
Go to the top of the page
 
John Vinson
post Nov 10 2017, 09:36 PM
Post#2


UtterAccess VIP
Posts: 4,276
Joined: 6-January 07
From: Parma, Idaho, US


If you have not done so already, I'd (strongly) suggest creating a completely new, empty backend database (all users should be locked out); import all the tables from a working copy (current if possible, most recent working backup if not); compact and repair this NEW database; and relink to it. There are some kinds of corruption which C&R is unable to fix.

You should also be sure that all the users are connecting over a fast, stable, wired LAN - wireless connections, VPN, or <shudder> links over the Internet can and will cause corruption and data loss. If users need to communicate from remote sites (not on the corporate LAN) then you should strongly consider having them use terminal emulation (Citrix or the like) to connect to a box on the LAN, containing that user's copy of the frontend in a private directory, linked to the shared backend.
Go to the top of the page
 
youngb
post Nov 13 2017, 05:33 AM
Post#3



Posts: 552
Joined: 2-September 03
From: Galway, Ireland


Hi John,

Thanks for the response,

that is indeed what I did, excluding the two corrupt tables, for the two corrupt tables I appended the good data to a new table and the imported in, so I am back up and running,
but I am more concerned about the fact that this is not a one off and has occurred 4 times in the last six months,
to answer your question, no they are not using and wireless(I have had to back track on a Wireless set up before as it was not just reliable enough in a manufacturing environment) and is all using fast stable, wired lan,
the only thing that does stand out is there are using a Nas drive as the server, (Nas Server), I am a little suspect to the fact that this occurred on 3 out of the 4 occasions on a Friday morning,
I know there are a lot of back up's running, I am wondering could this be caused by some backup that would lock the mdb backend file so the edit records can't write back to the database.
It has to be something like this as the 67 records that are corrupt were of course the ones that were been edited that morning.

As a process of elimination and I am considering moving the backend database file to a shared drive on a different pc just to see if this will solve the problem.

Regards
Brian
Go to the top of the page
 
youngb
post Feb 14 2018, 12:34 PM
Post#4



Posts: 552
Joined: 2-September 03
From: Galway, Ireland


Hi Guys,

FYI

my conclusion is that it was the Nas drive as I moved the backend Access database to a different drive/computer and after 3 months no issues,
definitely think it was something to do with the Nas drive running a backup,
so worth watching out for!!
Go to the top of the page
 
John Vinson
post Feb 17 2018, 01:59 PM
Post#5


UtterAccess VIP
Posts: 4,276
Joined: 6-January 07
From: Parma, Idaho, US


So... Can we conclude that for Access, NAS = Nasty? fundrink.gif
Go to the top of the page
 
DanielPineault
post Feb 17 2018, 04:04 PM
Post#6


UtterAccess VIP
Posts: 6,276
Joined: 30-June 11



Not all NAS drives are created equal. Some work just fine with Access, others cause such problems. That said, a NAS is a Storage device and not meant to be used as a server for managing multi-user databases.

I know when I did some research, a while back for a client who's database wasn't performing well on their NAS, I found it had to do with op-locks and some other settings. In that case, the solution was to share a folder off of a PC.


Go to the top of the page
 
youngb
post Feb 20 2018, 06:57 AM
Post#7



Posts: 552
Joined: 2-September 03
From: Galway, Ireland


Hi Guys,

it is very good to know that NAS drives have caused problems elsewhere because I wasn't aware of that,
it was just process of elimination that I came to the conclusion that it could be a source of my data corruption,
now it may not always be a problem but it is good to be aware that it can be a problem,
and it is the kind of problem that you need to avoid, can do serious damaged to user's confidence in the Database Application,
Also very valid point that NAS is a Storage Device and not meant to be used as a server managing multi user database.

So I would say
QUOTE
Access' with NAS 'User/Developer Beware!!'


Thanks For the Feedback.

Brian
Go to the top of the page
 
DanielPineault
post Feb 20 2018, 12:27 PM
Post#8


UtterAccess VIP
Posts: 6,276
Joined: 30-June 11



Funny to see this discussion as I just got off the phone with another client who just had the db corrupt on their Seagate NAS after running fine for the past several months. iconfused.gif pullhair.gif

So, if you decide to use a NAS to host an Access database, backups become even more critical than they normally are (and make sure they're done on another device), but I think the general consensus would be to keep Access away from NAS drive. They don't appear to play happily together.


Go to the top of the page
 
youngb
post Feb 23 2018, 06:48 AM
Post#9



Posts: 552
Joined: 2-September 03
From: Galway, Ireland


That is Interesting,
Backups become more important indeed it is always good to check them!!
but don't like having to use them too frequently!!
Thanks for the feedback
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th December 2018 - 07:18 PM