Permission for 'MsysTable' object

[JDRoss]

I wonder could you help me out on a security issue.

I have code which creates a query at run time depending on which table the user is searching. This query is created dynamically.

The problem is that whatever permissions other than the default Admins Group permissions I set for the user, it will not give them access to creating the query. Naturally, I don't want users to have Admins Group permissions.

What is curious is that Access tells them that the user does not have permissions for 'MsysTable' object. Now I have searched through the hidden and system tables and can't find this one.

Any ideas?

Regards

John

 

[JDRoss]

After exhaustive troubleshooting, I have traced the problem to the DAO.

I had to convert this database back to 97 format to overcome a password problem, don't ask? It seems that in doing so there is confusion between ADO and DAO. I am now explicitly referencing DAO object, e.g. database, recordset, etc.

But I am getting the old stock reliables, 'Item Not in this Collection' or 'Type Mismatch' when I run the following piece of code.

 Dim db As DAO.Database, tdf As TableDef
    Dim fld As Field, rst As DAO.Recordset
    Dim tbl As String

    tbl = Forms!THisForm!cboTable
        ' referneces a table name from a combo 
    Set db = CurrentDb
        
    Set tdf = db.TableDefs(tbl)
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("Delete * from TableFields")
    DoCmd.SetWarnings True
    Set rst = db.OpenRecordset("TableFields", dbOpenDynaset)
    ' pass the field names from the table

For Each fld In tdf.Fields

The above line is the one that gives the type mismatch error

        If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
            rst.AddNew
            rst!fieldName = fld.Name
            rst!FieldType = fld.Type
            rst.Update
        End If
    Next fld

    Set db = Nothing
    cboFieldName1.Requery
    cboFieldName2.Requery
    cboFieldName3.Requery

Has anybody a solution to this? I know this code works because users have been using it for months. I know the problem it is related to the DAO reference library, is there a work around? Or is there another way to get field names from a table without using the Field type or tabledef?

Much appreciated as always.

John