On Table Open

[AccessProgram]

Hi everybody,

I have this code to determine if the table or query object is open and close it if open status:

CODE:

Option Compare Database
Public globalobjstate As Boolean ' booleans are initialized to false

Public Function objstate(Optional objname As Variant, Optional objtype As Variant) As Boolean
objstate = SysCmd(acSysCmdGetObjectState, Application.CurrentObjectType = acTable or acQuery, Application.CurrentObjectName)

End Function

Public Function closemanualopen()

Dim curobjname As String
Dim curobjtype As String

curobjname = Application.CurrentObjectName
curobjtype = Application.CurrentObjectType

If globalobjstate <> objstate Then
DoCmd.Close curobjtype, curobjname
End If

End Function




However, I dont know where to put this event. I would like to know if there is a code that will be triggered on open of a table or query object. Or any equivalent way that will detect if a table or query object is being opened.

 

[boblarson]

tables and queries do not have events. Forms, Reports, and custom classes have events.

 

[Galaxiom]

You have posted a number of questions about opening and closing tables and queries. You urgently need to change your thinking.
Users should only be interacting through Forms.

 

[AccessProgram]

tables and queries do not have events. Forms, Reports, and custom classes have events.

Somehow I know that tables and queries dont have events. Is there another way to detect or determine that a table or query has been opened?

 

[AccessProgram]

You have posted a number of questions about opening and closing tables and queries. You urgently need to change your thinking.
Users should only be interacting through Forms.

I agree and it is true that users should only be interacting with forms.

It is for the purpose of my learning that I would like to ask how things are done in Access like this one. I would like to learn more and in depth about Access.

 

[AccessProgram]

If I may restate my question:


How can I use the above code I posted that it will run when the database is opened until the database is closed. (the database is not running on runtime mode)

 

[Atomic Shrimp]

If I may restate my question:


How can I use the above code I posted that it will run when the database is opened until the database is closed. (the database is not running on runtime mode)

First off, what you have there is a couple of functions - all by themselves, they have no idea what they're meant to do - they need to be called, with parameters specifying what objects they are meant to act on.


If you want to call them repeatedly throughout the whole time your database is open, you might be able to do it from a timer event on a hidden form that launches on startup of the db.

But it really would make more sense if you told us what result you're trying to achieve - in general terms - what problem are you trying to solve here?

 

[AccessProgram]

First off, what you have there is a couple of functions - all by themselves, they have no idea what they're meant to do - they need to be called, with parameters specifying what objects they are meant to act on.


If you want to call them repeatedly throughout the whole time your database is open, you might be able to do it from a timer event on a hidden form that launches on startup of the db.

But it really would make more sense if you told us what result you're trying to achieve - in general terms - what problem are you trying to solve here?

You are correct Atomic Shrimp and I know that functions need to be triggered. Actually I have tried to use autoexec macro to run the function on db startup and using loop in the function to determine any opened table or query. However, when you use loop, it only performs that function and hangs. Im trying to figure out the code that will only be triggered upon or when table/query is open and not using loop.


My Code: (module)

Option Compare Database
Public globalobjstate As Boolean ' booleans are initialized to false

Public Function objstate(Optional objname As Variant, Optional objtype As Variant) As Boolean
objstate = SysCmd(acSysCmdGetObjectState, Application.CurrentObjectType = acTable Or acQuery, Application.CurrentObjectName)

End Function

Public Function closemanualopen()

Dim curobjname As String
Dim curobjtype As String

curobjname = Application.CurrentObjectName
curobjtype = Application.CurrentObjectType

If globalobjstate <> objstate Then
DoCmd.Close curobjtype, curobjname
End If

End Function

Public Function lookforopenedtablethenloop()
Do While globalobjstate <> objstate
closemanualopen
Loop
End Function

 

[wiklendt]

You are correct Atomic Shrimp and I know that functions need to be triggered. Actually I have tried to use autoexec macro to run the function on db startup and using loop in the function to determine any opened table or query. However, when you use loop, it only performs that function and hangs. Im trying to figure out the code that will only be triggered upon or when table/query is open and not using loop.

sounds like you're just trying to prevent anyone opening a query or table. a better way is to hide the navigation pane so they can even see the tables or queries in the first place (edit: and requires no code! yippee!).

you WANT to be able to open tables and queries if YOU are editing something AS A DEVELOPER, so you DON'T want arbitrary code closing things as soon as they're open... so if you hide the navigation pane form users, you can get it back during development by holding down the SHIFT key while you're opening the file.

how to do this depends on you version of access. in 2007:

"use access special keys" is unchecked because you can normally hide/unhide the navigation pane by pressing F11 (i think?)... this check prevents that.

