Question Database needs to be repaired.......but

[Joe8915]

I have a database that is on a network. The database will crash at times, when it does I copy the db and paste on my drive and compact. But what I like to do is kick out all the users before I pasted back to the network.

Is there anyway I can kickout the users before I paste the db back to the network.

Thanks

 

[Scooterbug]

This doesn't answer your question...but it will help correct the problem.

Is your database split into a front end/back end? I'm guessing not, which is why you are running into corruption issues.

The back end should house the tables. The front end, which should be on each users computer, should contain the forms, queries, reports and modules. Tables should be linked. Searching the forums here for Splitting Database will turn up a wealth of information.

 

[Joe8915]

You are so right. I have to waite now until Oct 1 to split. I am paying the price for sure. When they say split ..............."DO IT"

 

[The_Doc_Man]

The way to kick out all users is tricky because you want it to be orderly. The way I do this is to put an event table up, hide it, and define an opening form (startup form). Let that form act as a secondary splash. When it is time to drop out of the way, do not close it, just minimize it and make it invisible. But let it run in the background. Or make it your switchboard if that is the way you work, in which case it stays active and visible.

In either case: Put timer code underneath that form. (Don't do this on the copy you use for development, as having a running timer interferes with autocomplete and other features that help you develop.) Once per minute or so, test a table that is hidden, but you know about it. Put two times in the that table representing the start and end of a "down-time" event. Have the form check for the current time being between the start and end time. If so, have it do an Application.Quit, which will quickly dump the instance.

The comments about splitting between FE and BE are also relevant, but that was noted already so I won't belabor that issue.

 

[Vassago]

You can even go a step further and add a warning in 10 minutes prior to your "start time" warning them they will be logged out of the database for repair and maintenance. This is what I did to a database of mine years back.

 

[Joe8915]

Thanks all.
The only problem is thats when you can get into the database. I have the option running and works great when the database is still up and running. Its when NO ONE can get into the db is when I have a problem. Thats where I have to call the IT team and have them log off all users from the drive where the db resides on. I just curious if I can do the same thing but try to relate it to the db in some way.

 

[Vassago]

You should set up the front end/back end and you'll most likely avoid that issue altogether.

 

[RuralGuy]

Jeff Conrad has a number of KickEmOff samples to pick from on his site.

 

[boblarson]

And Roger Carlson has one too.

 

[RuralGuy]

Hi Bob,
Two of the first 3 on Jeff's page are from Rogers site.

 

[boblarson]

Hi Bob,
Two of the first 3 on Jeff's page are from Rogers site.

Ah, oh well -

 

[Wet_Blanket]

Sorry for digging up an old thread. I decided to go with the following module to identify users logged into the database:

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

Problem is, I don't understand the code and my users are logged into the database based on their windows log in, so the output from this module doesn't tell me much. What can I change to show the user's windows log in?

Here is what I get:

ShowUserRosterMultipleUsers
COMPUTER_NAME               LOGIN_NAME                  CONNECTED     SUSPECT_STATE
RIGTS01                                   Admin                                     True          Null