I use Access97 for a Project Control application based in MS Access Forms. As the number of users grow it is becoming pretty unconfortable to perform any changes in the database design since the db is constantly locked.
Do you know a way to have Access to list "on-line users" ? do you have any advice about the best way to perform design changes to a production db?
Hi Juan. A couple of ideas. If you have security enabled, use the CurrentUser function to return the users logged in. If you are on a network, then you could use the following function.
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function GetNTUser() As String
'Returns the network login name
Dim strUserName As String
'Create a buffer
strUserName = String(100, Chr$(0))
'Get user name
GetUserName strUserName, 100
'Strip the rest of the buffer
strUserName = Left$(strUserName, InStr(strUserName, Chr$(0)) - 1)
GetNTUser = strUserName
Have you thought about splitting the DB into front-end and back-end, which I think is much more workable? If you do want to go down that route, have a look at the Microsoft Knowledge base. There is an article on manually splitting, You can also use the Database Splitter utility to split a database. For more information about how to use the Database Splitter utility, click Microsoft Access Help on the Help menu, type split an access database in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
I used CurrentUser() function already, but it only gives me my own user ID. Is there a way to make it answer with the rest of the users? I have even thought about adding a table and storing each user login once the startup for is accessed, but I'm trying to find another option, hopefully accesing the locking records directly if possible (.ldb file). I wonder that there should be a way to do this, since Access display the person who is locking a resource if you try to get writing access to it...
I wonder if the get user code you wrote would work in a similar fashion that CurrentUser() by giving the actual user who issues it instead the universe of logged users... I will try it out.
On the other hand, I will read the knowledge base article, because I don´t have any experience spliting Data bases...