Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014

    Unanswered: Connecting MS Access to mySQL Securely

    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. We will have to create users on mySQL like we now have in MS Access and implement appropriate security on the tables.

    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.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    In Access, you can retrieve the name of the user that opened the Windows session, either by using:
    Dim strUserName = Environ("USERNAME")
    or by calling a Windows API:
    Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA (ByVal lpBuffer As String, nSize As Long) As Long
    Public Function GetNTUser() As String
    ' Retrieve the ID of the currently connected user
        Dim strUserName As String
        Dim lngUserNameSize As Long
        strUserName = String$(255, 0)
        lngUserNameSize = Len(strUserName)
        GetUserName strUserName, lngUserNameSize
        strUserName = Left$(strUserName, lngUserNameSize - 1)
        GetNTUser = strUserName
    End Function
    You can then create a table of users with their privileges and interrogate this table to determine what each user can do.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts