Hello everybody.
I have a classmodule named “classFrmMasters” with the following code:
********** classmodule code **********
Private WithEventsclsVarFrmMaster As Form
Private WithEventsclsVarFrmSubCtrl As SubForm
‘routine called from the form declaring the class module
Public SubinitFrm(frm As Form)
Set clsVarFrmMaster = frm
With clsVarFrmMaster
Set clsVarFrmDataSheet =!frmSubCtrl.Form
.OnLoad = "[Event Procedure]"
Set clsVarFrmSubCtrl = !frmSubCtrl
clsVarFrmSubCtrl.OnExit = "[EventProcedure]"
End With
End Sub
‘function neededto check whether the sub form already has records
Private Function subFormHasData()As Boolean
subFormHasData =clsVarFrmSubCtrl.Form.Recordset.EOF
End Function
Private SubclsVarFrmMaster_Load()
‘codefor the Form_Load event
If subFormHasData=True then
‘dosomething
Else
‘dosomething else
End If
End Sub
Private SubclsVarFrmSubCtrl_Exit(Cancel As Integer)
‘code for the sub form “Exit”event.
‘……
End Sub
********** end classmodule code **********
I have severalforms each containing a sub form control. Each form has the following code:
********** formmodule code **********
Option CompareDatabase
Option Explicit
Dim clsFrmMasterAs classFrmMasters
Private Sub Form_Open(CancelAs Integer)
Set clsFrmMaster = New classFrmMasters
clsFrmMaster.initFrm Me
End Sub
Private SubForm_Unload(Cancel As Integer)
Set clsFrmMaster = Nothing
End Sub
********** end formmodule code **********
The code works perfectly.The only problem is that after referring to the sub form through the “subFormHasData()”function in the class module I loose exclusivity access to the database. So,after exiting code, if I try to apply some design changes to forms or to modulesor other MS-Access objects I get an error message “You do not have exclusiveaccess to the database at this time. If you make changes, you may not be ableto save them later.".
In order to fixthis problem I have either to close and re-open the database file, or tocompact and repair it but this is quite boring and time wasting.
It seems that the cause is that by referring to the subform a new connection to the database isopened. I found the following routine on Microsoft Support site which determineswho is logged on to a database.
********** msroutine code **********
SubShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
Set cn = CurrentProject.Connection
' The user roster is exposed as aprovider-specific schema rowset
' in the Jet 4.0 OLE DB provider. You have to use a GUID to
' reference the schema, asprovider-specific schemas are not
' listed in ADO's type library for schemarowsets
Set rs =cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
'Output the list of all users in thecurrent database.
Debug.Print rs.Fields(0).Name,"", rs.Fields(1).Name, _
"", rs.Fields(2).Name,rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1),_
rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend
End Sub
********** end msroutine code **********
I copied and pastedthe above routine on a module and run it before and after opening the form callingthe “classFrmMasters”.
-Below is theoutput result of the routine BEFORE opening the form:
COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
DESKTOP-L708R4V Admin True Null
-Below is theoutput result of the routine AFTER opening the form:
COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
DESKTOP-L708R4V Admin True Null
DESKTOP-L708R4V admin True Null
There is a newuser named “admin” (with the lower “a”) logged who now has exclusive access tothe database!
Does anybody knowshow I could fix this problem?
Thank you
Arti