Advice needed: Should we upgrade MS Access 2000? And if so to what?

[karen]

But that happens very, very seldom. I've only seen actual corruption
to the point of data loss a couple of times in 10 years of Access
development (all Jet back ends). And that happened in only two
different kinds of cases:

1. the client was doing no maintenance on the db at all, and storing
it on a workstation running unstable software that was constantly
crashing. The most memorable of these was back in the days of
Win3.x, and the client was running WordPerfect 6.0 on the
workstation where the Access 2 data file was stored.

2. in Access 2000, before the arrival of Jet 4 SP6, I had one client
who did lose 3 records to corruption, ones that couldn't be
recovered after a corrupted primary key index.

I also once corrupted a client's database by running a huge append
operation against the live data file, and then killing the Access
process. I intended to run it against test data, but made a mistake.
My second mistake was killing the process. The file had to be sent
to Peter Miller for recovery and all the data was recovered.

I currently have a client with an unreliable network who is getting
"disk or network error" messages frequently, and they have not as
yet experienced any corruption of their replicated Jet 3.5 back end.

So, I'd say that Jet is not all the prone to corruption at all.

Of course, I'm defining corruption to be restricted to
non-recoverable corruption, since the only reason server databases
don't lose data is because you can run the transaction logs to
rebuild a corrupted data store. That's not the same as never
corrupting the data store (which does, in fact, happen), so, since
you're giving the benefit of the doubt to the server back end, I'm
giving the same to Jet.

Ok, I usually lurk, but I have some questions about corruption. I've read
that the databases should be split, with data on the network, and the forms
and queries on each individual pc. We use Acess 97, not split, but with no
queries or forms. All of the user input and queries are done through
programs written in Visual C++ 6.0. The database lives on the network, the
programs live on each users' PC. I didn't write the actual database code,
so I assume it uses JET. It doesn't use ODBC. Most of the users have XP,
but a few still have Win98, so when I do get a chance to make changes in the
code, I compile it for both, and have separate installs. I do a compact and
repair on the databases about once a month, when everyone else has gone
home. The databases are small: the largest is about 30-35MB.

We get corruption, oh, maybe once a year or so. The network is slow
perhaps, then they start getting error messages. I kick everyone out, and
see records with #ERROR all across. I've recovered the data by copying
everything to Excel in the bad database, and then copying it back. (I've
got to do some funky stuff, since there are autonumber fields to deal with
as well.) We always lose a few records. Last week we had corruption, and
the repair wouldn't work, so I restored from backup. I then tried a repair
from 2002, and it did work (converting the database as well), so I managed
to give them some of the data they had entered. Some was still lost.

So, other than not having enough time to work on this project, and not
knowing exactly how the database access* works, what am I doing wrong? I
don't like any corruption, but it still occasionally happens. Do I need a
split database when the database only has tables? Tell me more about
transaction logs to rebuild corrupted data stores. (I don't usually have
physical access to the computer the data resides on, just a network map to
the drive.)

-k

*access, not Access. Although I certainly don't know exactly how the
database Access works either!

 

[Tim Marshall]

Ummm, is it my newsreader or does all this adsl person do is reply
quoting the text of the post to which s/he is responding?

It looks as if s/he is trying to participate but in all posts from this
person I don't see anything except quoted text?

 

[Alex White MCDBA MCSE]

Hi Karen,

You do not need to split your database, splitting is the process of moving
the data away from everything else in an mdb file, in your situation, you
are using the mdb as a data store only. The fact that your front-end is C++
would normally indicate a pretty good programmer as they have used (In my
opinion) the hardest language to master outside of assembler. I would make
sure you are running the latest MDAC/Jet stuff http://www.microsoft.com/data
make sure your computers are patched and in good working order.

 

[Immanuel Sibero]

Looks like the work of a virus-infected computer.


Immanuel Sibero

 

[John Vinson]

Ok, I usually lurk, but I have some questions about corruption. I've read
that the databases should be split, with data on the network, and the forms
and queries on each individual pc. We use Acess 97, not split, but with no
queries or forms. All of the user input and queries are done through
programs written in Visual C++ 6.0. The database lives on the network, the
programs live on each users' PC. I didn't write the actual database code,
so I assume it uses JET. It doesn't use ODBC. Most of the users have XP,
but a few still have Win98, so when I do get a chance to make changes in the
code, I compile it for both, and have separate installs. I do a compact and
repair on the databases about once a month, when everyone else has gone
home. The databases are small: the largest is about 30-35MB.

