By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Cause Of Data File Corruption?

P: n/a
I have an Access 2003 data file that has now corrupted twice in a
week. The database is extremely simple with one main data table and a
few lookup tables. The lookup tables are linked to the main table via
relationships. Each user has their own copy of the frontend which
links back to the data file on the server.

The corrupted data file repairs OK but on both occasions 2
relationships have disappeared in the Relationships Window. When
these are reinstated all is back to normal. No data is corrupted.

It is probably hard to tell, but can this sort of corruption be caused
by the usual faulty network card or connection or can there be other
causes? Is there anything that I can do to track down the actual
cause?

Aug 26 '08 #1
Share this Question
Share on Google+
16 Replies


P: n/a
The problem usually stems from having a shared mdb on a network. If
more than one user has the application open and someone else performs a
compact/repair on it - that is where the problem arises. This is a
reason (of several) why using a File based RDBMS (like Access) as a
shared system over a network like a Server based RDBMS (like sql server)
will result in problems.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 26 '08 #2

P: n/a
Rich P <rp*****@aol.comwrote in
news:12*************@news.newsfeeds.com:
The problem usually stems from having a shared mdb on a network.
If more than one user has the application open and someone else
performs a compact/repair on it - that is where the problem
arises. This is a reason (of several) why using a File based
RDBMS (like Access) as a shared system over a network like a
Server based RDBMS (like sql server) will result in problems.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Rich,

you are talking through your hat. Firstly, the OP states that his
users each have their own copy of the front end. A compact and
repair will not happen on a shared back end as long as the .ldb
indicates other users have the .mdb open. So that's not the OP's
problem either.

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **
Aug 26 '08 #3

P: n/a
Sorry, Rich, you're mistaken. When others have the app open, no one can
perform and compact/repair on the back end db. It doesn't corrupt, it just
fails to compact.

The OP stated that each user has their own front end, so if they
compact/repair their front end, no one else has that db file open. It
doesn't corrupt, it compacts as directed.

Having a shared db on a network doesn't cause corruption. It's not setting
it up properly (many sharing the same front end or not even splitting the
shared db), cutting the power while writing to disk, faulty network, wireless
connections on the network and opportunistic locks from the file server that
cause corruption.

Chris
Microsoft MVP
Rich P wrote:
>The problem usually stems from having a shared mdb on a network. If
more than one user has the application open and someone else performs a
compact/repair on it - that is where the problem arises. This is a
reason (of several) why using a File based RDBMS (like Access) as a
shared system over a network like a Server based RDBMS (like sql server)
will result in problems.

Rich
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 27 '08 #4

P: n/a
A faulty network card can cause corruption, but missing relationships is an
odd form of corruption. Are you sure one of your users isn't messing with
the relationships window in the back end so they can get one of their forms
to work?

Look in your code for "db.relations.delete..." and look in your code and
queries for any "alter table tablename drop constraint..." sql that might be
causing the problem.

Chris
Microsoft MVP
Wayne wrote:
>I have an Access 2003 data file that has now corrupted twice in a
week. The database is extremely simple with one main data table and a
few lookup tables. The lookup tables are linked to the main table via
relationships. Each user has their own copy of the frontend which
links back to the data file on the server.

The corrupted data file repairs OK but on both occasions 2
relationships have disappeared in the Relationships Window. When
these are reinstated all is back to normal. No data is corrupted.

It is probably hard to tell, but can this sort of corruption be caused
by the usual faulty network card or connection or can there be other
causes? Is there anything that I can do to track down the actual
cause?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 27 '08 #5

P: n/a
On Aug 27, 10:47*am, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:
A faulty network card can cause corruption, but missing relationships is an
odd form of corruption. *Are you sure one of your users isn't messing with
the relationships window in the back end so they can get one of their forms
to work?

Look in your code for "db.relations.delete..." and look in your code and
queries for any "alter table tablename drop constraint..." sql that mightbe
causing the problem.

