DAO Recordset does not have access rights

[Terry]

In Access 2002 the line
Set Sn = CurrentDb.OpenRecordset(sqlText, dbOpenSnapshot)
only works when the admin user has the required access rights. If the Admin
user has no rights to the database this line errors. If the Admin user has
Open rights the error message says no rights to the table involved.

1. Am I correct in thinking OpenRecordset uses a separate 'connection' (I
say 'connection' as the table concerned is not a linked table) to the
database and that it does this as Admin user?

2. How do I open a recordset with the same priveliges as the current user?
(The database does not have linked tables so no Connect sting is relevant.

Or am I misunderstanding something?

 

[Lance McGonigal]

Well, this is why I'm not a fan of Access security. Have you thought about
removing security and running this app. My rule of thumb is Access
front-end/back-end if no security is needed and less than 25 users.
Otherwise, Access Front-end/SQL Server back-end.

Sorry

 

[Terry]

Unfortunately this is not an option as it is the user's requirement.