MSysCompactError Strange Error Recorded

[David]

I am familar with Access. However, after converting to ACCESS 2007 some
'WEIRD' things have been occurring that I have not see in previous versions.
1. After a Compact and Repair Database Access occassionally drops all
changes since the previous Compact and Repair. Tables, Queries, Macros,
Reports, Projects(VBA) all disappear. I have to rely on backup MDBs to
recover these objects.
2. No error messages populate in the MSysCompactError table.
3. Occassionally, I will see an entry in the MSysCompact Error indicating
that a particular field cannot be found. Error Code -1057. The field the
error is referencing has not existed for weeks! This error keeps popping up
after a Compact and Repair but not after every Compact and Repair. The field
being referenced does not exist!!!! Really strange!
4. I entered the DateDiff Fuction in the Field row in a Query column per
norm.
ResearchDur: DateDiff("s",[Resolution / Workaround].[Acknowledgment
Date],[Resolution / Workaround].[Research Completed]) After one Compact and
Repair I started receveiving a Compile error indicating there was a syntax
error in the Date Diff Function. Which there was not. It kept telling me
there was an extra '.' period.
But there was not. This function was being used in this query for days
successfully.
Whats really weird is everytime I attempted to recreate the Function Access
would changed what I Typed after tabing out of the column to:
ResearchDur: DateDiff("",[Resolution / Workaround].[Acknowledgment
Date],[Resolution / Workaround].[Research Completed])
Notice that Access kept trying to place brackets around the time interval.
Which is not the correct syntax for the DateDiff Function. At this point I
am contempalting returning to Access 2003 for all our companies MDBs. Any
help or suggestions you can provide is appreciated! Thanks!

 

[Allen Browne]

David, others are also reporting that there are strange bugs with
Compact/Repair in Access 2007. It would be really helpful if we could pin
down what is causing the problem. That means eliminate some possible causes
that are not contributing to the problem, and narrow down the circumstances
under which the problem is seen.

First up, is Name AutoCorrect turned off? Details:
http://allenbrowne.com/bug-03.html
This is a major contributor to corruption in all versions of Access, and the
first thing to check. The misassigned names you mention suggest this as a
possibility. Actually, I saw a similar problem yesterday in an A2007
database, where a query complained about a field not existing. In this case,
I was able to solve the problem by typing into the Immediate Window:
? Currentdb.QueryDefs("Query1").SQL
then deleting the query, and creating a new one using the same SQL
statement. The scary part is that Name AutoCorrect was turned off, and yet
the symptoms were just like the problems caused by this misfeature.

If Name AutoCorrect has been on, I suggest you perform this sequence:
a) Turn it off.
b) Compact.
c) Decompile.
d) Compact again.
e) Create a new database.
f) Immediately turn off Name AutoCorrect in the new database.
g) Set your references in the new database
h) Import everything from the old database.
That *should* get rid of whatever is causing the problem.

If things are still weird, what version of Windows are you running on?
Are you logged in as a computer administrator?
Where is your temp folder?
Is there lots of free space on this drive?
Delete any files left in the temp folder.

Also, have you added your database folder as a trusted location?

If you open Regional Settings in the Windows Control Panel, what character
is set as the List Separator? If it is not the comma, it may affect how the
arguments are separated in DateDiff(), in the context of the query design
window.

Anything else you can pinpoint about the compact problems would be helpful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am familar with Access. However, after converting to ACCESS 2007 some
'WEIRD' things have been occurring that I have not see in previous
versions.
1. After a Compact and Repair Database Access occassionally drops all
changes since the previous Compact and Repair. Tables, Queries, Macros,
Reports, Projects(VBA) all disappear. I have to rely on backup MDBs to
recover these objects.
2. No error messages populate in the MSysCompactError table.
3. Occassionally, I will see an entry in the MSysCompact Error indicating
that a particular field cannot be found. Error Code -1057. The field the
error is referencing has not existed for weeks! This error keeps popping
up
after a Compact and Repair but not after every Compact and Repair. The
field
being referenced does not exist!!!! Really strange!
4. I entered the DateDiff Fuction in the Field row in a Query column per
norm.
ResearchDur: DateDiff("s",[Resolution / Workaround].[Acknowledgment
Date],[Resolution / Workaround].[Research Completed]) After one Compact
and
Repair I started receveiving a Compile error indicating there was a syntax
error in the Date Diff Function. Which there was not. It kept telling me
there was an extra '.' period.
But there was not. This function was being used in this query for days
successfully.
Whats really weird is everytime I attempted to recreate the Function
Access
would changed what I Typed after tabing out of the column to:
ResearchDur: DateDiff("",[Resolution / Workaround].[Acknowledgment
Date],[Resolution / Workaround].[Research Completed])
Notice that Access kept trying to place brackets around the time interval.
Which is not the correct syntax for the DateDiff Function. At this point
I
am contempalting returning to Access 2003 for all our companies MDBs. Any
help or suggestions you can provide is appreciated! Thanks!

 

[Vladimír Cvajniga]

This is (just another) really serious warning for me not to continue with MS
Access. I wonder how this can happen after beta tests. To me it seems they
should have not released MS Office 2007 at all - they'd better wait for
2008.

Also, I don't understand why Microsoft doesn't remove Name AutoCorrect from
Access after all those reported bugs.

What I have read recently read about MS Access:
"It's not a bug, it's an elephant." :-/

