Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Looking Access 2007 version to Identify LDB locks

Posted on 2013-05-09
16
Medium Priority
?
481 Views
Last Modified: 2013-05-10
Looking for code to identify if an LDB is active and If so Send email asking user to exit the database.

K
0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
16 Comments
 
LVL 75
ID: 39153955
This will:

http://fmsinc.com/MicrosoftAccess/monitor.asp

I use it at work daily.

mx
0
 

Author Comment

by:Karen Schaefer
ID: 39153963
thanks for the company will not authorize the  purchase.  any code available or an particular error message to indicate that the ldb is active hence it is in use.

Karen
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39154060
The ldb file will not give you this info.
The ldb file contains the id of users/machines that have opened the database since the ldb file was created.  Such users may have long since closed their frontend but their entry remains in the ldb file until it overwritten by another or until the ldb file is closed.

If you are not into 3rd party apps then you can build a 'flag' table into the backend.  The front-end needs a timer event on a form that is always open, and in the timer event procedure you test whether the flag is set.  If so you can issue a message to the user asking them to close down.  You then need a procedure that sets the flag , and one that unsets it.  You may put those into a separate frontend 'management' application.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 75
ID: 39154107
"but their entry remains in the ldb file until it overwritten by another or until the ldb file is closed."
But there is a flag in the LDB file that will tell you if user is active or has closed.  FMS shows this.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39154390
you can use this codes


Sub ShowUserRosterMultipleUsers()
    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 a provider-specific schema rowset
    ' in the Jet 4.0 OLE DB provider.  You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets

    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

    'Output the list of all users in the current 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
            

see this link


How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access
http://support.microsoft.com/?kbid=285822
0
 
LVL 21
ID: 39154496
I use this method:

Force Shut Down (Click Here)
Force Shut Down is an example database that shows how to get multiple Microsoft® Access® application front-ends on a network to automatically close. With Force Shut Down, you simply check a box bound to a field in a linked table, then each application front-end detects this change, displays a warning message to users, then automatically shuts down within 2 minutes. It's a great way to make sure that your database back-end is available for maintenance activities such as backup, or compact and repair.
0
 

Author Comment

by:Karen Schaefer
ID: 39155877
Regarding  #39154390

Returning  Schema - is there a way to modify this to  look at a particular mdb other than the current db?


Unfortuantely there isn't a frontend back end situation - Yes I know that is not the best way to do things, but that is what the customer wants.  I am forced to comply.  I just want to look at the directory/filepath and verify if there is a ldb existing for the particular mdb.  if so update the internal table with an error message.

Thanks for all the input.  I remember usins a ChkDir code in the past, could you help with the code needed to review the filepath/direcoty?

K
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39155912
if dir(<path>,"*.ldb") <>"" then
 
  msgbox "yes .ldb is present"

end if
0
 

Author Comment

by:Karen Schaefer
ID: 39155925
Regarding  #39154390

Returning  Schema - is there a way to modify this to  look at a particular mdb other than the current db?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39155935
try changing this

   Set cn = CurrentProject.Connection

with the path to the other db.
0
 

Author Comment

by:Karen Schaefer
ID: 39155966
ok Drawing a blank - what is the syntax from trimming off the last 4 characters of a string to be replaced with a actual value.
ie.  
 NFile = Right(gAPFilePath, 3) & ".ldb"  I want to strip off the mdb to replace with the ldb of the actual filepath value


         gAPFilePath = rs.Fields("apdbms_db").Value
      '   ShowUserRosterMultipleUsers (gAPFilePath)
        '
        NFile = Right(gAPFilePath, 3) & ".ldb"
       
        ' Z:\VH001\Access
         If Dir(NFile, NFile) <> "" Then
 
             MsgBox "Yes"

        End If
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39155997
NFile =replace(gAPFilePath,".mdb",."ldb")
0
 
LVL 75
ID: 39156102
"Access 2007"
Just curious, are you using the ACCDB format instead of MDB?

mx
0
 

Author Comment

by:Karen Schaefer
ID: 39156111
because these databases where inherited and the customer did not want them converted to accdb, just in case some user were still using 2003
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39156124
thanks that did the trick.  Thanks for all the input.
0
 

Author Comment

by:Karen Schaefer
ID: 39156141
could you please revisit my posting http://www.experts-exchange.com/Microsoft/Development/Q_28122214.html

Thanks Still need help
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question