Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2015
    Posts
    13

    Unanswered: Error Number 3422 (cannot Modify Table Structure. Another User Has The Table Open.)

    Hi people,

    I'm trying to make a function SetTableValidation (see below). But I get always an error number 3422 (Cannot modify table structure. Another user has the table open.). Only there are no users in my database, only my. That's very strang. That's why I've made an another function CloseAllObjects to ensure that all objects are closed in my database. But no results. Please, can you maybe find a wrong in my function SetTableValidation? The aim of this function is if there another person than me is logged in my database, then this person can't modified all tables. I'm trying to make a "special key" for me only. I'm working with ACCDB and Access 2010. If I split my original ACCDB-database into back-end and front-end (ACCDE), this problem is still present. Because the other users can modificate the data in all tables to their pleasure. That's why I'm trying to make this function SetTableValidation. In this function you can see where it can find the information about the user. The information about the users are present in two tables "tbl_Users" and "tbl_Security_Level" (see attachments).

    Greeting from Wappervliegje!

    P.S. I hope that my English is good enough to understand?

    Code:
    Public Function SetTableValidation()
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim DLookUpSID As String
            Set db = CurrentDb
            DLookUpSID = DLookup("Security_ID", "tbl_Security_Level", "[Security_ID]=" & _
            Nz(DLookup("User_Security_ID", "tbl_User", "[User_Name]='" & Environ("USERNAME") & "'"), "5") & "")
                If DLookUpSID <> 1 Then  1 = Developer/Administrator has only an access to all tables
                    For Each tdf In db.TableDefs
                        With tdf
                            .ValidationRule = False
                            .ValidationText = "Attention!" _
                                                & vbCrLf & "This table has been blocked against modifications!"
                        End With
                    Next
                End If
    End Function
    Code:
    Public Function CloseAllObjects()
        Dim aob As AccessObject
            With CurrentData
               'All Tables
               For Each aob In .AllTables
                   If aob.IsLoaded Then
                       DoCmd.Close acTable, aob.Name, acSaveYes
                   End If
               Next aob
               'All queries
               For Each aob In .AllQueries
                   If aob.IsLoaded Then
                       DoCmd.Close acQuery, aob.Name, acSaveYes
                   End If
               Next aob
            End With
            With CurrentProject
               'All forms
               For Each aob In .AllForms
                   If aob.IsLoaded Then
                       DoCmd.Close acForm, aob.Name, acSaveYes
                   End If
               Next aob
               'All reports
               For Each aob In .AllReports
                   If aob.IsLoaded Then
                       DoCmd.Close acReport, aob.Name, acSaveYes
                   End If
               Next aob
               'All pages
               For Each aob In .AllDataAccessPages
                   If aob.IsLoaded Then
                       DoCmd.Close acDataAccessPage, aob.Name, acSaveYes
                   End If
               Next aob
               'All macros
               For Each aob In .AllMacros
                   If aob.IsLoaded Then
                       DoCmd.Close acMacro, aob.Name, acSaveYes
                   End If
               Next aob
               'All modules
               For Each aob In .AllModules
                   If aob.IsLoaded Then
                       DoCmd.Close acModule, aob.Name, acSaveYes
                   End If
               Next aob
            End With
    End Function
    Attachment 16143Attachment 16144
    Last edited by Missinglinq; 02-02-15 at 22:19.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The first problem I see is with this line

    If DLookUpSID <> 1 Then 1 = Developer/Administrator has only an access to all tables

    I assume you meant the part of the above in red to be a comment, but you've omitted the Single Quote that would tell Access this. So start by changing that line, in Function SetTableValidation(), to

    If DLookUpSID <> 1 Then ' 1 = Developer/Administrator has only an access to all tables

    Once you've fixed this we'll see what else, if anything, is wrong.

    Linq ;0)>
    Last edited by Missinglinq; 02-02-15 at 22:38.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Feb 2015
    Posts
    13
    Hi Missinglinq,

    Thank you for your reaction! Ow, I was forgotten to set ' behind the sentence "1 = Developer/Administrator has only an access to all tables" when I had placed the copy of my function to here. Sorry for my mistake. But in my database is ' still present behind these sentence. These sentence is shown in green and is very useful for me.

    However this wrong is still present. I'll show the printscreen (see attachment) what I want to reach my aim with my function. I hope that it's useful for you? I'm a Dutchman and I've no Access in English. I'll translated the marked texts:

    Validatieregel = Validation Rule
    Validatietekst = Validation Text
    Eigenschappenvenster = Property Sheet (?)
    Hulpmiddelen voor tabellen = Appliances for tables (?)

    Do you maybe know/see what I've made a wrong in my function?

    Click image for larger version. 

Name:	Afbeelding1.png 
Views:	4 
Size:	686.2 KB 
ID:	16148

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Is it possible that your code already running in this instance if Access is opening the table and not closing it, or is opening it for exclusive use.
    I've also seen this sort of error when developing. But running the same code in 'live mode'. Sometimes that because when you are developing Access sets various 'switches' which are not set when running in say a runtime or made/accede application
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2015
    Posts
    13
    Dear healdem,

    I hope that you've no problem that I've place my same post at the other forum. I thought that it's always a good way to help each other. The person dmhzx from the other forum has my warned that my function is maybe trying to entrance ALL tables in my database such as system tables. See below the link. I'd followed up his instructions with debug.print. And the error message is just for one system (?) table "MSysAccessStorage" and that's hopefully all. How can my function ignore these table?

    My same post in other forum

  6. #6
    Join Date
    Feb 2015
    Posts
    13
    Dear people,

    I will know you that I have finally found the solution for the problem with my function. See the below link to my post at the other forum. In this way I want share my solution with you. Also I want thank you all to read/answer my post.

    My post on the other forum

  7. #7
    Join Date
    Feb 2015
    Posts
    13
    Quote Originally Posted by healdem View Post
    Is it possible that your code already running in this instance if Access is opening the table and not closing it, or is opening it for exclusive use.
    I've also seen this sort of error when developing. But running the same code in 'live mode'. Sometimes that because when you are developing Access sets various 'switches' which are not set when running in say a runtime or made/accede application
    Dear people,

    I will know you that I have finally found the solution for the problem with my function. See the below link to my post at the other forum. In this way I want share my solution with you. Also I want thank you all to read/answer my post.

    My post on the other forum

  8. #8
    Join Date
    Feb 2015
    Posts
    13
    Pfff... My excuses for whole last same posts. I'd send my post already, but I get no affirmation that my post has already placed. After "Submit Reply" the website is displaying a blank page.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •