LDBUser_GetUsers error: subscript out of range

[Mourad]

I'm calling LDBUser_GetUsers() function (provided by Microsoft as a
part of JetUtils).
Then when referencing the first element (index 0) I'm getting the
error "Subscript out of range"

This code is a sample fromMicrosoft website:

ReDim lpszUserBuffer(1) As String
Dim intLooper As Integer
Dim Cusers As Long

On Error GoTo Err_GetUsers

strUsers = ""
StrDbPath = "abx.mdb"

' Set Cusers to the number of computers currently connected
' to the database. Insert computer information into the
' lpszUserBuffer array.

' Arguments of LdbUser_Get Users:
' 1 = All users who have logged in since the LDB file was
' created
' 2 = Only users who are currently logged in
' 4 = Only users who are causing the database file to be
' corrupted
' 8 = Just return the count of users

Cusers = LDBUser_GetUsers(lpszUserBuffer(), StrDbPath, 2)


' Print possible errors returned by the function.
Select Case Cusers
Case -1 ' when it is open
by Access 2.0
strUsers = "Can't open the LDB file for " & DBCode
strUsers = strUsers & ". File may be locked by one or more
Access 2.0 users "
Case -2
strUsers = "No user connected to " & DBCode
Case -3
strUsers = "Can't Create an Array"
Case -4
strUsers = "Can't redimension array"
Case -5
strUsers = "Invalid argument passed"
Case -6
strUsers = "Memory allocation error"
Case -7
strUsers = "Bad index for " & DBCode
Case -8
strUsers = "Out of memory"
Case -9
strUsers = "Invalid Argument"
Case -10
strUsers = "LDB is suspected as corrupted for " & DBCode
Case -11
strUsers = "Invalid argument"
Case -12
strUsers = "Unable to read MDB file for " & DBCode
Case -13
strUsers = "Can't open the MDB file for " & DBCode
Case -14
strUsers = "Can't find the LDB file for " & DBCode
Cusers = 0
End Select

if Cusers > 0 Then
For intLooper = 0 To Cusers - 1
Debug.Print lpszUserBuffer(intLooper) '<<<< Gives error
"Subscript out of range"
Next
end if


Thanks for reading,

Mourad

 

[Clifford Bass]

Hi Mourad,

Try adding the following line to the top of your module, outside of any
functions or subroutines:

Option Base 0

Hope that helps,

Clifford Bass

 

[Mourad]

Thanks, Clifford

I'll certainly try this. Even though the this is the default setting,
and the error does not occur all the time.

Let's see

Thanks,

Mourad

 

[Clifford Bass]

Hi Mourad,

I am not familiar with the LDBUser_GetUsers function. You might want
to debug it if you can. It is possible that LDBUser_GetUsers may be
redimensioning the array to have an explicit lower bound of 1. For example:
ReDim MyArray(1 to 5) As String. Or it could be using the Option Base 1
setting, which can cause an implicit change of the lower bound. For example:

If this is in one module:

Option Base 0

Public Sub Test()

ReDim xxx(1) As String

MsgBox LBound(xxx)

ReDimMe xxx

MsgBox LBound(xxx)

End Sub

And this is in another:

Option Base 1

Public Sub ReDimMe(ByRef strArray() As String)

ReDim strArray(5)

End Sub

The results of running Test() will be 0 and then 1. So, it may be
better to change the For line to this :

For intLooper = LBound(lpszUserBuffer) To UBound(lpszUserBuffer)

Hope that helps,

Clifford Bass

 

[Mourad]

Thanks, Clifford , for th hint,

You are right. The array LBound is 1, and sometimes is 0!! so the
safest way, as you said, is to use the LBound function.

Would you please tell how can I debug the dll? I'm certainly curious
to know how it works.

Thanks again,

Mourad

 

[Clifford Bass]

Hi Mourad,

You are welcome!

My statement "You might want to debug it if you can." was based on the
predicate that it was something provided to you as VBA code, or maybe as a
DLL with along with its source code. Since it is a DLL supplied by Microsoft
without any code I cannot help.

Clifford Bass

 

[James A. Fortune]

I'm calling LDBUser_GetUsers() function (provided by Microsoft as a
part of JetUtils).
Then when referencing the first element (index 0) I'm getting the
error "Subscript out of range"

This code is a sample fromMicrosoft website:

ReDim lpszUserBuffer(1) As String
Dim intLooper As Integer
Dim Cusers As Long

On Error GoTo Err_GetUsers

strUsers = ""
StrDbPath = "abx.mdb"

' Set Cusers to the number of computers currently connected
' to the database. Insert computer information into the
' lpszUserBuffer array.

' Arguments of LdbUser_Get Users:
' 1 = All users who have logged in since the LDB file was
' created
' 2 = Only users who are currently logged in
' 4 = Only users who are causing the database file to be
' corrupted
' 8 = Just return the count of users

Cusers = LDBUser_GetUsers(lpszUserBuffer(), StrDbPath, 2)


' Print possible errors returned by the function.
Select Case Cusers
Case -1 ' when it is open
by Access 2.0
strUsers = "Can't open the LDB file for " & DBCode
strUsers = strUsers & ". File may be locked by one or more
Access 2.0 users "
Case -2
strUsers = "No user connected to " & DBCode
Case -3
strUsers = "Can't Create an Array"
Case -4
strUsers = "Can't redimension array"
Case -5
strUsers = "Invalid argument passed"
Case -6
strUsers = "Memory allocation error"
Case -7
strUsers = "Bad index for " & DBCode
Case -8
strUsers = "Out of memory"
Case -9
strUsers = "Invalid Argument"
Case -10
strUsers = "LDB is suspected as corrupted for " & DBCode
Case -11
strUsers = "Invalid argument"
Case -12
strUsers = "Unable to read MDB file for " & DBCode
Case -13
strUsers = "Can't open the MDB file for " & DBCode
Case -14
strUsers = "Can't find the LDB file for " & DBCode
Cusers = 0
End Select

if Cusers > 0 Then
For intLooper = 0 To Cusers - 1
Debug.Print lpszUserBuffer(intLooper) '<<<< Gives error
"Subscript out of range"
Next
end if


Thanks for reading,

Mourad

This is only related to your question, but perhaps the following will
help someone:

I made some changes to a WhosOn() function called from and contained
within a form called frmLoggedOn. I'm not sure where I got it from, but
the following post:

http://groups.google.com/group/comp.databases.ms-access/msg/bca1fda000f85d6f

shows that the original code is quite similar to code that Arvin Meyer
posted 10 years ago. Reading the .ldb file is a simple task anyway and
it seems that many people use that function or something very similar to
return the machine names from the .ldb file. I got a list of each
user's machine ID by right clicking 'My Computer' and recording the
'Full computer name' from the 'Computer Name' tab. I put the machine
and user names in the following lookup table assuming that most of the
time the user logs in from their own computer:

tblMachineUserLookup
MULID AutoNumber
FullName Text
DefaultComputerNetworkID Text

Sample:

MULID FullName DefaultComputerNetworkID
1 Fred Hsieh D115JYB1MRAM
2 Carol Jones CarolInAccounting
3 Jeff Alvarez MyCompany-f4rvgm9

Here is the altered function:

'-------------------------------------------------------------------------------------
' Subject : WhosOn()
' Purpose : Will read *.LDB file and read who's currently
' logged on and their station name.
'
' The LDB file has a 64 byte record.
'
' The station name starts at byte 1 and is null
' terminated.
'
' Log-in names start at the 33rd byte and are
' also null terminated.
'
' I had to change the way the file was accessed
' because the Input() function did not return
' nulls, so there was no way to see where the
' names ended.
'-------------------------------------------------------------------------------------
Private Function WhosOn() As String

On Error GoTo Err_WhosOn

Dim iLDBFile As Integer, iStart As Integer
Dim iLOF As Integer, i As Integer
Dim sPath As String, x As String
Dim sLogStr As String, sLogins As String
Dim sMach As String, sUser As String
Dim rUser As UserRec ' Defined in General
Dim dbCurrent As Database
'--------------------- I added the following variables:
Dim MyDB As Database
Dim MyRS As Recordset
Dim strSQL As String

' Get Path of current database. Should substitute this code
' for an attached table path in a multi-user environment.

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
sPath = dbCurrent.Name
dbCurrent.Close

' Iterate thru dbCurrent.LDB file for login names.

sPath = Left(sPath, InStr(1, sPath, ".")) + "LDB"

' Test for valid file, else Error

x = Dir(sPath)
iStart = 1
iLDBFile = FreeFile

Open sPath For Binary Access Read Shared As iLDBFile
iLOF = LOF(iLDBFile)
Do While Not EOF(iLDBFile)
Get iLDBFile, , rUser
With rUser
i = 1
sMach = ""
While .bMach(i) <> 0
sMach = sMach & Chr(.bMach(i))
i = i + 1
Wend
i = 1
sUser = ""
While .bUser(i) <> 0
sUser = sUser & Chr(.bUser(i))
i = i + 1
Wend
End With
'---------------------
'See if sMach is in tblMachineUserLookup.
'If so, replace sMach with tblMachineUserLookup.FullName
Set MyDB = CurrentDb
strSQL = "SELECT FullName FROM tblMachineUserLookup WHERE
DefaultComputerNetworkID = " _
& Chr(34) & Trim(sMach) & Chr(34) & ";"
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If MyRS.RecordCount > 0 Then
MyRS.MoveFirst
sMach = MyRS("FullName")
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
'---------------------
sLogStr = sMach
If InStr(sLogins, sLogStr) = 0 Then
sLogins = sLogins & sLogStr & ";"
End If
iStart = iStart + 64 'increment to next record offset
Loop
Close iLDBFile
WhosOn = sLogins

Exit_WhosOn:
Exit Function

Err_WhosOn:
If Err = 68 Then
MsgBox "Couldn't populate the list", 48, "No LDB File"
Else
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Close iLDBFile
End If
Resume Exit_WhosOn

End Function
'-------------------------------------------------------------------------------------

Note that the machine name is returned if it is not found in the table.

Sample output:

Logged On Users

Logged on Users
_______________
Carol Jones
Fred Hsieh
MyCompany-f138049
_______________

I wanted the list to show in the same order as read from the .ldb file
so I didn't need to break up the names for sorting. I think I left off
the user string from the .ldb file since that is usually Admin. A
DLookup() function could have been used instead of opening a recordset.
The code change is very simple but there might be a lot of users who
never bothered to set up a lookup table. Watch out for line wrap.
Also, remember that it's possible in some cases for a user to exit
Access yet still show up in the .ldb file so don't make too many firm
conclusions when a user shows up in the .ldb file.

James A. Fortune
(e-mail address removed)