asked on

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
            Next
            Debug.Print qdf.Name
             Set qdf.Properties(i).Value = 0
    Next
End Sub

Open in new window

Jeffrey Coachman

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 Brock

Hidden doesn't make a query table non-updatable. It just .. hides it.

/gustav

Jeffrey Coachman

Gustav.
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...?

bfuchs

ASKER

@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.

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

Thanks,
Ben

Jeffrey Coachman

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...
;-)

Jeff

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

Thanks Pat, I think the Server authentication suggestion would work.

bfuchs

ASKER

@Pat,
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..

https://www.experts-exchange.com/questions/28639339/Can-I-create-a-user-DSN-to-SQL-Server-programatically.html

Thanks,
Ben

PatHartman

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.

bfuchs

ASKER

@Pat,

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.
Thanks,
Ben