Permissions / Access help

[ngreenwood]

Hello, this is my first post so please be gentle ...
I am trying to filter some data stored in a table and am using a SQL query which writes the filtered data into a table tblLessonTemp

strSQL = "SELECT snapshots.faculty, snapshots.overallGrade " & _
             "INTO tblLessonTemp " & _
             "FROM snapshots " & _
             "WHERE faculty=(Faculty_txt);"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
 
 
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblLessonTemp")

However the database needs to be read only to everyone except a few people. When I simply set the permissions on the server it doesn’t allow the above code to write to tblLessonTemp. What would you recommend as a solution?

[FishVal]

Hi, there.

Could you explain the reason why you want to write filtered records into a separate table?

Kind regards,
Fish

P.S. SQL syntax seems to be incorrect. Is [faculty] numerical field?

[ngreenwood]

I am writing the filtered data into a separate table so I can use one form as a template and then change the information it displays. I use

DoCmd.OpenForm "frm_LObs"
Set frm = Forms!frm_LObs
frm.RecordSource = "tblLessonTemp"

Faculty is a string. It is for a school and the reason the data needs filtering is so the Heads of Faculty can only see the information stored on members of their faculty. It is vital that nobody on the basic user level has the ability to see unfiltered data.

[FishVal]

So why not to set Form.RecordSource to select query.

SELECT snapshots.faculty, snapshots.overallGrade FROM snapshots WHERE faculty='...faculty name....';
 

Note, string constant to be filtered by is enclosed in single quotes as SQL syntax requires.