another method would be to right-click on each table and select "hide". (but far better is to hide nav pane)

i think there many be other methods as well, but i'm not sure what they might be. (again, far better to just hide nav pane)

also, something that helps is if you split your database into a FrontEnd and a BackEnd. then, even if the users sees the tables (as linked tables in the FE), they can't actually edit them from the FE... but really, they shouldn't even SEE them, and the above method shows you how to do that (hide navigation pane).

hidden navigation pane on DB open:

....is that what you mean to do?

 

[wiklendt]

oh, forgot to say, hiding nav pane also prevents your users screwing around with modules and macros.

and you might ask: but i want to open forms and reports! well, in the settings screen on my previous post, you can specify a form to open when the database does (the option is "display form", near the top of the window). most people make a 'hub' form (or switchboard) to be displayed so that users can open other forms for specific data entry/viewing. (in my previous post my startup form is a login form, when a user logs in they get taken to a hub form then).

for reports, you need buttons on forms to call the reports (or other code in other events if you like), and very fancy things can be done by calling reports from forms instead of directly - like filtering...

my items form in the database pictured shows a method of printing (well, opening a report, anyway) with various checkboxes to determine WHAT to print (there's only one Items report "rptItems", but it gets filtered depending on the checkbox selections and which button is pressed):

nifty eh?

 

[AccessProgram]

thanks wiklendt. I am also using version 2007 and I have also done your advices.


With the question of Atomic Shrimp asking me ("But it really would make more sense if you told us what result you're trying to achieve - in general terms - what problem are you trying to solve here?").

Probably, one answer would be just to disallow users from opening the tables and queries if they have found way to get inside the db. But my primary reason is to learn more and in depth of how things are done in Access like this one.

 

[wiklendt]

Probably, one answer would be just to disallow users from opening the tables and queries if they have found way to get inside the db.

one thing you have to always remember: a DETERMINED user (or would a better word be "hacker"?) will find a way, no matter what safe-guard to put in place.

the amount of security you impose on your database depends somewhat in what environment your DB will be used and how confidential the data is. there are LOTS of threads on DB security in the forum (use the forum's search page)

you can go from very basic security (like i've described here, which i have deployed in a shared server/network at work) or even go to extremes (for people wanting to use access and deploy their DB as a "program" to the public domain). it just depends on how much time you want to spend on it relative to how determined (or at all) you think your users will be to get inside it.

most users are content in USING the database, not making changes. my users for the database i've used and an example here, are WAY too busy to bother trying to make changes to the DB, let alone figure out how to get in! ;P (edit: but, obviously, if you want to SELL you DB, then security becomes a big issue).

 

[AccessProgram]

What you have said about the hackers is true Wiklendt. Moreover, Selling the db is one of the factors in my db planning. For now I am not going to use other db aside from Access.

At present I am experimenting and learning, researching ways as part of my planning.

 

[wiklendt]

here's something else to remember if you are using access to make your own 'program'...

http://www.access-programmers.co.uk/forums/showthread.php?t=183686&highlight=VBA+project

 

[gemma-the-husky]

i just dont see what you are trying to achieve

close any table as soon as the table is opened directly?

 

[wiklendt]

What you have said about the hackers is true Wiklendt. Moreover, Selling the db is one of the factors in my db planning. For now I am not going to use other db aside from Access.

At present I am experimenting and learning, researching ways as part of my planning.

and remember - not even microsoft has been able to prevent total application security of their OWN products... so there's little chance that you'll be able to create something that's even more water tight.

having said that, the thing you could possibly rely on is that there won't be so big an effort to "crack" you app as there is each time M$ releases a new product.

 

[The_Doc_Man]

The problem with attempting to close a table if it was opened directly is that it is always opened directly - from the outside of Access.

You are ALWAYS outside of Access because you don't have a way to know whether a user opened table X directly or not, if it is also opened from a query through a form. Open is open.

Second problem. Forms are open when they appear by name in the Forms collection. But... they are only seen as open in YOUR workspace if YOU opened them. Otherwise, forms (and for that matter, reports) are in the documents collection.

If you are using a shared DB, each user's workspace is separate and the knowledge of what objects are open in another workspace just isn't there. Unless you pre-engineer a way to make it be available.

I understand what you want to do, and I personally agree that there should be some kind of event for table open, table update, etc. - and of course, query open. But 'tain't happening right now. Larger-scale databases like MySQL and ORACLE and SYBASE and a few others have "triggers" that fire based on table activity. But not Access. It is a small-system model and has small-system tools.

 

[AccessProgram]

thanks for the replies guys. Its really not happening right now. I guess I have to stick to what is available. thanks.