Cause Of Data File Corruption?

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?

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 P wrote in
news:1219792367_2908-at-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 **

Sorry, Rich, you're mistaken. When others have the app open, no one can
perform and compact/repair on the back end Database. 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 Database file open. It
doesn't corrupt, it compacts as directed.

Having a shared Database 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 Database), 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

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 "Database.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

On Aug 27, 10:47*am, "Chris O'C via AccessMonster.com"
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 "Database.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.

Weird is right. Have you tried creating a new Database 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

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"
wrote:
> Sorry, Rich, you're mistaken. *When others have the app open, no one can
> perform and compact/repair on the back end Database. *It doesn't corrupt, it just
> fails to compact.

Compacting the Database 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

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 Database" _
& " FROM MSysObjects WHERE Type=" & AttachedTable
With DBEngine(0)(0)
.TableDefs.Refresh
Set rcs = .OpenRecordset(SQL)
With rcs
Do While Not .EOF
If DoesFileExist1997(!Database) Then
If CanBeOpenedExclusively(!Database) Then
Shell SysCmd(acSysCmdAccessDir) &
"MsAccess.Exe " & """" & !Database & """" & " /compact"
If Notify Then
MsgBox "Successfully Compacted" _
& vbCrLf _
& !Database & "." _
, vbInformation, "FFDBA"
End If
Else
MsgBox "Can't compact" _
& vbCrLf _
& !Database & "." _
& vbCrLf _
& "Database seems to be opened exclusively
by another user.", vbExclamation, "FFDBA"
End If
Else
MsgBox "Can't compact" _
& vbCrLf _
& !Database & "." _
& 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"
wrote:
> Compacting the Database 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.