Results 1 to 13 of 13
  1. #1
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296

    How to clear data from temp table on read only account?

    I know this is a dumb question but my work database has a security file that limits most users to read only.
    I made a continuous form recently that puts records into a temporary table. Upon opening the form it runs some SQL to erase the previous records so that it starts blank but this doesn't work in the read only account.
    I was also going to try generating a temporary table using the CREATE TABLE method but it seems to have a similar issue.



    Does anyone know a work around to have this table empty every time while keeping the user in read only?

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is it a local temp table or a SQL server temp table?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Minty View Post
    Is it a local temp table or a SQL server temp table?
    It is local in the frontend.

  4. #4
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    The story seems a bit strange to me. Despite the fact that you only have read only rights, you can still create forms and put data in the table.
    You are talking about a temporary table. They are precisely intended to temporarily store data, and therefore also to delete it again. It's strange if you couldn't.
    Are you sure you're not doing something wrong when deleting? What exactly are you doing? What (error) message do you get?
    Groeten,

    Peter

  5. #5
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by xps35 View Post
    The story seems a bit strange to me. Despite the fact that you only have read only rights, you can still create forms and put data in the table.
    You are talking about a temporary table. They are precisely intended to temporarily store data, and therefore also to delete it again. It's strange if you couldn't.
    Are you sure you're not doing something wrong when deleting? What exactly are you doing? What (error) message do you get?
    There are 2 different logins. I use the admin login to modify the forms etc but the users have a read only login.
    Code:
        'Clears the temp table
        Dim strSQL As String
        strSQL = "DELETE * FROM TempPullsTable"
        CurrentDb.Execute strSQL
    
    
        'If you don't requery, a table called #Deleted will appear every time.
        Me.Requery
    Upon running this code I get
    Run-time error '3109':
    Record(s) cannot be deleted; no delete permission on 'TempPullsTable'

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    How are you applying the "Read Only" status?

    I agree with Peter in that I'm confused, you are in control of what they can and can't do in a local table.
    It's virtually impossible to open a Access database unless you can write to it at a very basic level, unless I'm missing something??
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Minty View Post
    How are you applying the "Read Only" status?

    I agree with Peter in that I'm confused, you are in control of what they can and can't do in a local table.
    It's virtually impossible to open a Access database unless you can write to it at a very basic level, unless I'm missing something??
    Using a Secured.mdw file. I am unfamiliar with it.
    There is a shortcut that points to the frontend and uses the Secured.mdw.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    my work database has a security file
    First thing I thought of was mdw file - then I looked at your profile and dismissed the idea. If the version of the db with your issue is not the same, you should say so. AFAIK, mdw files haven't been used since about 2003. Time to upgrade? No idea how you could achieve this using old security files with Access.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    If you are still able to use a mdw file, then time to start googling on how to amend the security levels for a user.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by Vita View Post
    It is local in the frontend.
    Personally I put any temp tables into a temp Database. I'm not familiar with the security issues of A2002.
    Is it possible to have a side database without security in A2002?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  11. #11
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Micron View Post
    Code:
    my work database has a security file
    First thing I thought of was mdw file - then I looked at your profile and dismissed the idea. If the version of the DB with your issue is not the same, you should say so. AFAIK, mdw files haven't been used since about 2003. Time to upgrade? No idea how you could achieve this using old security files with Access.
    I believe I can give them access to delete records in that table only under "Tools > Security > user groups and permissions".
    I'm sorry if I mislead you. Yea my company still uses Access 2000 and does not want to upgrade.

    Hypothetically if I were able to convince them to (unlikely but) I assume the mdw would not work anymore then? Would you be so kind as to point me to a different security method that I can essential setup a few different user logins?
    EX: a read only account that can do simple searching but no editing of records, an account that can edit, input, remove, and add records, and an admin account. (This is roughly what we have.)
    As my manager put it, It may not be secure but it does a good enough job of stopping the basic users from accidentally deleting records and/or messing up the entire db.

    Quote Originally Posted by moke123 View Post
    Personally I put any temp tables into a temp Database. I'm not familiar with the security issues of A2002.
    Is it possible to have a side database without security in A2002?
    I honestly wouldn't know. It would be a pain to do anyways since (don't ask why because I can't remember) everyone has their own frontend that all access the same backend. So I would need to make it and attach the add-on DB to each frontend.

  12. #12
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    The user groups and permissions seemed to work. I just granted the lowest level account(the read-only) the privilege to write, delete, insert, and update to the temporary table.
    Now it functions as intended and doesn't let them accidentally edit other tables.

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    So I would need to make it and attach the add-on DB to each frontend.
    No, you would add a procedure to a front end and distribute the new front end to everyone. The creation and linking is done by the procedure.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-14-2022, 10:52 AM
  2. Read Design & Read Data Issue
    By f15e in forum Access
    Replies: 7
    Last Post: 10-18-2016, 12:44 AM
  3. Replies: 0
    Last Post: 09-10-2016, 11:55 AM
  4. Creating a Form Button to Clear Table Data
    By wojosh6 in forum Access
    Replies: 5
    Last Post: 12-10-2015, 01:45 PM
  5. Replies: 2
    Last Post: 12-20-2011, 10:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums