We have an application running on an Access 2000 database front-end with an Access 2000 backend on an NT server. It currently copes with 20 concurrent users logged on. However, we want to increase the number of users to 30 and modify the application to use and update some data on our lightly loaded remote AS/400 (720 2062) with OS/400 V4R3. It's been suggested that we retain the Access2000 front end, rebuild the backend on the AS/400 and connect the two via ODBC. Do you think ODBC will cope OK with 30 concurrent users? If so, are there any particular versions of ODBC that you would recommend? If not, what alternatives could you suggest?
I think that ODBC will work ok for you on the described env. The Client Access Express latest ODBC driver is fast and quite stable.
Please remember the following:
* On many occasions ODBC will be slow because of bad SQL requests. Be sure to optimize your SQL requests.
* You will need unique keys over as400 files in order to update it from access.
* Journaling is highly recommended.
* AS/400 files will better be single members files (This is how SQL in general likes it).
* When using as400 database in c/s env I would also consider using advanced db techniques such as referential integrity and check constraints. It is best to "teach" your database to defend from logical failures.
This was first published in June 2001