How do I kick people off the database? 1

[Guest_imported]

We have a macro that pulls in data from our AS/400 (using ODBC) , and formats it for use in Access. Unfortunately, this macro will not work if someone is in the database. The table will not update. I am wondering if there is some way of knocking everyone out of the database before updating the table. Or if there is any other way of updating the table while a user is accessing it (A virtual table or something). Any help will be appreciated. Thanks. <p>John Vogel<br><a href=mailto:john@computerwiz.net>john@computerwiz.net</a><br><a href=

http://computerwiz.net>ComputerWiz</a><br>

 

[elizabeth]

Access can be temperamental about how people exit. You can throw people out but you may end up corrupting something. Our solution has been to set up these types of jobs to run during off hours.

 

[Guest_imported]

I have thought of that. I need something though, to &quot;gracefully&quot; exit a person. Since, #1.) Our company doesn't really have any &quot;off hours&quot;, and #2) People seem to have a &lt;Very Bad&gt; habit of leaving a query open, when they leave. Another scenerario I have thought up is to use net send to send a message to everyone who has the database open... if I could figure out how to even tell WHO is on, then that would work. But then that leaves, what to do with people who leave their Access up when they leave their office? <p>John Vogel<br><a href=mailto:john@computerwiz.net>john@computerwiz.net</a><br><a href=

http://computerwiz.net>ComputerWiz</a><br>

 

[elizabeth]

You can tell who is on by viewing (in NT) who is in the .ldb - same name as the database but .ldb extension. This file creates itself when the first user opens the .mdb and deletes itself when the last user has left. I used to view the users and then make irate phone calls to them as it was generally the same ones time after time. If you delete the .lbd file, which is the locks, you will throw everyone out. If you have a utility to back up an open file, you could just do a backup first.

 

[ag]

To add to that, another route to viewing users logged into an MDB file is to use the LDBView utility (<A HREF="

http://support.microsoft.com/support/kb/articles/Q176/6/70.ASP"

TARGET="_new">

http://support.microsoft.com/support/kb/articles/Q176/6/70.ASP</A>).

This allows you to monitor current users, historic users and users who have left the database in a suspect state. There is even a DLL included that you can use to wrap this functionality into an application component, if you so wish.<br>
<br>
This works on non-NT platforms as well.<br>

 

[elizabeth]

ag - thanks, will find that very useful - elizabeth

 

[Visitor]

say, noticed 2 problems (for me):<br>
1) this only seems to work on the back end (data) db, not the front end. how come?<br>
2) the user shown is the machine name not the login name

 

[ag]

Elizabeth...no problem....I've used for a couple of years now and it has been a major timesaver.<br>
<br>
Re. the other questions:<br>
<br>
1) this only seems to work on the back end (data) db, not the front end. how come?<br>
<br>
Can you explain a little more about what you mean by the front-end please.<br>
<br>
2) the user shown is the machine name not the login name <br>
This seems a little strange I agree. If you examine the DLL there are 5 routines, only one of which is intended for calling by a developer seeking a user list. This function returns the machine name, when in fact if you view the ldb file (even in notepad) you will clearly see the username (Access username as opposed to OS login) listed along side the machine name. <br>
<br>
Whether the design is such that one assumes that by contacting the user at a particular machine one deals with all sessions of Access on that machine, irrespective of login details (thus a username list is arguably redundant), but I agree that what would be useful would be a machine name _and_ a username.<br>
<br>
Perhaps MS would care to comment....perhaps not as it is not officially supported.<br>
<br>
I notice that Michael Kaplan was involved with the design of the sample app, and for anyone who doesn't know, he can be found on the microsoft public newsgroups on a regular basis.<br>
<br>
Andrew<br>
<br>

 

[Visitor]

thanks ag - by front end i mean all objects except data tables; the app.

 

[ag]

Hi,<br>
<br>
Apologies if I'm missing the point completely here...<br>
<br>
a) If you are using an app built in MS Access, you have to log into the MDB to use it and therefore are an active user within that MDB, and thus will show up on the LDBView.<br>
<br>
b) If you are using a VB/VC etc front-end and do not have an active connection to the MDB file (i.e. you are not accessing any data at the time) then you may well be logged out of the MDB, depending on how you app functions. This would cause you not to be listed in the current user list, but maybe the historic list.<br>
<br>
I hope this is what you are getting at. If not, let me know <br>
<br>
Andrew<br>
<br>
<br>

 

[CincyScott]

Thanks, Andrew, for the pointer to the jetutils utility. I'm probably the odd man out here, using Delphi 5 and the BDE (Borland Database Engine) to write the front end to an Access 97 back end, but those utilities look to be of value to me, as well, at least from the descriptions given on MS's download page.<br>
<br>
Scott

 

[Guest_imported]

Thanks. This makes things alot easier. I can't believe I didn't think of the ldb file. I can easily write a visual basic routing that loads the ldb, seperates the machine name/user name, sends a message (via net send) to all the machines, something like &quot;WRANING! SecureP.MDB will be updating in 15 minutes. All users should exit their ACCESS applications now. Thank you&quot; Or something to that effect. Then wait 15 minutes, delete the ldb file, fire up the Access and run the macro (not sure how I am going to accomplish the latter though). This shouldn't be too difficult! Thanks again hey, and if anyone wants to see the VB code I'll email it to them, or maybe just post it here (Not sure if anyone is interested in this type of program though).<br>
<br>
Thanks again, and God Bless!<br>
<br>
John Vogel <p>John Vogel<br><a href=mailto:john@computerwiz.net>john@computerwiz.net</a><br><a href=

http://computerwiz.net>ComputerWiz</a><br>

 

[ag]

John,<br>
<br>
Always interested to see other peoples' solutions to problems like this.....please post when you get things running.<br>
<br>
Cheers<br>
<br>
Andrew<br>

 

[elizabeth]

Just learned there is a free utility called FMS Jet4.adm that might be useful for this. Haven't tried it yet myself but will try to remember to post here when do.<br>
<br>
<A HREF="

http://www.fmsinc.com/free/utilities/index.html"

TARGET="_new">

http://www.fmsinc.com/free/utilities/index.html</A>