[SOLVED] How to check in Access VBA whether ODBC SQL Server table has write access?

How to check in Access VBA whether ODBC SQL Server table has write access?

I have a ODBC Linked table in Microsoft Access which is connected to sql server.

For some users the login which connect access to SQL Server has access to only one database with db_datareader role so they cannot edit any data in the tables. For other users they have db_datareader + db_datawriter role and they can edit any data.

How can I check in vba that my table is not editable in case of db_datareader logins?

Solution

You can use passthrough queries to get user role membership, and querydefs to create or access them:

Public Function is_datawriter() As Boolean
    Dim qdef As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set qdef = CurrentDb.CreateQueryDef("")
    qdef.Connect = "ODBC; MY_ODBC_CONN_STRING"
    qdef.SQL = "SELECT IS_ROLEMEMBER('db_datawriter')"
    Set rst = qdef.OpenRecordset(dbOpenDynaset)
    If rst.Fields(0).Value = 1 Then is_datawriter = True
End Function

Testing table-specific rights is somewhat more difficult, but in your case this will probably do.