Access 2000 connecting to 2002 back-end file - Any known corruption issues?

R

Ragnar Midtskogen

Hello,

We have an Access 2000 multi user application linked to an Access 2002
back-end file. The back-end used to be in 2000 format, but when our client
upgraded to new machines recently he could not buy Office 2000 so he got XP.

We continued to use the 2000 format for a while and had few problems. And if
there were problems the client could fix them himself by running JetComp.

Some time ago someone converted the back-end file to 2002 format but left
the client app in 2000.
It seems there has been more corruption problem since then, although we
don't have any accurate logs.

Right now I am trying to fix a serious problem with corrupted records in two
tables, that JetComp could not handle.
I was unable to export the bad tables to a text file and had to resort to
copying an pasting block of records int a clean table.
Even that was much harder than usual since some of the bad records did not
display #### in all fields as they usually did

If anyone has anyone had any similar experiences I would appreciate to hear
about it.
TIA

Ragnar
 
T

Tony Toews

Ragnar Midtskogen said:
We have an Access 2000 multi user application linked to an Access 2002
back-end file. The back-end used to be in 2000 format, but when our client
upgraded to new machines recently he could not buy Office 2000 so he got XP.

We continued to use the 2000 format for a while and had few problems. And if
there were problems the client could fix them himself by running JetComp.

Some time ago someone converted the back-end file to 2002 format but left
the client app in 2000.

I wouldn't have though that would work. The BE being in A2002 format
and the FE in A2000. Nevertheless my first suggestion would be to
convert the BE back to A2000.
It seems there has been more corruption problem since then, although we
don't have any accurate logs.

I'd try the OpLocks fix first. Also ensure that all client
workstations are at the same msjet40.dll version.

For more information on corruption including possible causes,
determining the offending PC, retrieving your data, links, official MS
KB articles and a list of vendors who state they can fix corruption
see the Microsoft Access Corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm
I was unable to export the bad tables to a text file and had to resort to
copying an pasting block of records int a clean table.
Even that was much harder than usual since some of the bad records did not
display #### in all fields as they usually did

FWIW there is some code that can find the errors for you.

See the Damaged records within tables section in the To retrieve your
data from a Corrupt Microsoft Access MDB page at my website
http://www.granite.ab.ca/access/corruption/corruptrecords.htm

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
 
R

Ragnar Midtskogen

Thank you Tony,
I wouldn't have though that would work. The BE being in A2002 format
and the FE in A2000. Nevertheless my first suggestion would be to
convert the BE back to A2000.

That is what I did. Once I had restored the damaged tables I copied all the
objects into an Access 2000 file.
I'd try the OpLocks fix first. Also ensure that all client
workstations are at the same msjet40.dll version

I will try that on my next visit. I don't think they use thair computer for
anythin else than our app, so disabling OpLocks should be OK.
I will doublecheck Jet version, but all machines were set up with Office XP
recently, so it is probably the same.
See the Damaged records within tables section in the To retrieve your
data from a Corrupt Microsoft Access MDB page at my website

I tried using the recordset code to copying data to an empty table, but the
empty table has an s_Generation (Autonumber) field in it which gives me key
violations. I tried changing the field to number but Access won't let me
change the field, it claims it is a system field.
I am going to try redesigning the table from scratch to see if I can get
this to work.
If that does not work I will just use similar code to see if I can find the
bad records, so I can copy and paste by hand.

Again, thanks for the pointers.

Ragnar
 
R

Ragnar Midtskogen

Hi Tony,

One more question. What about converting the client app to Access 2002? Is
2002 generally less corruption prone than 2000?

What we really should do is to move to SQL Server back-end, the largest
table has over 60000 records, and they are complaining about performance
problems. Unforunately I don't hink the client is willing to pay for that,
because we have had very good experiences with that configuration.

The app has worked well over the years, the first version went live 9 years
ago, using Access 95. It was converted to 97 and then 2000 and is used by a
chain of carpet stores to take orders, schedule measurements and
installations, and to print invoices.

Ragnar
 
T

Tony Toews

Ragnar Midtskogen said:
I tried using the recordset code to copying data to an empty table, but the
empty table has an s_Generation (Autonumber) field in it which gives me key
violations. I tried changing the field to number but Access won't let me
change the field, it claims it is a system field.

That sounds like a replication field.

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
 
T

Tony Toews

Ragnar Midtskogen said:
One more question. What about converting the client app to Access 2002? Is
2002 generally less corruption prone than 2000?