Essentailly, you already HAVE a split database: the tables in a .mdb
file on the backend, and the user interface application distributed
onto each frontend. It's only different in that your frontend is
implemented in VC++ rather than in Access. You'll get no benefit from
splitting since basically there is nothing to split!

We get corruption, oh, maybe once a year or so. The network is slow
perhaps, then they start getting error messages. I kick everyone out, and
see records with #ERROR all across. I've recovered the data by copying
everything to Excel in the bad database, and then copying it back. (I've
got to do some funky stuff, since there are autonumber fields to deal with
as well.) We always lose a few records. Last week we had corruption, and
the repair wouldn't work, so I restored from backup. I then tried a repair
from 2002, and it did work (converting the database as well), so I managed
to give them some of the data they had entered. Some was still lost.

Hm. I don't see clearly how going to Excel buys you anything! In that
situation I'd be inclined to a) keep good current backups and simply
trash the corrupt database and simply recover from the backup; b) have
a routine of Compacting the database (you may well be doing so); and
c) if necessary, create a brand-new empty database and use File... Get
External Data... Import to import all the tables. For a damaged table
it may be necessary to create the table empty, Link to the damaged
table, and run one or more Append queries to migrate the data.

So, other than not having enough time to work on this project, and not
knowing exactly how the database access* works, what am I doing wrong? I
don't like any corruption, but it still occasionally happens. Do I need a
split database when the database only has tables? Tell me more about
transaction logs to rebuild corrupted data stores. (I don't usually have
physical access to the computer the data resides on, just a network map to
the drive.)

Transaction logs are available natively in SQL/Server, but not in
Access JET databases. They can be implemented in JET but it would be a
REALLY big painful job - simpler to install MSDE and move the data to
SQL!

It may well be worth your while to check out Tony's excellent FAQ at

http://www.granite.ab.ca/access/corruptmdbs.htm

for suggestions to prevent (or at least inhibit) corruption.

John W. Vinson[MVP]

 

[David W. Fenton]

Mass mailing is something to be done cautiously, when ever I
provide a solution to a client, Un-announced emails are a real
problem in world wild web, and I don't condone it at all, all my
clients are using opt in systems, with opt out systems for people
that don't want the emails anymore. The biggest problem with RBL's
is Reverse NDR spamming and not real spammers. I think RBL's are
going to be a thing of the past soon, with SPF records in DNS
going to become the standard soon.

The client for whom I created a mass mailing system was entirely
opt-in, so that had nothing to do with it. I would not have done the
work if I was not assured of that.

The problems were manyfold:

1. their ISP put in place a requirement that emails had a limit of
25 recipients (we were using BCC back in those old days of spam
innocence!).

2. we first switched to indivual emails, but their ISP eventually
throttled their SMTP connections and wouldn't allow them to send
more than X number of email messages in a particular period (I
forget the exact number and the exact period).

3. we then switched to batches of 25 in the BCC, and that has
worked. However:

4. lots of ISP's spam filters mark their mailings as spam because:

a. 25 addresses in the BCC

b. sent from a dynamic IP.

So, they are now using CoolerMail.com to send the announcement of
their quarterly newsletter. I really do think that class of service
is the only alternative nowadays.

Fortunately, it's very easy for them to export the temp table I was
using to drive the mass emailing from Access.

 

[David W. Fenton]

We get corruption, oh, maybe once a year or so. The network is
slow perhaps, then they start getting error messages. I kick
everyone out, and see records with #ERROR all across. I've
recovered the data by copying everything to Excel in the bad
database, and then copying it back. (I've got to do some funky
stuff, since there are autonumber fields to deal with as well.)
We always lose a few records. Last week we had corruption, and
the repair wouldn't work, so I restored from backup. I then tried
a repair from 2002, and it did work (converting the database as
well), so I managed to give them some of the data they had
entered. Some was still lost.

You must have a network problem somewhere, or your program is doing
something not kosher with managing connections. Or somebody is
shutting off their PC with a pending edit (though whether or not
that can corrupt your data depends entirely on how it's implemented
in the app).

