Master Control Help Asap

[N1ck9141]

I used the big name to attract attention lol...

my problem: i look after a large data base on a shared drive we are talking massive that hundreds of people with different user names and user rites use...

no when it comes to editing the data base this is very hard because it means everyone must be logged OFF to change something as simple as a form background.

What i want: i would like to no if there is neway of knowning who is currently logged IN to the databse, and once knowing this being able to boot them from it so i can work on it...

now i put this in the forms section of this site as i would like to be able to use and create such controls in a form of my own design is this possible ?????

 

[DCrake]

From the sounds of it you do not have the database split between front end and backend. This is the route of the problem. There are loads of issues relating to this topic on the forum.

Basically, everything with the exception of the tables is held in one database and the forms,reports,queries,etc are held in another, known as the front end.

Using the link tables manager you can link the table sin your back end to your front end. Each user then has a copy of the front end on their workstation, which is pointing to the shared backend. This gives you the freedom to develop the front end without disturbing the backend or making sure everyone is logged off. When you have made and tested your changes you simply deploy the latest version of the fron end to your users.

CodeMaster:

 

[N1ck9141]

i no this is the standard way of doing things, however because of the security of where im working and the shared drive i am using etc.

i am not able to do this, hence the question any other advise u can give ???

 

[DCrake]

If you do not split your database you are heading for disaster, in my opinion. The principle of having the data on a server is that the data is secure. If you protect your front end sufficiently there should not be a problem with accessability.

 

[N1ck9141]

i understand this but i am unable to take this route so advise something else that will help to solve my problem...

or dnt advise at all

 

[DCrake]

I sympathise with your situation, however, the advice given on this form is free without any obligation, and as a rule is contructive not destructive. Obliging members not to comment if they cannot come up with an all singing all dancing solution will not get you anywhere. Please be more considerate and patient.

David Crake
Super Moderator

 

[N1ck9141]

thanks for the advise its jst very frustrating, and no need to sine yr name to hint yr a super moderator i no lol

 

[Pat Hartman]

So far nothing you have said has given us a clue why spliting the database is impossible. Leaving it as a monolithic entity is downright dangerous. You say you have hundreds of people authorized to access the database. They certainly are not all using it at once or the db would colapse since rarely can a database support more than 25 concurrent users.

You also need to understand the difference between a production environment and a testing environment. You have a fiduciary responsibility to your employer to keep his data safe and making changes directly to the production version of the database, especially one that actually contains data, is a serious risk. Changes, no matter how small, should always be made to the test version of the application. Once they work, you backup the production version and replace it with the test version and relink the tables. This is only done during off hours when the users are out of the database. Unless you are performing an urgent bug fix, no change should bring the application down during prime time. Schedule your updates off peak times to avoid disrupting production work.

 

[RodgerDJr]

Nick,

If you are looking for advice from an experts, you are getting it! Everyone is here to help and just because it is not what you want to hear, do not disregard their sound advice. It is not wise to reply with snide comments. Obviously, you are asking for help and want to learn so take this in and learn from what we are telling you. Also when you post you should not use short hand. Spell out your words, use spell check!

The advice you have gotten is very true you need to have the database split, if you cannot do this then you need to explain to those in charge the risk they are taking without doing such. If you have that many users connecting to the database as you claim you will for sure have corruption issues.

Think of Access like any other client server application you have the application installed on all the workstations and then there is a piece the database is on a server. You need to treat access just like you would any other application. Then you could make all the changes you want on one workstation and then when it has been tested and you are ready for your users to use it you could push it out to them.

Any other advice would not be sensible.

Rodger

 

[N1ck9141]

the reason i was unable to do this is cause i work on a site where everything has to be authorised and this was not, however i would still like to find a way of seeing who is logged on and kick them off if i need to...or want to weather my database is split into front end or back end or not

 

[DCrake]

Taking it literally "I want to know who is logged in or not." does not tell us alot.
What do you actually want to know about the people who are logged in?

Their name?
Their IP Address?
Their PC Name?
Their Windows Login Name?
Their Application Login Name?
Their Application Access Rights?
Etc

Somewhere some how you are going to have to employ a login screen with user names and passwords, which will be stored in a table somewhere. This will validate the user and record the date/time of login and upon exit record date/time end session.

some time later....

So you have done all this, and as Administrator/Developer you identify who is logged in or not. What do you want to do

Send them a message?
Log them out automatically?
Ring them up and ask them log out?
etc

Decide your objectives and requirements then evaluate the logical posibilities, can it be done? how long will it take? Is it actually worth it?

CodeMaster:

 

[N1ck9141]

ok in ENGLISH LOL

I have a database using the built in user name and password security tool that comes with ms access 2003
(this data base is on a shared network with many users )

