Connected users?

[Gasman]

Hi,

Some of my colleagues appear not to understand that they need to close down their FE at the end of the working day, so can have exclusive access to the BE and compact the BE.

In a log file on my home system using Access 2003 it shows the computer name in clear in Notepad. I tried opening the be.laccdb file in work with Notepad, but just got illegible characters.

Is there anyway to force the users to be disconnected. Data integrity is not an issue.

I have managed to get around this by copying the locked BE to another location, compacting and copying back, overwriting the live BE file tonight, but would expect this is not the best way to do things.

So I either need to kick everyone off the BE or find out who they are?

As an aside what size can my BE grow to before I can expect problems.

I compacted it today from 240MB to around 20MB. It grew to that size in about 4 days, presumably due to the fresh data being imported each day.

TIA

 

[BigHappyDaddy]

I think I would tackle this problem in this fashion.

In the FE, open a form and keep it hidden. In the timer event of this form I would probably use an IF...THEN statement. If current time is >= to "After Work hours" then close all open forms, then Application.Quit. You will have to determine what "After Work Hours" is for your situation.

Finally, set the Timer Interval for the hidden form to at some interval as to not impact the performance during the work day, but often enough that you can ensure all front ends are to be closed soon after "After Work Hours" + Timer Interval.

That help?

 

[BigHappyDaddy]

Also, Access is limited to 2GB per file. After that, you are begging for problems.

Simply put, file "bloating" is due to actions that could increase objects (tables, forms records, etc) followed by actions that reduce objects (deleting records, tables, forms).

In some cases, you won't be able to get around that issue. But there are situations where moving a table from the BE to the FE (like a TEMP table) will transfer the bloat from the BE to the FE. Then you address FE bloat by forcing the users to obtain a fresh copy of the FE each day.

Just one of many options...

 

[jdraw]

See this link for more info

 

[gemma-the-husky]

it is tricky. you can assume it is safe to boot users off, but you never know. restarting the be server is drastic, but is guaranteed to clear users.

one thing - the be locking file bename.ldb, or bename.laccdb will not be there if no users are logged in. If it is there, it may be an error. If you can delete it, then there are no users logged in. If you cannot delete it, then users ARE logged in.


I struggle to see how you could reliably overwrite an in-use file. I am not sure the copy would actually work.

 

[smig]

File 'bloat' is mostly because of Access indexing every field that in Number format.
I mostly see bloats of the FE not the BE

 

[Gasman]

Thanks for thsoe options. I like the form option as I already have a hidden form at startup that maintains a persistent connection.

I removed all indices for numeric fields in tables that have the imported data.

Data is refreshed every day, so most data is deleted and reimported. A few fields are updated during teh day for each persons allocation. A few tables remain static, Users, lookups, Analysts, small tables.

I found a link on the MS site to compact DB from another DB, and was hoping that there might be a way of deleting the entries for the connected users as well.

 

[Gasman]

If it is of any interest to anyone else, I have found this http://support.microsoft.com/kb/285822 (How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access)

 

[winshent]

If you create a config table in the back end with a bit field named 'GetOut', that you can edit manually.

Build the app so that the forms check this flag every minute or so using the Form_Timer() event.

Build in some code that checks the flag, and closes the users front end within a few minutes if it is set.

 

[Gasman]

Another good idea. I presume by bit field you mean the logical yes/no field?
I've just looked at the laccdb file just now and it shows in clear

057A0235209                     Admin                           057A0235205                     Admin                           057A0235100                     Admin                           057A0275538                     Admin                           057A0235218                     Admin                           WKS3287888                      Admin                           057A0235225                     Admin                           057A0235207                     Admin                           057A0235174                     Admin                           057A0235227                     Admin                           057A0235083                     Admin                           057A0276060                     Admin                           057A0216832                     Admin                           WKS3287886                      Admin                           057A0235223                     Admin                           057A0235103                     Admin                           057A0235102                     Admin                           057A0117116                     Admin                           057A0235104                     Admin                           057A0158154                     Admin                           057A0221761                     Admin                           WKS3287869                      Admin                           057A0216827                     Admin                           057A0235202                     Admin                           057A0149861                     Admin                           057A0235100                     Admin

The code that MS offers, only shows the user logged in at the FE.
When I try and insert it in the BE it complains on the first DIM statement for New ADODB Recordset.?

However for now if I can read the laccdb file I can look up the user as I will add their computer names to the User table I have. I will however investigate the Form Timer and Interval event.property and how to use them, not something I have used before.

If you create a config table in the back end with a bit field named 'GetOut', that you can edit manually.

Build the app so that the forms check this flag every minute or so using the Form_Timer() event.

Build in some code that checks the flag, and closes the users front end within a few minutes if it is set.

 

[Gasman]

Really warming to this idea.

I could send a warning message when I first find out the flag is set and after a set time shutdown their FE.

That way I do not have to rely on set hours, as normally I want them off at the end of the working day, so I can do whatever I need to do on the BE.

I think I will still incorporate their computer names in the user table to identify those not toeing the line when asked...:banghead: