Passthrough Query Changing HIddenAttribute (1 Viewer)

[tt1611]

Hello All.
Does anyone know why tables I have hidden using vba code

Application.SetHiddenAttribute acTable, tdf.Name, False

keep appearing after a SQL Server passthrough query is run? The tables are all hidden on load of a main menu based on access rights I have set locally but as soon as said user runs any passthrough query, all the linked tables appear.

Thanks in advance.

 

[Minty]

What does the pass through do?
As far as I'm aware there is nothing in running a pass through that should change table visibility.
I know that I have hidden queries in a few databases, and they don't appear when pass throughs are run.

I also use deep hidden tables, if I really want to hide something that needs linking permanently.

 

[tt1611]

The passthroughs are just straight select statements linked to some of the same tables I'm hiding. As soon as the user double clicks on any of them, the hidden table group reappears populated with only the linked SQL tables.

 

[isladogs]

Please check the Flags value for this table in MSysObjects before and after running your passthrough queries

 

[tt1611]

Im not seeing anything special here causing this

Just running this query itself caused all the hidden tables reappear

SELECT
    MSysObjects.Name,
    MSysQueries.Attribute,
    MSysQueries.Flag,
    MSysQueries.Expression,
    MSysQueries.Name1,
    MSysQueries.Name2
FROM MSysObjects
    INNER JOIN MSysQueries
        ON MSysObjects.Id = MSysQueries.ObjectId
WHERE (((MSysObjects.Flags)<>3))
ORDER BY
    MSysObjects.Name,
    MSysQueries.Attribute,
    MSysQueries.Flag;

I may have to create a timer on my log in form to rehide the tables every so often but im just trying to understand why this keeps happening.

Before query - at app login

Post Running query

 

[tt1611]

Just a follow up. I noticed that when i placed a timer on my main form to rehide all the tables, they no longer appear even if I run any queries. This is the weirdest thing but no worries. Ill keep it pushing

 

[Minty]

I use PT queries a lot, so I thought I would have a proper look.
I hid two tables - they disappeared from the normal view, but if you ticked show hidden objects you can see them greyed out.

Then I ran a load of pass through queries. Some return records some just run Stored procedures, nothing changed. The flags haven't changed on those tables, nothing altered at all.

All my normal and the freshly hidden tables have a flag value of 537919488 if that helps.

Have you tried importing everything into a new blank database, in case it's some weird corruption?

 

[tt1611]

Thanks for getting back @Minty (the continued value of this forum and site in general proves itself). I've been encountering the weirdest things happen in this app build most of them likely occurring with my new upgrade to Office 365. I deployed the timer changes to my production environment. Tables that hide on load no longer show post hide from the login screen. This is after this recent update of adding the timer (head exploding). I'm going to watch this one and yes maybe there is some underlying corruption I'm not seeing here. Either way, thanks so much for getting back.

 

[Minty]

This might be a weird suggestion, but try deleting all the links, compact and repair, then relink and hide?

 

[isladogs]

@tt1611
Unfortunately your long answer in #5 didn't answer my question from the previous post.

Your first screenshot showed part of the MSysQueries system table.
I asked for the Flags value for the relevant tables in MSysObjects before and after running the passthrough queries.
That is not the same thing

Running the query in your SQL should not cause any hidden tables to become visible.
You showed that the Tables group became visible (which also shouldn't happen) but didn't show whether any of the tables were also visible.
I suspect they were but that should not happen and does not happen for me

So again, can I ask that you answer my question from post #4 and if possible upload a cut down version of your database.
Only include enough objects to shows the issues and remove/change any sensitive data

 

[Pat Hartman]

Also, Timers are extremely dangerous so you need to have the ability to turn off the timers in your forms when you want to modify objects. If the timer triggers as you are changing code, it can be lost.