i would like to create a form within the database,
which shows me the "usernames" who are currently logged on and the "time" they have been logged on for...

I would like this information to show in a text box...

secondly i would like a button so when i highlight the user in the text box and click the button they will be logged off...

(this next bit is just if possible no important)

i would like to create a second button which allows me to highlight the user in the textbox and when i click the botton send them a popup message containing the information from a different text box....

i really cant explain this any clearer or ne other so your best advise thank you

 

[DCrake]

I have no problem with what you are trying to achieve, however, from a database administrators point of view remotely disconnecting a user from a database without notice could lead to corruption. They could be in the middle of a record update at the point of loss of connection.

Conversely I would be more content with employing the use of idle time disconnection. To explain, I have an application that also uses multi users, and as users are they log in and go for lunch, whatever. This prevents me from opening up the database exclusively. There phone is never answered, and if they are at a remote site you cannot simply walk down the corridor and log out.

So the approach I took was to set up an idle time situation. This works by detecting any keypresses or mouse movements on a form. If no activity is detected for a set period, in my case 3 minutes, it closes the current open form. This does this retreating back to the main menu. if the current form is the main menu and the 3 minutes are up it then quits the application. In this situation I am confident that no processes were halted mid operation.

In answer to you second question if you use the NetSend command you can send messages to individual computers to inform them that they have been logged out automatically.

If you have administrator rights to your network and you have software that allows you to view active connections to certain type of files, in your case a specific mdb. You can drop the connection globally and take everybody out at once.

Only thinking out loud, I would not like to be the person who was responsible for causing the company's database to be corrupted.

CodeMaster:

 

[N1ck9141]

I like the idea of the net command to send messages hhow does this work and can it be incorporated into a form...

secondly i cant log them off after a curtain time thats not what they want (dnt as me why i jst do waht im told)

and they cant corrupt nething as they arent actually alloud to change nething jst view its tables charts etc...

so is there a way to KICK them off when i want, if not just say no but im sure there must be and this is what they want and what i need (by they i mean client)

 

[DCrake]

Sub ShowUserRosterMultipleUsers()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i, j As Long

    Set cn = CurrentProject.Connection

    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4.0 OLE DB provider.  You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets

    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

    'Output the list of all users in the current database.

    Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
    "", rs.Fields(2).Name, rs.Fields(3).Name

    While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1), _
        rs.Fields(2), rs.Fields(3)
        rs.MoveNext
    Wend

End Sub

This will show you a list of current users

Within a form:

Create a list box with four columns
set the row source type to Value list


replace

Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name

with

Me.LstConnections.AddItem (rs.Fields(0).Name & ";" & rs.Fields(1).Name & ";" & rs.Fields(2).Name & ";" rs.Fields(3).Name

Replace

Debug.Print rs.Fields(0), rs.Fields(1), _
rs.Fields(2), rs.Fields(3)
rs.MoveNext

With

Me.LstConnections.AddItem (rs.Fields(0) & ";" & rs.Fields(1)& ";" & rs.Fields(2) & ";" rs.Fields(3)

This should place all connected users into the list box

Have never logged a specific user out of a shared application before so I cannot help you on that.

 

[N1ck9141]

i will try this thanks for the advise, do i need to lable the list columns nething special or not thanks ??????

 

[N1ck9141]

Sub ShowUserRosterMultipleUsers()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i, j As Long
 
    Set cn = CurrentProject.Connection
 
    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4.0 OLE DB provider.  You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets
 
    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
 
    'Output the list of all users in the current database.
 
    Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
    "", rs.Fields(2).Name, rs.Fields(3).Name
 
    While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1), _
        rs.Fields(2), rs.Fields(3)
        rs.MoveNext
    Wend
 
End Sub

This will show you a list of current users

Within a form:

Create a list box with four columns
set the row source type to Value list


replace

with




Replace




With



This should place all connected users into the list box

Have never logged a specific user out of a shared application before so I cannot help you on that.

sorry known of this worked it complains about syntax errors for both and the form showed nothing at all i dnt see where its connected should it be named something sepcail

 

[DCrake]

The connection is the currently opened database

cn = CurrentProject.Connection

If you have saved the sub as a function in a module and run it through the immediate window does it work there?

 

[N1ck9141]

the original code the code i found and have used before the one u surjested worked in the module window...

what didnt work where was the changes u selected they gav syntax errors...

this has become a new question really of how do i change this...

Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name


to something that will display in a form with a columed list box

 

[N1ck9141]

the original code the code i found and have used before the one u surjested worked in the module window...

what didnt work where was the changes u selected they gav syntax errors...

this has become a new question really of how do i change this...

 Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name

to something that will display in a form with a columed list box