How to hide all querys

[JPaulo]

Hi all;

I hide tables so, and to hide all querys?

Dim Tb As TableDef
For Each Tb In CurrentDb.TableDefs
If Not Tb.Attributes And dbHiddenObject Then
Tb.Attributes = Tb.Attributes Or dbHiddenObject
End If
Next
MsgBox "All Tables have been occult. ", vbExclamation, "Aviso "
Else
For Each Tb In CurrentDb.TableDefs
If Tb.Attributes And dbHiddenObject Then
Tb.Attributes = Tb.Attributes Xor dbHiddenObject
End If
Next
MsgBox "All tables are visible. ", vbExclamation, "Aviso "
Exit Sub
End If

 

[jdraw]

Hi all;

I hide tables so, and to hide all querys?

Dim Tb As TableDef
For Each Tb In CurrentDb.TableDefs
If Not Tb.Attributes And dbHiddenObject Then
Tb.Attributes = Tb.Attributes Or dbHiddenObject
End If
Next
MsgBox "All Tables have been occult. ", vbExclamation, "Aviso "
Else
For Each Tb In CurrentDb.TableDefs
If Tb.Attributes And dbHiddenObject Then
Tb.Attributes = Tb.Attributes Xor dbHiddenObject
End If
Next
MsgBox "All tables are visible. ", vbExclamation, "Aviso "
Exit Sub
End If

Prefix all queries with USys... and set options to "not show system objects".

 

[HiTechCoach]

Have you tried using the QueryDefs collection?

You can also store the SQL for the query within the record source property of forms and reports.

You could also store the SQL for your queries in a hidden table.

 

[JPaulo]

Thanks for the tip, solved.

Function HideQuerys()
Dim qry As QueryDef
    Dim x As Integer
 
    For Each qry In CurrentDb.QueryDefs
 
        If Left(qry.Name, 1) <> "~" Then
            If Not Left(qry.Name, 4) = "Usys" Then
                qry.Name = "Usys" & qry.Name
            End If
        End If
    Next
End Function
 
Function UnHideQuerys()
Dim qry As QueryDef
    Dim x As Integer
 
    For Each qry In CurrentDb.QueryDefs
 
        If Left(qry.Name, 1) <> "~" Then
            If Left(qry.Name, 4) = "Usys" Then
                qry.Name = Mid(qry.Name, 5)
            End If
        End If
    Next
End Function

 

[JReagan]

I liked your "hide queries" code very much. However, I'm running into the problem now, I have a form with buttons that I use to run queries to generate reports. It seems now that the code for these buttons can't locate the query. Any ideas on how to correct this?

JB

 

[HiTechCoach]

I liked your "hide queries" code very much. However, I'm running into the problem now, I have a form with buttons that I use to run queries to generate reports. It seems now that the code for these buttons can't locate the query. Any ideas on how to correct this?

JB

JB,

The code is hiding the queries by renaming them to have the "Usys" prefix. This tells Access that the are User SYStem objects. This will hide them unless you have view system objects turned on.


Since all the queries get renamed with this code, you will have to modify all the objects (form, reports, etc) in your database to use the new name.

 

[JReagan]

Well I don't have to change anything in the forms since the "Auto Name Tracker" is turned on. I would just have to change the names in the VBA code so that these could run.
Is there a way to hide object through code where other users couldn't link to it from a blank database? I'm working with Access 2007 and if you open up a blank database, then all you have to do is check the "Hidden Objects" or "System Objects" to view these in objects and import them.
Is there a way to hide the objects from other Access Databases? Is there a way to block the "importing" or "linking" to properties without using a password?

JB

 

[boblarson]

Well I don't have to change anything in the forms since the "Auto Name Tracker" is turned on.

TURN IT OFF! TURN IT OFF!!!!! (did I say Turn it off?)

That is one feature of Access which is likely to corrupt your database. It is known as AutoCorrupt by most of us long-time Access programmers. It is worthless and only causes problems. If you have to rename you can use something like Rick Fisher's Find and Replace (costs money though) or the FREE V-Tools which has a tool called Total Deep Search which can do a replace as well).

 

