looking to change table properties in access

Hi Experts,

I have code that successfully  loops thru all queries in my App and makes them not updatable.

Now i'm looking to do the same for all tables (linked tables)

In addition I would also want to change the hidden property of the tables to true

how can I accomplish that?

below is some code I have tested with.

Public Sub UpdateAllTables()
    Dim db As Database, qdf As TableDef
    Set db = CurrentDb

    For Each qdf In db.TableDefs
            For i = 1 To 25
            'Debug.Print qdf.Properties("hidden").Name & "--" & qdf.Properties(i).Value
            Debug.Print qdf.Name
             Set qdf.Properties(i).Value = 0
End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
1. ....and what, specifically, does, or does not happen when you run the code?
2. Can you post the code that does this for the queries?

3. This is a very odd and unconventional request.
...can we ask why you need to do this?

4. Change: CurrentDB to: CurrentDB()

(Also why are you naming your table variable "qdf", ...instead of "tdf"?)
Gustav BrockCIOCommented:
Hidden doesn't make a query table non-updatable. It just .. hides it.

Jeffrey CoachmanMIS LiasonCommented:
I think the OP wants to do both...
This is why I asked...
I am not sure why (or how) you can use code to make a query not updatable...?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

bfuchsAuthor Commented:
@Jeff, Gustav

We have an Access file linked to SQL BE where users create their own queries, for reporting purposes only.

This is why I have the following code to make them all as snapshot (read-only)
Public Sub UpdateAllQueries()
    Dim db As Database, qdf As QueryDef, sSql As String
     Set db = CurrentDb()
    On Error GoTo displayError
    For Each qdf In db.QueryDefs
        If qdf.Properties(15) <> 2 Then qdf.Properties(15) = 2
    Next qdf
End Sub

Open in new window

However the tables are still editable and this is what I am trying to avoid.

Basically I want to have two separate Access files, one that has access to all tables in read only mode, and another one that all tables are hidden, the only thing they should be viewing is the existing queries.

the reason of having qdf is that I simply copied the existing code for queries, to demonstrate what I'm trying to accomplish-:)

Jeffrey CoachmanMIS LiasonCommented:
the reason of having qdf is that I simply copied the existing code for queries, to demonstrate what I'm trying to accomplish-:)
...ok, thanks,...just syntax, ...but it confused me for a sec...

I would not link to the tables at all.  I would create views and link to those.  Unless you define a unique identifier when you link the view, it will not be updateable.  If the underlying view is not updateable, the queries that use it will be not updateable also.

However,  a knowledgeable user can circumvent this easily by simply linking to the tables himself.  

The only real way to protect the data is for the server to do it and that means SQL Server authentication.  If you use active directory and allow users to connect using their normal network logon, then if they must be able to update to use your app, they will also be able to update using this reporting app and nothing you do can actually stop them.  All they have to do is open Access and create a new database.  They have authority to link to the tables and you're dead.

To get past the security problem caused by knowledgeable users, I use SQL Server authentication always.  It is more trouble to maintain but is more secure.  I use the users normal userID so he logs in to the Access app (the real one, not the reporting one) using that and whatever password he uses to log into Access.  Then the Access app "calculates" using some algorithm the user's SQL Server password and links the tables during the log on process WITHOUT saving the password in the connection string.  This is all transparent to the user.  You and the DBA need to discuss how the passwords will be derived.  If you deliver the app as an .accde, the user won't see the code and it might not help him anyway.

Then for the reporting app, I link the tables using a single userID and Password that is stored with the link but this account is not allowed to update the data so it doesn't matter if the user sees it.  He can link to what ever tables the account has access to but he can't update them.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks Pat, I think the Server authentication suggestion would work.
bfuchsAuthor Commented:
Now with this solution I will create a new user with read only rights and then create a user DSN with that new users credentials, and then link all the tables with that DSN.

The only thing that will be an issue is the task having to go to each individual pc and create manually the new DSN.

I already posted a question a while back on how to create DSN programatically, so far none of the answers I got worked, perhaps you can solve that issue as well..


As long as everyone is in your network, you can have your desktop support people push the new DSN to each user using Group Policy.  Ask them.  If they look at you like you're crazy, I'll see if I can find out more details but that isn't my area of expertise.

I have created DSN-less connections.  You just have to link the tables in code.  I don't have it handy and I'm heading out the door, but you should be able to find some example.
bfuchsAuthor Commented:

DSN-less connection can work, in fact someone in that other post provided something in that direction, however for some reason it didn't work by me.

If you can post over there something that works I would appreciate.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.