Creating Multi-User App

[daneb]

Hi.

We have developed an app, and is now working. However, it must be revised to support networking and multi-user access. As of now, all operations are done by accessing an Access 97 database installed on separate PCs. Of course, this should be revised to having only a single database copy on a server and all database operations be done on this copy. Also, our app has a mixture of DAO/ADO operations.

As beginners in multi-user apps, how will we start modifying our program? Someone said that we have to use ODBC. Can someone guide us or at least point us to the right direction/links toward multi-user access? Thanks in advance!

[Andrew_Fryer]

Hello,

I am not too sure how to do using simply Access, but one way to do it is using an ORACLE database with Access front-end. The benefit of using this is that you can distribute the Oracle database and the Access front-end and as the database is using an ODBC connection then you can specify the IP of the host computer where that database is stored and when the user opens the application they will be prompted for the user name and password for that database instance. It wouldn't be too hard to change your tables for an ORACLE database and there would be no code changes needed at all.

If you decide to use Access entirely then I would look in the Tools->Security menu as this mentions group permissions.

If you need any more specific help then just give me a shout.

Hope this helps

Andrew

[phunkydude]

If you intend staying with Access as the DBMS then I suggest you convert all your code to DAO.

For the code changes I suggest you make sure that all recordsets have appropriate locking enabled (set when you open the recordset object). This is required for DAO and ADO.

For DAO:
Enable the code to allow users to dynamically specify the path to the DB in the event that the drive mappings are different on various machines.
Using DAO you can implement a system.mdw for security (place this on the 'server' and point the other machines to this file also). Then you can go ahead and (in DAO) create the workspaces depending on successful logons (which you will have to implement.

If, however, the possibility exists that you may later upgrade the DBMS to SQL Server or Oracle or something bigger, better and faster than Access, I suggest you go the ADO route.

For ADO:
The easiest would be to create a DSN in ODBC on each machine where the app will be installed. Then use that DSN as the Data Source in your ADO connections.

HTH

[daneb]

Thanks for the reply. Could you elaborate more on what you said regarding the creation of system.mdw, and in letting other PCs point to it?

[Iouri]

If you want to check if somebody is using db right now you can read the .ldb file and check the user who is using db.

Iouri Boutchkine
iouri@hotsheet.com

[phunkydude]

I will not go into any detail, just point you in a direction so that you could get hold of the documentation required.

Check out the properties of the DBEngine object.
Check out and fiddle with the Work Group Administartor tool.

HTH & sorry for not being too clear (time, time, time...)