Bad news this evening... :-(

Vlado

P.S.
Allen, is there a list of all known bugs in A2007 that people have reported
so far?

David, others are also reporting that there are strange bugs with
Compact/Repair in Access 2007. It would be really helpful if we could pin
down what is causing the problem. That means eliminate some possible
causes that are not contributing to the problem, and narrow down the
circumstances under which the problem is seen.

First up, is Name AutoCorrect turned off? Details:
http://allenbrowne.com/bug-03.html
This is a major contributor to corruption in all versions of Access, and
the first thing to check. The misassigned names you mention suggest this
as a possibility. Actually, I saw a similar problem yesterday in an A2007
database, where a query complained about a field not existing. In this
case, I was able to solve the problem by typing into the Immediate Window:
? Currentdb.QueryDefs("Query1").SQL
then deleting the query, and creating a new one using the same SQL
statement. The scary part is that Name AutoCorrect was turned off, and yet
the symptoms were just like the problems caused by this misfeature.

If Name AutoCorrect has been on, I suggest you perform this sequence:
a) Turn it off.
b) Compact.
c) Decompile.
d) Compact again.
e) Create a new database.
f) Immediately turn off Name AutoCorrect in the new database.
g) Set your references in the new database
h) Import everything from the old database.
That *should* get rid of whatever is causing the problem.

If things are still weird, what version of Windows are you running on?
Are you logged in as a computer administrator?
Where is your temp folder?
Is there lots of free space on this drive?
Delete any files left in the temp folder.

Also, have you added your database folder as a trusted location?

If you open Regional Settings in the Windows Control Panel, what character
is set as the List Separator? If it is not the comma, it may affect how
the arguments are separated in DateDiff(), in the context of the query
design window.

Anything else you can pinpoint about the compact problems would be
helpful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am familar with Access. However, after converting to ACCESS 2007 some
'WEIRD' things have been occurring that I have not see in previous
versions.
1. After a Compact and Repair Database Access occassionally drops all
changes since the previous Compact and Repair. Tables, Queries, Macros,
Reports, Projects(VBA) all disappear. I have to rely on backup MDBs to
recover these objects.
2. No error messages populate in the MSysCompactError table.
3. Occassionally, I will see an entry in the MSysCompact Error indicating
that a particular field cannot be found. Error Code -1057. The field
the
error is referencing has not existed for weeks! This error keeps popping
up
after a Compact and Repair but not after every Compact and Repair. The
field
being referenced does not exist!!!! Really strange!
4. I entered the DateDiff Fuction in the Field row in a Query column per
norm.
ResearchDur: DateDiff("s",[Resolution / Workaround].[Acknowledgment
Date],[Resolution / Workaround].[Research Completed]) After one Compact
and
Repair I started receveiving a Compile error indicating there was a
syntax
error in the Date Diff Function. Which there was not. It kept telling
me
there was an extra '.' period.
But there was not. This function was being used in this query for days
successfully.
Whats really weird is everytime I attempted to recreate the Function
Access
would changed what I Typed after tabing out of the column to:
ResearchDur: DateDiff("",[Resolution / Workaround].[Acknowledgment
Date],[Resolution / Workaround].[Research Completed])
Notice that Access kept trying to place brackets around the time
interval.
Which is not the correct syntax for the DateDiff Function. At this point
I
am contempalting returning to Access 2003 for all our companies MDBs.
Any
help or suggestions you can provide is appreciated! Thanks!

 

[David W. Fenton]

If Name AutoCorrect has been on, I suggest you perform this
sequence: a) Turn it off.
b) Compact.
c) Decompile.
d) Compact again.
e) Create a new database.
f) Immediately turn off Name AutoCorrect in the new database.
g) Set your references in the new database
h) Import everything from the old database.
That *should* get rid of whatever is causing the problem.

Decompiling has no effect whatsoever on Name Autocorrect issues in
queries.

 

[Allen Browne]

Vladimir, firstly, I owe you an apology.
The issue I referred to was not due to Name AutoCorrect.

Tom Wickerath (Access MVP) suggested I check the OrderBy property of the
query. Sure enough, that's where the spurious parameter was. Hence, it
wasn't in the SQL statement, but it was in the query, and so Access was
correct in requesting it. Not only was it not a Name AutoCorrect issue, but
Access was behaving correctly, exactly as designed. My bad.

As for realistic expectations, all software has bugs. That includes the
database you write, the ones I write, and the software Microsoft writes.
Access 2007 has so much new stuff in it, that there's guaranteed to be many
bugs. See:
http://allenbrowne.com/Access2007.html#Bugs
for the significant ones I am aware of that apply just in Access (i.e. not
including interactions with other software.) I expect most of these will be
fixed when MS releases Service Pack 1.

If you feel that it is best not to deploy the new version until the service
pack is released so you are not living on the "bleeding" edge, you are not
alone. In fact, that's exactly what I recommend you do:
http://allenbrowne.com/Access2007.html#Buy

Sometimes I find Access very frustrating too. But there's not many other
products like it that give you a multi-user relational data engine with SQL
querying capacity, a simple design interface exposing data-centric events
and subforms for related data, flexible reporting, and a complete
programming language. Using number of units sold as criteria, Access is
probably the most popular database of all time.

 

[David W. Fenton]

Sometimes I find Access very frustrating too. But there's not many
other products like it that give you a multi-user relational data
engine with SQL querying capacity, a simple design interface
exposing data-centric events and subforms for related data,
flexible reporting, and a complete programming language.

Are there any at all any more? In active development?

 

[Vladimír Cvajniga]

TYVM for your respond, Allen.

All MVPs recommend to switch off the Name AutoCorrect in Access database. I
don't understand why Microsoft guys & gals ignore all Name AutoCorrect
problems. IMHO, the simpliest way to fix the bug is to remove it. In large
projects with dynamic functions Name AutoCorrect can't resolve all
situations anyway.

Vlado