Linking MS Access 2010 to mySQL Securely

[Mike Anderson]

We have an environment with some 30 users accessing a mySQL backend using an MS Access 2000 front end. We're using the Jet security system (MDW file) for security. Everyone gets into the mySQL database using ODBC connectors. This gives them full rights to the mySQL database. The rights are restricted using the MS Access security.

We're now overdue in updating MS Access and want to move to Office 2010. However, we will lose the Jet security. My question is how do I structure the ODBC connectors when I have around 5 or more levels of security (access to tables, queries, etc). Or is there a way to prompt for the mySQL username/password when people logon to MS Access? Thanks in advance for any suggestions you might have.