Hi Joe
This sounds unusual. I've never seen Access 2003 do anything like that
repeatedly.
First thought is a Name AutoCorrect corruption. This is still a major cause
of corruption in A2000, 2002, and 2003. Details:
http://members.iinet.net.au/~allenbrowne/bug-03.html
Another possibility is that the indexes are corrupted, but it would be less
likely to have separate corruptions in the indexes of 2 tables.
Here's a sequnce to try to build a reliable copy on A2003:
1. Make a backup copy of both databases (A2000 and A2003).
2. Using A2000, open the original mdb and make sure the Name AutoCorrect
boxes are unchecked under:
Tools | Options | General
Then compact the database:
Tools | Database Utilities | Compact.
3. Still in A2000, open the Relationships window, and delete any the
relationships these 2 tables are involved in.
4. Open each of the 2 tables in design view. Open the Indexes box (View
menu), and delete all the indexes including the primary key). Note: don't
delete the primary key field: just the index. Verify that there are no
hidden indexes left, by opening the Immediate window and entering something
like this:
? dbEngine(0)(0).TableDefs("MyTable").Indexes.Count
5. Still using A2000, decompile a copy of the database by entering something
like this at the command prompt while Access is not running. It is all one
line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
6. Compact again. Then close A2000.
5. Using A2003, open the problem database and ensure Name AutoCorrect is
off. Then compact. Decompile. Compact again.
6. Using A2003, create a new (blank) database. Immediately turn off the Name
AutoCorrupt check boxes.
7. In this new database, import the tables. Uncheck the box for importing
relationships (under the advanced or options button, from the Import
dialog).
8. Open the tables in design view, and recreate the indexes. Then recreate
the relations.
9. Import the other objects from your A2003 database.
10. From the code window, remove any references you don't need (Tools |
References), and check the project compiles.
This should give you a stable database, with no imported corruptions and
nothing internally that is likely to corrupt it.
More information on what will corrupt your database, and how to avoid them:
http://members.iinet.net.au/~allenbrowne/ser-47.html
Functions to list any indexes on a table (even hidden ones), and to
programmatically delete all relations in the database:
------------------code starts-----------------
Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field
Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IIf(ind.Primary, "Primary", ""), _
IIf(ind.Foreign, "Foreign", ""), ind.Fields.Count
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Next
End Function
Function DeleteAllRelationships() As String
' WARNING: Deletes all relationships in the current database.
Dim db As Database ' Current DB
Dim rex As Relations ' Relations of currentDB.
Dim rel As Relation ' Relationship being deleted.
Dim iKt As Integer ' Count of relations deleted.
Dim sMsg As String ' MsgBox string.
sMsg = "About to delete ALL relationships between tables in the current
database." & vbCrLf & "Continue?"
If MsgBox(sMsg, vbQuestion + vbYesNo + vbDefaultButton2, "Are you
sure?") = vbNo Then
DeleteAllRelationships = "Operation cancelled"
Exit Function
End If
Set db = CurrentDb()
Set rex = db.Relations
iKt = rex.Count
Do While rex.Count > 0
Debug.Print rex(0).Name
rex.Delete rex(0).Name
Loop
DeleteAllRelationships = iKt & " relationship(s) deleted"
End Function
------------------code ends-----------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Joseph Macari" <jm******@cox.net> wrote in message
news:EgFAd.15121$jn.4093@lakeread06...
I recently installed Office2003 on my computer. I had imported (not linked)
a couple of tables from an Access 2000mdb into an Access 2003mdb. I had
composed various queries and forms with these imported tables...saved the
2003mdb numerous times without incident. I was working on a form with
this
imported data (not linked) and all of a sudden the data just disapeared
(??)....i.e., the data in the two tables was empty!!! No problem; imported
the data again (2 tables); this time I copied the structure of the tables,
renamed them and copied the original imported data into the newly
structured
tables and continued working. I continued to work with the mdb file for
several sessions, saving, compacting, repairing, etc. without incident
again....wouldn't you know it, today the same thing happened...I was
creating a new form and again lost all the data in the two previously
mentioned tables. I've never seen something like this before...(been
using
access since ver 1.0).
I have updated Office2003 with the latest Office Update; if anybody has
any
suggestions, it would be appreciated. By the way, the original Access
2003mdb I have been working on was also imported from a 2000mdb without
incident.
-Thanks-
Joe
