Access 2k upgrade to Access 2003 or SQL Server Express?

[RayPower]

I'm having a system using Access 2000 as both front-end (queries,
forms, reports & temp tables for reports) & back-end (data) with
back-end running on the server. The application runs on the network
with around 15 users.

The back-end database is suffering from performance issues. There were
several corruptions since few months & one major corruption & corrupted
the MSysObjects table. Also, the system will run batch process that
generate hundreds to thousands of records in the transaction table,
which caused the transaction table being locked even though users just
trying to add new transactions even though the database has been
selected to use record-level locking. Furthermore, some queries are
very slow as they query the transaction table which has over 1.3
million records.

The company has just been covered by the corporate license (as a
company of the group) to update all PCs to Office 2003. Is upgrading to
Access 2003 help to get the system more stable/reliable? Is Access 2003
not so easy to corrupt? Can we get true record-locking or still
suffering from locking pages or even the whole table when adding
hundreds of records?

As upgrading to SQL Server is not an option at the moment (due to the
budget), I'm also looking to the SQL Server Express which is free. But
how hard is it to upgrade to SQL Server Express? There are lots of
codes using DAO recordset in the application, do I need to change all
to ADO? Is the front-end still in .MDB (database) or change to .ADP
(Access project)?

Thanks,
Ray

[Larry Linson]

"RayPower" <ra********@yah oo.com.au> wrote

I'm having a system using Access 2000 as both front-end (queries,
forms, reports & temp tables for reports) & back-end (data) with
back-end running on the server. The application runs on the network
with around 15 users.

The back-end database is suffering from performance issues. There were
several corruptions since few months & one major corruption & corrupted
the MSysObjects table. Also, the system will run batch process that
generate hundreds to thousands of records in the transaction table,
which caused the transaction table being locked even though users just
trying to add new transactions even though the database has been
selected to use record-level locking. Furthermore, some queries are
very slow as they query the transaction table which has over 1.3
million records.
I'd suggest you do some searching at Tony Toews' webstite,
http://www.granite.ab.ca/accsmstr.htm -- he has the best collection of
information and links on avoiding corruption and improving performance that
I know of, for an Access mutliuser environment. A couple of more sites with
good information are Jeff Conrad's "Access Junkie" site... look for posts by
him in this newsgroup and the microsoft.publi c.access... newsgroups, and
Albert Kallal's site... again look for posts by Albert -- I think he carries
the site address in his SIG line.

Larry Linson
Microsoft Access MVP
The company has just been covered by the corporate license (as a
company of the group) to update all PCs to Office 2003. Is upgrading to
Access 2003 help to get the system more stable/reliable? Is Access 2003
not so easy to corrupt? Can we get true record-locking or still
suffering from locking pages or even the whole table when adding
hundreds of records?
Well, I feel more comfortable with Access 2002 or Access 2003, with all the
Service Packs and updates, than I do with Access 2000, with all its Service
Packs and updates. I've discovered quite a number of people who have
problems but have not got their IT department to apply the SPs and updates.

As upgrading to SQL Server is not an option at the moment (due to the
budget), I'm also looking to the SQL Server Express which is free. But
how hard is it to upgrade to SQL Server Express? There are lots of
codes using DAO recordset in the application, do I need to change all
to ADO? Is the front-end still in .MDB (database) or change to .ADP
(Access project)?

Thanks,
Ray

[david epsom dot com dot au]

Access 2003 uses the same version of the database engine as
Access 2000. There are unlikely to be any performance changes.

You can change to SQL Server Express without making any
changes at all, but explicit dao transactions are likely
to fail, and the system is unlikely to be any faster unless
you re-optimise for the new system. SQL Server will still
automatically upgrade record locks to table locks in some
situations.

Running batch processes across the transaction table that
the users are using is always a bad idea, and always scales
badly. One of the reasons I am not permitted to have direct
access to some of the Servers I use is because the dba
are afraid that some user may naively run a batch process
or query across the whole transaction table, bringing all
interactive use to a stop. Changing your server from a
file server to a sql server may buy you some time, but it
won't fix the fundamental design problem.

(david)

"RayPower" <ra********@yah oo.com.au> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .

I'm having a system using Access 2000 as both front-end (queries,
forms, reports & temp tables for reports) & back-end (data) with
back-end running on the server. The application runs on the network
with around 15 users.

The back-end database is suffering from performance issues. There were
several corruptions since few months & one major corruption & corrupted
the MSysObjects table. Also, the system will run batch process that
generate hundreds to thousands of records in the transaction table,
which caused the transaction table being locked even though users just
trying to add new transactions even though the database has been
selected to use record-level locking. Furthermore, some queries are
very slow as they query the transaction table which has over 1.3
million records.

The company has just been covered by the corporate license (as a
company of the group) to update all PCs to Office 2003. Is upgrading to
Access 2003 help to get the system more stable/reliable? Is Access 2003
not so easy to corrupt? Can we get true record-locking or still
suffering from locking pages or even the whole table when adding
hundreds of records?

As upgrading to SQL Server is not an option at the moment (due to the
budget), I'm also looking to the SQL Server Express which is free. But
how hard is it to upgrade to SQL Server Express? There are lots of
codes using DAO recordset in the application, do I need to change all
to ADO? Is the front-end still in .MDB (database) or change to .ADP
(Access project)?

Thanks,
Ray

[Lyle Fairfield]

The company should hire a competent database manger.

He or she, together with the IT department should identify the cause(s)
of the problems.

After the problems have been identified, solutions should be planned.

As you describe the db, I am prompted to think that it's a piece of
shit. Queries on 1.3 million record tables should happen in the wink of
an eye. The company may think it can't afford to fix it, but it's
likely to be spending more because of general inefficiency, corruption
and breakdown than fixing it would cost.

I've worked in very large corporations during the 90's that had many
databases worked on by hundreds of users. I have never seen a case of
db corruption, other than ones sent to me form other parts of the world
for repair.

The answer to all your questions is Maybe. It depends on who and how..