see if an Access databse is in use

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,347
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 :oops: )

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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

sam slade

New Member
Joined
Sep 14, 2006
Messages
22
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

Well-known Member
Joined
Jan 21, 2003
Messages
1,347
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

New Member
Joined
Sep 14, 2006
Messages
22
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

Well-known Member
Joined
Jan 21, 2003
Messages
1,347

ADVERTISEMENT

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 :biggrin:
 

sam slade

New Member
Joined
Sep 14, 2006
Messages
22
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

Well-known Member
Joined
Jan 21, 2003
Messages
1,347
Good idea. Thanks once again, I am busy doing the code right now!!
 

sam slade

New Member
Joined
Sep 14, 2006
Messages
22
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!!! :devilish:

time for a nice cuppa me thinks.

glad i could help.
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top