[JReagan]

I usually keep it off. For the sake of these examples it was on, so sorry to get your worked up over that j/k.
Do you have an idea or answer to my question on how to hide queries or objects from being viewed by linking or importing to my databases?

JB

 

[boblarson]

I usually keep it off. For the sake of these examples it was on, so sorry to get your worked up over that j/k.
Do you have an idea or answer to my question on how to hide queries or objects from being viewed by linking or importing to my databases?

JB

Not really that much you can do with an Access database. If you don't want links to your tables you could use SQL Server or SQL Express instead. Then you have control over that. But with Access, it is almost impossible to keep someone from linking to a table if they are determined to get there. If your users are not technically adept then usually the hiding method will work so they don't show up. But if they know to turn on System and/or Hidden tables checkboxes then there's not much you can do.

As for queries, if you didn't want them in at all you might consider using an ADP instead with SQL Server or SQL Server Express as you can then limit both the tables and views/stored procedures from viewing with SQL Server Security.

 

[JPaulo]

Hi;

Can use this statement to queries hidden;

Private Sub Command0_Click()
Dim stDocName As String
stDocName = "Usys" & "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub

 

[JReagan]

I would have to enter this code in for every query? Couldn't this still be viewed if I just checked the view "Hidden" objects from a blank database when trying to link to this one I'm trying to hide the queries in?

JB

 

[HiTechCoach]

JB,

As Bob has already pointed out, securing an Access 2007 database is not easy to do.

You have to put up as many road blocks as possible.

Here is an in depth/lengthy discussion on the topic and vPPC:

Access 2007 security

also see this example that can handle vPPC:

DB start up and BE relinking

 

[JReagan]

I like the sounds of the first part talking about opening up the secured database in another runtime instance. I'm just still hazy on how to go about it. Am I just creating a VB file, and then on click it runs or what? I didn't understand this part from alancossey:

VB6 code to open Access OK for a database with a database password such as the following seems to work OK.

Sub Main()
Dim app As Object ' Access.Application
Set app = CreateObject("Access.Application")
app.Visible = True
app.UserControl = True
app.AutomationSecurity = 1 ' = msoAutomationSecurityLow
app.OpenCurrentDatabase Command, , "TheFrontEndPassword"
Set app = Nothing
End Sub

You then need to set up a shortcut on your desktop with the path to the exe file followed by the path of the database, e.g.

"C:\Documents and Settings\Alan\Desktop\LaunchApp.exe" C:\Access2007\FE.mdb

Where am I setting this up?

JB

 

[HiTechCoach]

Have you read all seven (7) the pages of this thread? There are over 120 posts just in this one thread.


I would urge you to read the thread completely before you attempt to implement any of it.

 

[JReagan]

Ironically I did spend a lot of time combing through these examples yesterday. Just still hazy on it as I'm new to a lot of these topics.

JB

 

[HiTechCoach]

TURN IT OFF! TURN IT OFF!!!!! (did I say Turn it off?)

That is one feature of Access which is likely to corrupt your database. It is known as AutoCorrupt by most of us long-time Access programmers. It is worthless and only causes problems. If you have to rename you can use something like Rick Fisher's Find and Replace (costs money though) or the FREE V-Tools which has a tool called Total Deep Search which can do a replace as well).

According the shrol29, the correct link for V-Tools is actually HERE

 

[boblarson]

I just used my path that I had saved in my Internet Shortcuts. I can't view that site at all because it is blocked (for some reason) at work. I'll replace my link with yours Boyd so I can provide the right one in the future.

 

[HiTechCoach]

I just used my path that I had saved in my Internet Shortcuts. I can't view that site at all because it is blocked (for some reason) at work. I'll replace my link with yours Boyd so I can provide the right one in the future.

Bob,

I was trying to download the source from the URL you gave. The URL to the source was not working so I emailed skrol29. That is how I found out that the URL was not the correct one. I also had the incorrect one bookmarked. I have seen the same URL before on other sites.

I now have it updated on my links here:

Access Developer Tools: Apps, Add-ins, VBA Code