473,378 Members | 1,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Strange Access2003 problems

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
Nov 13 '05 #1
2 1711
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

Nov 13 '05 #2
Allen-

Thanks for your reply. After reading your message, I realized that the 2
problematic tables were imported from an A97 mdb (I have the same database
in the 3 latest versions,A97, A2000 and now A2003-their for sewer billing
purposes). I think your suggestion regarding relationships may be a clue;
they were originally imported with their relationships intact.....anyways, I
have performed all the tasks you suggested; time will tell if they act up
again. It's funny, everything else works fine (code, OCX controls, action
queries, etc)...anyways, thanks again.

-Joe

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
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


Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
by: Neil Ginsberg | last post by:
I have a strange situation with my Access 2000 database. I have code in the database which has worked fine for years, and now all of a sudden doesn't work fine on one or two of my client's...
1
by: Dennis | last post by:
If I create a database in AccessXP-2000mode, can someone with Access2003 run it? Do either of us have to do anything special? Thanks! Dennis
9
by: Lauren Quantrell | last post by:
Hoping someonce can tell me what compatibility problems I might run into when I have to have my Access Project developed on Access2K run in a user environment where users are using Access2003 as...
6
by: Ryan Bounds | last post by:
Hi All We have upgraded from Access2000 to Access2003 with all service packs. The problem that we have is: When a user try's to filter a client the database crashes and close's Access2003,...
9
by: Zlatko Matić | last post by:
Hello. Could somebody explain the following situation: I have a .mdb on my notebook with Windows XP SP2 and Access 2003. I prepared ..mde and tried to use it on another computer with Windows XP...
3
by: Bugs | last post by:
Hi Everyone. This problem is driving me crazy. Im using Access2003. Basically what I wish to do is create a new record for each jpg bmp or gif file in the current directory. I need to...
9
by: 2D Rick | last post by:
With the help from members in the VB forum I've pieced together code that works in VB6 to create radial text similar to "text on a path" seen in graphics programs.(on a circle only) I use an...
3
by: Vera | last post by:
I built a class in VB.NET that has an overloaded constructor. It can either accept nothing, a string or an object Public Sub New( MyBase.New( End Su Public Sub New(ByVal strName As String...
13
by: usenet | last post by:
How and where can one find out about the basics of VB/Access2003 syntax? I am a died in the wool C/C++/Java Linux/Unix programmer and I am finding it difficult to understand the program format...
3
by: Ramchandar | last post by:
Hi, I am creating reports using VBA code. I have the same query in a querydef residing both in Access97 and Access2003. The result of this querydef is then moved to a table in Access97 and...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.