see if an Access databse is in use

[pcc]

I am trying to see if a database is in use or not, but only have Access 97 so the links posted by C T Witter are no use. (Post from Access forum, sorry I have mistakenly posted this to the Access forum and meant it to go here )

http://www.mrexcel.com/board2/viewtopic.php?p=1118358#1118358
I like Norie's suggestion of lookng for the .ldb file. This works fine when the access database is read-write, and I have implemented this idea.
However, I have several databases that are in a read-only folder (with me being the only read-write person). In this case the .ldb file is only created when I connect to the database: for others there is no file created.
Whilst the code I provide connects them to the database in read-only mode, I still get occasional errors if more than one user is reading the data at the same time. I would like to see if the database is being read, and if so, put a loop in the code that waits until it is not, thus avoiding simultaneous access by >1 person. Any ideas how to accomplish this with a read-only databse

 

[sam slade]

Hi pcc,

Is it possible you can alter the database(s) in any way, if so you could build a login form whereby the users name is recorded in a table and thus you can access that table to see if someone is in the database. Though i have found this can be circumvented by entering another users details.

I did advise in another thread that you can grab the logged on users window details and use that to record that they are in the database.

Does this help?

Sam

 

[pcc]

Hi pcc,

Is it possible you can alter the database(s) in any way, if so you could build a login form whereby the users name is recorded in a table and thus you can access that table to see if someone is in the database. Though i have found this can be circumvented by entering another users details.

Good suggestion, but the Access database is only used as a repository of information - all read and write processes are done using Excel VBa, so I don't think this is an option.

I did advise in another thread that you can grab the logged on users window details and use that to record that they are in the database.

Not sure what you mean by this? Can you elucidate, or if it requires doing something directly in Acess then it's probably not an optio, either.
Thanks for reply.

 

[sam slade]

pcc,

you can set up a similar login through Excel. Each time the user queries from Excel the users name is recorded first then the query run.

I worked for bank where they only gave us Excel, but we found a stray copy of Access and used it in a similar vein, using Excel to store and retrieve data.

Getting the users details is simple, you search the windows Environs settings for the users name. Which you can do in Excel. Pass the data to Access.

May i ask though, if a user is not using Access to 'access' the db directly, then there is no permanent connection to the database, unless it is a pivottable. You could write a simple txt file and append to it each time a user queries the db

Any clearer. I try not to get too technical.

Sam

 

[pcc]

Thanks, you've given me what I need as an idea. Whenever a user connects, I can write a record to a log file (in a global read-write area of the LAN that I have). When the connection is closed, the record can be deleted. Thus, a check can be made before connection, and if there are any records, then a loop can be started until the number of records is zero again.
Thanks very much

 

[sam slade]

you could also use a maintanence file as a means of preventing users connecting to the db simply by checking a key word in the file before opening a connection, and preventing connection if the key word is there.

Sam

 

[pcc]

Good idea. Thanks once again, I am busy doing the code right now!!

 

[sam slade]

nice one pcc,

i wish i was coding right now, unfortunately i am comparing factoring index numbers, using a complex vba routine and charting the results to see the trend .... it's doing my nut in!!!

time for a nice cuppa me thinks.

glad i could help.