Check Who is Using an MDB

Test who is using an Access database

;Test who is using an Access database
;(may also work with SQL Server)
;
;assumes you have MDAC/Jet Provider installed
;
;Stan Littlefield, November 23 2008
;////////////////////////////////////////////////////////////////////////////////////////////////

GoSub udfs
;no error handler calls in UDFs - use your own

;typical usage
;check for existance of file
cMDB = "path\filename.mdb"
If ! FileExist(cMDB)
   Display(1,"Error",cMDB:" Database Not Located")
   Exit
EndIf

;display who is currently using the database
cConn="Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%;"
oConn = CreateObject("ADODB.Connection")
oConn.CommandTimeOut = 0
oConn.Open(cConn)
GetDBUsers(oConn,cMDB)
closeConn()

Exit
;////////////////////////////////////////////////////////////////////////////////////////////////



udfs:
#DefineFunction GetDBUsers(cO,cDB) ;cO is an open Connection Object, cDB the Database
; Recordset Fields from OpenSchema
; 0 - COMPUTER_NAME:    Workstation
; 1 - LOGIN_NAME:       Name used to Login to DB, or null
; 2 - CONNECTED:        True if Lock in LDB File, or null
; 3 - SUSPECTED_STATE:  True if user has left database in a suspect state(else Null)

adSchemaProviderSpecific = -1
JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
oRS=CreateObject("ADODB.Recordset")
oRS.ActiveConnection=cO
oRS.ActiveConnection.CommandTimeout = 600
oRS = cO.OpenSchema(adSchemaProviderSpecific,,JET_SCHEMA_USERROSTER)
If oRS.eof
   Display(2,cDB,"Is Not In Use")
Else
   Display(5,cDB,oRS.GetString())
EndIf
oRS.Close()
oRS=0
Return(1)

#EndFunction

#DefineSubRoutine crConn(vCon)
oConn=CreateObject("ADODB.Connection")
oConn.ConnectionTimeOut=0
oConn.CursorLocation=3 ;or 2
oConn.Open(vCon)
While oConn.State==0
   TimeDelay(.5)
EndWhile
Return(1)
#EndSubRoutine

#DefineSubRoutine closeConn()
If isObject(oConn)
   If oConn.State<>0 Then oConn.Close()
EndIf
oConn=0
Return(1)
#EndSubRoutine


#DefineFunction isObject(obj)
Return(VarType(obj)>=1024)
#EndFunction

Return
;////////////////////////////////////////////////////////////////////////////////////////////////

Article ID:   W18035

Filename:   Check Who is Using an MDB.txt

File Created: 2008:11:25:12:16:28

Last Updated: 2008:11:25:12:16:28

Database Search

TechHome

Check Who is Using an MDB