Chris
Microsoft MVP
Thanks Chris. No, users aren't messing with the backend. The file
becomes corrupt and needs a repair. After that I look at the
relationships and a couple of them are gone. It's weird. When I find
the cause I will post back here.
Aug 27 '08 #6

P: n/a
Weird is right. Have you tried creating a new db file and importing all the
tables and relationships into it? That might get rid of any weird stuff in
the system tables. Relationships are stored in the system table
msysrelationships.

Chris
Microsoft MVP
Wayne wrote:
>Thanks Chris. No, users aren't messing with the backend. The file
becomes corrupt and needs a repair. After that I look at the
relationships and a couple of them are gone. It's weird. When I find
the cause I will post back here.
--
Message posted via http://www.accessmonster.com

Aug 27 '08 #7

P: n/a
I'm not sure if the app and the backend are being confused here, but a
backend can be compacted when more than one front end is linked to it,
provided no one else has the backend opened exclusively.
Will that corrupt the backend? Not in my experience.

On Aug 26, 8:26*pm, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:
Sorry, Rich, you're mistaken. *When others have the app open, no one can
perform and compact/repair on the back end db. *It doesn't corrupt, it just
fails to compact.
Aug 27 '08 #8

P: n/a
Compacting the db requires exclusive access to the file. If anyone else is
connected to the back end at the same time you want to compact it, the back
end can't be compacted. But that doesn't corrupt it.

See the pitfalls section near the bottom of this kb article.

http://support.microsoft.com/kb/306287

Chris
Microsoft MVP
lyle fairfield wrote:
>I'm not sure if the app and the backend are being confused here, but a
backend can be compacted when more than one front end is linked to it,
provided no one else has the backend opened exclusively.
Will that corrupt the backend? Not in my experience.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 27 '08 #9

P: n/a
I believe that this procedure has compacted various "backends" for me
for many years and seems to work in the present when more than one
user is linked to the backend, provided that those uers are idle.

Option Base 0
Option Explicit

Private Const AttachedTable& = 6
Private Const FileNotFoundErrNumber& = 53
Private Const Notify As Boolean = False

Public Sub CompactAttachedTableMDBS()
will fail if no reference to DAO
On Error GoTo CompactAttachedTableMDBSErr
Dim rcs As DAO.recordset
Dim SQL$
If Forms.Count Or Reports.Count Then
MsgBox "Please, close all forms and reports, and retry.",
vbExclamation, "FFDBA"
Else
SQL = "SELECT Distinct CStr(DataBase) AS db" _
& " FROM MSysObjects WHERE Type=" & AttachedTable
With DBEngine(0)(0)
.TableDefs.Refresh
Set rcs = .OpenRecordset(SQL)
With rcs
Do While Not .EOF
If DoesFileExist1997(!db) Then
If CanBeOpenedExclusively(!db) Then
Shell SysCmd(acSysCmdAccessDir) &
"MsAccess.Exe " & """" & !db & """" & " /compact"
If Notify Then
MsgBox "Successfully Compacted" _
& vbCrLf _
& !db & "." _
, vbInformation, "FFDBA"
End If
Else
MsgBox "Can't compact" _
& vbCrLf _
& !db & "." _
& vbCrLf _
& "Database seems to be opened exclusively
by another user.", vbExclamation, "FFDBA"
End If
Else
MsgBox "Can't compact" _
& vbCrLf _
& !db & "." _
& vbCrLf _
& "Database seems to have been moved or
deleted.", vbExclamation, "FFDBA"
End If
.MoveNext
Loop
.Close
End With
End With
End If
CompactAttachedTableMDBSExit:
Set rcs = Nothing
Exit Sub
CompactAttachedTableMDBSErr:
With Err
MsgBox .Description, vbCritical, "Error: " & .Number
End With
Resume CompactAttachedTableMDBSExit
End Sub