The problem you described sounds like lost memo field pointers. Two
things to do:

1. change the memo fields to text fields, if the data will fit.

2. make sure the app uses no "bound" methods for editing memos.

I'm not sure how a C++ application would implement this, but when
implementing it in Access, the crucial idea is that the user edits
an unbound control that was populated in code and then the changes
are posted via SQL that updates the underlying record. If the
programmer used any advanced add-in controls that offered any kind
of data binding, this might be the source of the problem.

Also, you might try sending a corrupted MDB to Peter Miller at
PKSolutions.com -- he can recover just about anything.

So, other than not having enough time to work on this project, and
not knowing exactly how the database access* works, what am I
doing wrong? I don't like any corruption, but it still
occasionally happens. Do I need a split database when the
database only has tables? Tell me more about transaction logs to
rebuild corrupted data stores. (I don't usually have physical
access to the computer the data resides on, just a network map to
the drive.)

The comment about transaction logs was about SQL Server, not Jet,
which doesn't have (and couldn't possibly ever have) such a thing.

 

[karen]

Ok, I usually lurk, but I have some questions about corruption. I've read
that the databases should be split, with data on the network, and the
forms and queries on each individual pc. We use Acess 97, not split, but
with no queries or forms. All of the user input and queries are done
through programs written in Visual C++ 6.0. The database lives on the
network, the programs live on each users' PC. I didn't write the actual
database code, so I assume it uses JET. It doesn't use ODBC. Most of the
users have XP, but a few still have Win98, so when I do get a chance to
make changes in the code, I compile it for both, and have separate
installs. I do a compact and repair on the databases about once a month,
when everyone else has gone home. The databases are small: the largest is
about 30-35MB.

We get corruption, oh, maybe once a year or so. The network is slow
perhaps, then they start getting error messages. I kick everyone out, and
see records with #ERROR all across. I've recovered the data by copying
everything to Excel in the bad database, and then copying it back. (I've
got to do some funky stuff, since there are autonumber fields to deal with
as well.) We always lose a few records. Last week we had corruption, and
the repair wouldn't work, so I restored from backup. I then tried a
repair from 2002, and it did work (converting the database as well), so I
managed to give them some of the data they had entered. Some was still
lost.

So, other than not having enough time to work on this project, and not
knowing exactly how the database access* works, what am I doing wrong? I
don't like any corruption, but it still occasionally happens. Do I need a
split database when the database only has tables? Tell me more about
transaction logs to rebuild corrupted data stores. (I don't usually have
physical access to the computer the data resides on, just a network map to
the drive.)

-k

*access, not Access. Although I certainly don't know exactly how the
database Access works either!

Thank you all for the advice. I was thinking that maybe my setup meant that
I essentially already had a split database. It's nice to know that is the
case. So, for preventative measures, I should:
-- check the versions of MDAC used, and make sure it is up to date
everywhere.
-- make sure systems are up to date in general (A couple are Win98 still).
-- Remind people to always close this application before shutting down.


We do have a memo field, but a text field isn't big enough. I'm not sure
what a bound method for editing memos is, but I bet I can google that.

When I do get corruption I can:
-- make a new database and use File...Get External Data to import the data,
skipping the Excel step. (That sounds good.)
-- If all else fails, restore from backup.
-- blame the network! I often do that anyway. It may not be fair, but
it's convenient, especially since they often say it is slow right before it
chokes.

And refer to http://www.granite.ab.ca/access/corruptmdbs.htm for clues and
reminders.

-karen

 

[Tony Toews]

Ummm, is it my newsreader or does all this adsl person do is reply
quoting the text of the post to which s/he is responding?

It's not you. I see the same thing.

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

 

[Tony Toews]

3. we then switched to batches of 25 in the BCC, and that has
worked. However:

4. lots of ISP's spam filters mark their mailings as spam because:

a. 25 addresses in the BCC

Trouble is the only time the ISP would know if there are multiple
address in the BCC are if they are to the same host. Your email
server should be splitting out the email to each individual host.

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

 

[Larry Linson]

Hi Larry,

Read your comments, it is interesting the different views we all have,

