Hi,

We've just converted part of a client's database from Access to MySQL, but are wondering on the best route to take regarding different user's rights to different MySQL tables. How do we handle users logging on to MySQL seemlessly, ie. using VB to authenticate them, create the ODBC links with Access, and then destroy these links when they exit the db. ??

The Access db. is split in a frontend and backend .mdb file (the frontend.mdb is on each user's harddrive, linked to the backend on a Linux fileserver). In our scenario, we have about 10 users on 7 PC's, and Pete and Paul may both use the same PC. Pete has rights on MySQL table A (and some Access tables), and user Paul has rights on MySQL table B (and some other Access tables). So we created the two users in Access and MySQL, with their applicaple rights on both systems. They log on to Access and use the Access tables via forms with no problems. The MySQL tables that are permanently linked to the frontend via ODBC (password stored in Access), also provide no hassles. But when we don't store the username/password, the user gets the DSN dialog popping-up, and we rather don't want them guessing around there!

My {humble :)} opinion is... after the user has logged on to Access, check which user it was, and create/edit the applicable (permanent) ODBC links to the protected MySQL tables, using his username and password which is hardcoded in VBA. Protect the VBA with a password. This code is situated on the startup main menu form in Access, which always remains open. When the form is closed, the links are destroyed or just username & password removed. Can anyone help to provide the code for this editing / linking... I've looked in Access Help, but couldn't find anything?? NOTE, the links must exist in Access, not only in VBA, since forms are based on these tables etc. One problem I can think of here is, if Access crashes, then the links are still there. One could however, on Startup, destroy any existing links which shouldn't exist for particular users. Anyway, my gut-feeling says there must be a better way... oh please?

Like with MsSQL Server, could one perhaps integrate the logging-on of Access and SQLServer, ie. you log on once which gives you access to both?