Private Function CanBeOpenedExclusively(ByVal FullPath$) As Boolean
Dim d As Database
Dim p As PrivDBEngine
Set p = New PrivDBEngine
On Error Resume Next
Set d = p(0).OpenDatabase(FullPath, True)
CanBeOpenedExclusively = Not (d Is Nothing)
p(0).Close
Set d = Nothing
Set p = Nothing
End Function

Public Function DoesFileExist1997(ByVal FilePath$) As Boolean
On Error GoTo DoesFileExist1997Err
GetAttr FilePath
DoesFileExist1997 = True
DoesFileExist1997Exit:
Exit Function
DoesFileExist1997Err:
With Err
If .Number <FileNotFoundErrNumber Then
MsgBox .Description, vbCritical, "Error Number: "
& .Number
End If
End With
Resume DoesFileExist1997Exit
End Function

On Aug 27, 1:41*am, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:
Compacting the db requires exclusive access to the file. *If anyone else is
connected to the back end at the same time you want to compact it, the back
end can't be compacted.
Aug 27 '08 #10

P: n/a
There is, of course, no limit to the weirdness that corruption can cause. One
cause of repeated corruption that Allen Browne usually cites is the Name
AutoCorrect feature. Goto Tools - Options - General and uncheck everything in
the Name AutoCorrect box.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 27 '08 #11

P: n/a
My appologies. It sounds like I said something that was inaccurate
about Access.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 27 '08 #12

P: n/a
Maybe! What exactly did you have in mind?

On Aug 27, 4:03*pm, Rich P <rpng...@aol.comwrote:
My appologies. *It sounds like I said something that was inaccurate
about Access.
Aug 27 '08 #13

P: n/a
Prior experience. Whenever we had a shared mdb on the network (pre-sql
server days - so we are talking old Access - well, pre sql server before
we started migrating to sql server) if someone tried to close the app or
compact it while someone else had it open - the app would become
unusable by anyone - I don't remember the exact scenario but do remember
that Access used to get corrupted alot back then. I think that might
not have been the case with the OP here. Hmmm, maybe I should read the
post more slowly/carefully.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 27 '08 #14

P: n/a
Looks as if you have a similar problem to the one I posted to
comp.databases.ms_access on 18th August. Unfortunately I got no real
resolution to the problem other than ignore it

Phil

"Rich P" <rp*****@aol.comwrote in message
news:12*************@news.newsfeeds.com...
Prior experience. Whenever we had a shared mdb on the network (pre-sql
server days - so we are talking old Access - well, pre sql server before
we started migrating to sql server) if someone tried to close the app or
compact it while someone else had it open - the app would become
unusable by anyone - I don't remember the exact scenario but do remember
that Access used to get corrupted alot back then. I think that might
not have been the case with the OP here. Hmmm, maybe I should read the
post more slowly/carefully.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Aug 27 '08 #15

P: n/a
I love Access, and it is a great tool -- and Microsoft is a business -
in the business to sell its software. When used correctly - Access is
quite robust and reliable for a small file based rdbms. In its early
days - the network file sharing feature of Access was pretty big, but
had problems. The Microsoft fix for these problems was to create a
whole new paradigm - a server based RDBMS (sql server). The file
sharing features are still available in Access because that was the
original design - despite the issues. The fix for these issues is/was
-- there is no fix. You are on your own when networking with Access. I
took the MS route for my network fix. In the meantime, I still use
Access for non-network based (non-internet) projects.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 27 '08 #16

P: n/a
lyle fairfield <ly************@gmail.comwrote:
>I believe that this procedure has compacted various "backends" for me
for many years and seems to work in the present when more than one
user is linked to the backend, provided that those uers are idle.
Define idle. Your definition means there aren't any open forms with a bound
recordsource or combo box or list box or similar or report or recordset/database
variable defined in code as being open. If that is the case then yes they are idle
and a rename/compact of the backend can occur. Which is what I do.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Aug 30 '08 #17

This discussion thread is closed

Replies have been disabled for this discussion.