Yes, it is interesting... for a number of years I worked on a client
company's Access 2.0 client to an Informix database. As I am sure you know,
the only option was Access-Jet-ODBC-serverDB. When I last worked on it, it
was happily supporting between 175 - 200 users. I've worked on Access 97
DBs, similarly configured, but not with quite so many users.

There have been numerous detailed reports here of problems with early
versions of Access 2000, and with early versions of ADP. My experience is
that applying all the Service Packs and Jet updates makes Access 2000
reasonably solid and stable. But in Michael Kaplan's words, "I'd rather
slide down a giant razor blade into a vat of iodine than undertake a
development project with an unupdated Access 2000." Particularly given that
it is now "out of support".

My limited experience with ADP was, indeed, in maintaining and enhancing an
application with Access 2000 SP1. I did not run into serious problems, but
it was an unusual-design application. The original author had neither bound
any forms, nor defined primary keys on the SQL Server 2000 tables that it
accessed. I saw no advantage to ADP over using MDB and ODBC, either in
stability nor performance.

Larry Linson
Microsoft Access MVP

 

[Guest]

I am another small business owner who is looking for a database to deploy
over 5 computers, and these access delevoper extensions sound like the thing
I need, however i cant find more info on them on microsoft's site, could you
point me in the right direction?

Thanks,
Chris OHaire

 

[Ken Snell [MVP]]

For ACCESS 2003, you must obtain the Visual Studio Tools for Developers:
http://www.msdn.microsoft.com/office/understanding/vsto/

 

[Guest]

There are plenty of improvements from Access 2000 to Access 2003. As for
table and database conversions, Access 2003 has built in tools that will help
you convert the existing format Databases over to the newer 2003 format, and
still have your saved, undamaged, unchanged 2000 format as well. Access 2003
can even open and work with databases built under 2000 as long as you do not
modify forms or reports. Any changes to the database other than raw data
changes requires saving to the newer format of 2003.

I've been using Access 2003 to store normalized data over to mainframe
computers with IBM DB/2 database controls, and all appears to be working
fine, mail merge, reports, forms, queries, etc. My only issue is lacking the
knowledge or skill set to allow our user to create WordPerfect Form Letters,
and mail merge to Word Perfect and not Microsoft Word. Office 2003 appears
to be more stable than 2000 was, there are still a few bugs, but there's
always support available for issues. I would be inclined to say that your
system crashing is due to either operating system failures, hardware
failures, or down-right user issues and not Access 2000. Access 2000 was
also a database system that was not very friendly or kind to having multiple
people accessing the same tables concurrently in use by others as well. Its
also best to have your code (forms, queries, reports, modules, etc) in a
database under one name and have the actual data in a backend database
associated by linking the tables to the first table. Acccess 2003 handles
this very well with data on the mainframe DB2 and and *.mde database module
to control forms, reports, and other functions for the user.
As for the OEM upgrade version, make sure that you apply all the current
upgrades available for all of the Office products after you make your initial
install. I believe there are 4 releases available for the Office 2003
products. Kinda wordy, but this and .55 cents can usually get you a cup of
coffee to drink and something to think about.

Robert

 

[Guest]

Would the open license be required for the 5 users or so, or could a
application be built with one license, then using the tool to create an *.MDE
module, then take that *.mde module and a copy of the Access 2003 Runtime
module and distribute it that way. It would be best to have the raw data on
a Backend table while the forms, reports and queries were on a frontend.mde.
Whats the legal license issues say about something along this line?

 

[Brendan Reynolds]

The point was that with just five users, the Open Licence option would not
cost very much more than the Developer Extensions, and would be less hassle.
But the choice is yours.

 

[Brendan Reynolds]

message

Access 2003
can even open and work with databases built under 2000 as long as you do
not
modify forms or reports. Any changes to the database other than raw data
changes requires saving to the newer format of 2003.

<snip>

In the interests of accuracy, I have to point out that this is incorrect.
You can make and save design changes to Access 2000 format MDBs in Access
2003 without converting to Access 2002/2003 format.

 

[aaron.kempf]

dude

these people are full of crap

the only reason to change from 97 to 2003 is Access Data Projects. if
you're a small business user; you probably dont need it

i would reccomend sticking with 97 and moving your apps to linux.. ms
has proven time and time again that they dont give a flying shit about
us anymore