Access 2000 - the right choice?

[Praty77]

Hello -

we have been using access 97 for a multiple user trading system at a
small bank. Typically around 10 users entered a total of hundred
trades everyday. Some of the data was shared with a MS SQL 2000
server, which we accessed (both for read and write) as a linked ODBC
table. Over the period of time, the system has become quite complex,
with 30 tables, 30 forms and a size of 140 M.

We have been facing a number of problems during our regular usage.
Some of them are:

1. Problems accessing linked odbc table. When we ran a query, not all
rows were returned to MS Access, possibly due to network problems

2. Clashes between concurrent users. Since we could not have
transactions in access 97, concurrently modifying data in same tables
produced unresolved locks. The system would stop responding, forcing
us to "end task" it.

3. Concern by the technical auditors in the bank. Access tables,
linked tables and code are unsecure, and anyone could tamper of delete
the data.

Would these problems be addressed by using Access 2000? Some
documentation pointed out the odbc implementation is more efficient.
But is it possible to have transactions? Also, is it possible to lock
direct access to tables (allowing access from code only). Finally how
much better is the compiled code... I could notice no performance
advantage, and I felt helpless because i could not "debug" it.

Thoughts from experts welcome!
Cheers
Praty77

[Larry Linson]

No. Those problems would not be fixed by using Access 2000.

Your back end should be set up to require user login, with password. If you
do, the "linked tables" are no less secure than server tables with any other
front end. If you embed a userid/password in the connect string, they would
be at risk, no matter what front-end you use.

There should be no "sensitive" information in the other objects you
mention -- put all the sensitive data in the server database. If there is,
then you have a significant design problem due to not considering security,
and changing to a later version of Access, or to some other front-end tool
will not solve it.

Should you move to a newer version of Access as part of your security
improvements, I'd suggest Access 2002, the database software component of
Office XP, or Access 2003, the DB software component of Office 2003 System.
Even with all three Service Packs applied to Access 2000, I think you'll
find the later versions are better.

Larry Linson
Microsoft Access MVP
"Praty77" <pr************@yahoo.com> wrote in message
news:6c**************************@posting.google.c om...

Hello -

we have been using access 97 for a multiple user trading system at a
small bank. Typically around 10 users entered a total of hundred
trades everyday. Some of the data was shared with a MS SQL 2000
server, which we accessed (both for read and write) as a linked ODBC
table. Over the period of time, the system has become quite complex,
with 30 tables, 30 forms and a size of 140 M.

We have been facing a number of problems during our regular usage.
Some of them are:

1. Problems accessing linked odbc table. When we ran a query, not all
rows were returned to MS Access, possibly due to network problems

2. Clashes between concurrent users. Since we could not have
transactions in access 97, concurrently modifying data in same tables
produced unresolved locks. The system would stop responding, forcing
us to "end task" it.

3. Concern by the technical auditors in the bank. Access tables,
linked tables and code are unsecure, and anyone could tamper of delete
the data.

Would these problems be addressed by using Access 2000? Some
documentation pointed out the odbc implementation is more efficient.
But is it possible to have transactions? Also, is it possible to lock
direct access to tables (allowing access from code only). Finally how
much better is the compiled code... I could notice no performance
advantage, and I felt helpless because i could not "debug" it.

Thoughts from experts welcome!
Cheers
Praty77

[Larry Linson]

"Chuck Grimsby" wrote

Personally, I disagree with Larry here.
Changing what version of Access you
are using will do nothing for you, in my
experience.

Sorry my post turned out to be misleading -- what I was trying to say was
that _if_ the OP was going to move to a more recent version, that either
Access 2002 or 2003 would, IMNSHO, be preferrable to Access 2000, not that
such a move would solve any of the stated problems.

[Tony Toews]

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote:

Oh, the code portion you can fix by having everyone use MDE's rather
then MDBs. You'll need the developer version to do that however. In
general, once the above is done, it's not required however.

You don't need the developer version to create MDEs.

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

[Mark Johnson]

Tony Toews <tt****@telusplanet.net> wrote:

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote:

Oh, the code portion you can fix by having everyone use MDE's rather
then MDBs. You'll need the developer version to do that however. In
general, once the above is done, it's not required however.

You don't need the developer version to create MDEs.
Tony

Even so, I wonder if that's why so many post with Office 97 questions?
The ODE is probably much cheaper on ebay for that than for later 2K
versions?

[Larry Linson]

"Mark Johnson" wrote

Even so, I wonder if that's why so
many post with Office 97 questions?
The ODE is probably much cheaper
on ebay for that than for later 2K
versions?

Access 97 is so highly regarded in the developer community that copies of
the O97DE on the auction sites often go for prices that, as a colleague and
client of mine said when he bought one, "You don't even want to know what I
had to pay for it." But, on occasion, you might be able to find a really
good buy.