Possibly. Jet is the more important component when it comes to
corruptions. Jet 4.0 SP8 or newer is what everyone should be on.
What we really should do is to move to SQL Server back-end, the largest
table has over 60000 records, and they are complaining about performance
problems. Unforunately I don't hink the client is willing to pay for that,
because we have had very good experiences with that configuration.

60K records is peanuts for Access. I have a client with 400K records
in tables and performance is fine. I'd check into indexing of course.

But also visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

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
 
R

Ragnar Midtskogen

It is, but it is not being used, so I thought I could delete it. As far as I
know we have never used replication for this application.
From what I remember about replication., as long as you have not created a
replica there should not be any problem deleting the replication fields.

Ragnar
 
R

Ragnar Midtskogen

Hi Tony,

Thanks for the info.

Would you happen to know how I could have someone at my client check what SP
of Jet they have?
My take would be to look at MSJet40.dll version. I have seen a tables of SPs
vs versions but now I can't find any.
60K records is peanuts for Access. I have a client with 400K records
in tables and performance is fine. I'd check into indexing of course.

Wow, is one table? If so, does it have many fields?

Ragnar
 
R

Ragnar Midtskogen

Hello Tony,

There is a happy ending to this story, I was able to recover the whole
table.

I gradually remembered the basics of replication so I figured out how to get
around the key problems

I created a copy operation function that used recordsets to copy the data
but I used queries and did not select any of the three replication fields,
s_Generation, s_GUID and s_Lineage.

Only s_GUID had any data in it, and it is an Autonumber fields of
Replication ID type (128 bits) so the new table created a new set of
entries. Since we don't use replication that was OK.

The corruption must have been in one of the replication fields, probably the
s_GUID, because all the records came across.

Thanks for all the help!

Ragnar
 
T

Tony Toews

Ragnar Midtskogen said:
Would you happen to know how I could have someone at my client check what SP
of Jet they have?

Several methods.

=== Manually =========================

Make sure you have the latest Jet 4 Service Pack installed.

To determine if you have the latest version of Jet installed you
search on msjet40.dll on your system. You should only find the one
in your WIndows\System 32 directory. When you right click on the
file >> Properties >> Version you can see the version number.

Jet 4.0 SP 6 - 4.0.6218.0
Jet 4.0 SP 8 - 4.0.8015.0
Microsoft Security Bulletin MS04-014 - 4.0.8618.0

For a somewhat complete list of msjet40.dll versions see
How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
Database Engine
http://support.microsoft.com/default.aspx?kbid=239114

If Windows XP ignore the msjet40.dll files found in the following
directories:
C:\WINDOWS\LastGood\System32
C:\WINDOWS\ServicePackFiles\i386

If Windows 2000 ignore the msjet40.dll files found in the following
directories:
C:\WINNT\ServicePackFiles\i386

=== Programmatically =========================

What I've done is use the various API calls available and am checking
the version number and date/time of a crucial dll, msjetxx.dll, to
ensure it matches what I have on my system. See the Verify
Appropriate Jet Service Pack is installed page at my website for more
details including sample code:
www.granite.ab.ca\access\verifyjetsp.htm
Wow, is one table? If so, does it have many fields?

Actually one table has 600K records but it's not used much while four
other tables have 400K records each. Five or ten more have 50K or
100K records. BE is 150 tables and 300 Mb in size. Umm, I'd have to
look but they likely have at least 10 fields each, maybe 15 or 20.

FE has, last time I looked, 1200 queries, 450 forms, 350 reports and
70K lines of code.

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
 
T

Tony Toews

Ragnar Midtskogen said:
One more question. What about converting the client app to Access 2002? Is
2002 generally less corruption prone than 2000?

Let me clarify this a bit. It seems that each newer version can open
successfully open and repair more corrupted databases than the
previous version. For example one suggest for A97 corrupted MDBs is
to try to open them in A2000 and newer and frequently they could fix
the problem.

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
 
T

Tony Toews

Tony Toews said:
Possibly. Jet is the more important component when it comes to
corruptions. Jet 4.0 SP8 or newer is what everyone should be on.

And just to clarify this. Everyone should be on the same Jet version.
Different Jet versions can cause problems.

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
 
R

Ragnar Midtskogen

Thanks for the info Tony,

I am astonished! Must be carefully designed and be running on a stable
network.

Ragnar
 
T

Tony Toews

Ragnar Midtskogen said:
I am astonished! Must be carefully designed and be running on a stable
network.

I designed and built it. <smile> And yup the IT department there are
competent.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top