Problem with function SetTableValidation (1 Viewer)

Wappervliegje · Jan 28, 2015

Hi people,

I'm trying to make a modified function SetTableValidation (this original function is founded at internet). 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? 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/ACCDE and Access 2010.

Greeting from Wappervliegje!

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

jdraw · Jan 28, 2015

Perhaps I'm missing your intent. If you have multiple users, you should be using a split database with a FrontEnd and BackEnd. The reasons and rationale are here.

So when you develop an accdb frontend, test it thoroughly and consider that as an "official/master" copy. You can create an accde and distribute that to each and every user for use on their own PC. The accde is intended to be non-modifiable.
You must keep and protect the "official/master" accdb in order to make changes to the frontend. You make the changes to the accdb (which becomes the new/latest "official/master"), then create a new version of the accde.

see this for more info.

Wappervliegje · Jan 29, 2015

Dear jdraw,

Thank you for your good tips. But that problem is still present. It's beautiful to split te ACCDB-database in back-end and front-end database. That's want I want too. Only the problem is that the users (their names are fictitious) of my database can simple change their security level in the tables (tbl_User and tbl_Security_Level) in front-end database (see the attachments). These tables are need for all forms in front-end database such as block the parts of forms. See my other post "Access can't find a subform/subsubform". So, is there maybe a solution to block the tables "tbl_User" and "tbl_Security_Level" against users of my database? I thought that my above codes must work be able?

GinaWhipp · Jan 29, 2015

The Table is in use, you are in the database. You are trying to set validation rules at the table level while it's open so that will not work. Perhaps you can get some ideas here that would work better in your scenario...

http://www.access-diva.com/f10.html

or here...
http://www.access-diva.com/f7.html (additional links at the bottom of the page)

It should also be noted that you need to split your database in our for anything to work. You want to set permissions based on who's in your database. If everyone is using the same Form that database will not be able to figure out what to do. JDraw posted a link... read it then split your database. Oh and just in case you need more information about splitting...

Does everyone have their own front end? Are their front ends installed on their drive and the back-end on a shared drive? Is the database opening in shared mode? Perhaps the below will help…

http://www.kallal.ca/Articles/split/index.htm

http://allenbrowne.com/ser-01.html

http://www.utteraccess.com/wiki/index.php/Sharing

You may also want to take a look at MVP Tom Wickerath's article…

http://www.accessmvp.com:80/Twickerath/articles/multiuser.htm

To handle the update of multiple front ends see…

http://www.autofeupdater.com/
http://regina-whipp.com/blog/?p=184
http://www.btabdevelopment.com/ts/freetools

If each user needs Access you can install the Runtime…

Access 2007
http://www.microsoft.com/downloads/...d9-9dc6-4b38-9fa6-2c745a175aed&displaylang=en

Access 2010
http://www.microsoft.com/downloads/...cd-5250-4df6-bfd1-6ced700a6715&displaylang=en

Access 2013
http://www.microsoft.com/en-us/download/details.aspx?id=39358

Wappervliegje · Jan 29, 2015

Dear GinaWhipp,

Thank you with all info. I'll studying your info. I'll looking to the best solution to block those two important tables (see above) in back-end database so that the users can't modificate their level security.

Greeting from Wappervliegje!

Wappervliegje · Feb 2, 2015

Dear people,

I've read all tips, but no of one give my a solution.

Is there no solution for this code (see below)? I get always this error number 3422 (Cannot modify table structure. Another user has the table open). Is there no solution to close this table?

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
                        [B].ValidationRule = False[/B]
                        [B].ValidationText = "Attention!" _
                                            & vbCrLf & "This table has been blocked against modifications!"[/B]
                    End With
                Next
            End If
End Function

GinaWhipp · Feb 2, 2015

There is no solution to your code because the Table is in use. To close the Table means to close the database. Sorry but you need to come up with another way, hence, my first two links.

Wappervliegje · Feb 3, 2015

Thank you for your effort to help me, GinaWhipp. I'll looking the right way to get my aim. I'll reading now your first two links.

Wappervliegje · Feb 3, 2015

Dear GinaWhipp,

The other person (see below the link) has my warned that my function is trying to entrance ALL tables in my database such as system tables. That's not my aim. I've made a mistake. The tables those I've made in the navigation pane at the left of Access are the aim for my function. Do you maybe know how I get all tables who are present in the navigation pane?

My post in other forum

GinaWhipp · Feb 3, 2015

For that error make sure you have turned off Name AutoCorrect...

Office Button | Access Options | Current Database | Name AutoCorrect

As for the rest of it, I see you are getting help in another Forum. Since it looks like they are working with your code I would suggest you stick with that as that is not the way I would do it.

Wappervliegje · Feb 4, 2015

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.

http://www.utteraccess.com/forum/index.php?showtopic=2025798

Problem with function SetTableValidation (1 Viewer)

Wappervliegje

Registered User.

jdraw

Super Moderator

Wappervliegje

Registered User.

Attachments

GinaWhipp

AWF VIP

Wappervliegje

Registered User.

Wappervliegje

Registered User.

GinaWhipp

AWF VIP

Wappervliegje

Registered User.

Wappervliegje

Registered User.

GinaWhipp

AWF VIP

Wappervliegje

Registered User.

Similar threads

Users who are viewing this thread