Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #13571 > unrolled thread

Way to identify open processes?

Back to article view | Back to comp.databases.ms-access

Contents

  Way to identify open processes? Neil <neil@myplaceofwork.com> - 2019-04-03 06:44 -0400
    Re: Way to identify open processes? Ron Paii <ron81pai@gmail.com> - 2019-04-03 05:07 -0700
      Re: Way to identify open processes? Neil <neil@myplaceofwork.com> - 2019-04-03 10:00 -0400
        Re: Way to identify open processes? Ron Paii <ron81pai@gmail.com> - 2019-04-03 10:41 -0700
          Re: Way to identify open processes? Neil <neil@myplaceofwork.com> - 2019-04-03 15:54 -0400
            Re: Way to identify open processes? Ron Paii <ron81pai@gmail.com> - 2019-04-03 13:58 -0700

#13571 — Way to identify open processes?

I have a problem with a routine that fails with an on-line Access 
database, giving the error that the routine can't run because there is 
another process running. The problem originated after 
converting/updating the Access database from .mdb to .accdb form.

I verified that all process calls in my routines are closed and removed 
on completion.

Is there a way to identify processes that are running and accessing the 
database, thus interfering with other routines?

-- 
best regards,

Neil

[toc] | [next] | [standalone]

#13572

On Wednesday, April 3, 2019 at 5:44:46 AM UTC-5, Neil wrote:
> I have a problem with a routine that fails with an on-line Access 
> database, giving the error that the routine can't run because there is 
> another process running. The problem originated after 
> converting/updating the Access database from .mdb to .accdb form.
> 
> I verified that all process calls in my routines are closed and removed 
> on completion.
> 
> Is there a way to identify processes that are running and accessing the 
> database, thus interfering with other routines?
> 
> -- 
> best regards,
> 
> Neil

if you have Microsoft's sysinternalsSuite on your workstation, the psFile program can list all log-in Names with a file open. If the BE is on a server, you will need admin rights to run it. I send the results to a text file for review, psfile outputs to the terminal. 

open a command prompt with admin rights to the folder containing the file
move to the folder with psfile program. 
psfile "Full Path and name to the BE file" > outputfile.txt
notepad outputfile.txt

Output looks like the following

Files opened remotely on ServerName matching "Full Path and name to the BE file"

[1946157101] "Full Path and name to the BE file"
    User:   LoginNameOnServer
    Locks:  22
    Access: Read Write 
[-2013265801] "Full Path and name to the BE file"
    User:   LoginNameOnServer
    Locks:  18
    Access: Read Write 
[1342177404] "Full Path and name to the BE file"
    User:   LoginNameOnServer
    Locks:  24
    Access: Read Write

[toc] | [prev] | [next] | [standalone]

#13573

On 4/3/2019 8:07 AM, Ron Paii wrote:
> On Wednesday, April 3, 2019 at 5:44:46 AM UTC-5, Neil wrote:
>> I have a problem with a routine that fails with an on-line Access
>> database, giving the error that the routine can't run because there is
>> another process running. The problem originated after
>> converting/updating the Access database from .mdb to .accdb form.
>>
>> I verified that all process calls in my routines are closed and removed
>> on completion.
>>
>> Is there a way to identify processes that are running and accessing the
>> database, thus interfering with other routines?
>>
>> -- 
>> best regards,
>>
>> Neil
> 
> if you have Microsoft's sysinternalsSuite on your workstation, the psFile program can list all log-in Names with a file open. If the BE is on a server, you will need admin rights to run it. I send the results to a text file for review, psfile outputs to the terminal.
> 
> open a command prompt with admin rights to the folder containing the file
> move to the folder with psfile program.
> psfile "Full Path and name to the BE file" > outputfile.txt
> notepad outputfile.txt
> 
> Output looks like the following
> 
> Files opened remotely on ServerName matching "Full Path and name to the BE file"
> 
> [1946157101] "Full Path and name to the BE file"
>      User:   LoginNameOnServer
>      Locks:  22
>      Access: Read Write
> [-2013265801] "Full Path and name to the BE file" 
>      User:   LoginNameOnServer
>      Locks:  18
>      Access: Read Write
> [1342177404] "Full Path and name to the BE file"
>      User:   LoginNameOnServer
>      Locks:  24
>      Access: Read Write
> 
Thanks, this looks interesting. If the required admin rights are at the 
same level as FTP or login credentials, maybe this could work. It's 
worth looking into.

The routine in this case replaces the database, but doesn't require 
functional access to it. So, another thought I had was having a way to 
pause all processes accessing the database until the routine was 
completed, then allow those processes to resume.

Any ideas would be welcome.

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13574

This is a solution that you can try from VBA. I have't tried it in production but it seems to work and you only need access to the BE file (no admin). You will need to set a reference to "Microsoft ActiveX Data Objects 6.1 Library", push it's priority to the bottom or use DAO.Recordset when defining DAO objects in your code.

Public Sub WhoHadBeOpen(Optional ByVal fileToCheck As String = "path and filename to BE file")
    On Error GoTo errWhoHadBeOpen
    
    Dim cn As New adodb.Connection
    Dim rs As New adodb.Recordset
    
    fileToCheck = Trim$(fileToCheck)
    
    If fileToCheck = vbNullString Then
        fileToCheck = Trim$(InputBox("Enter full path and file name to a ACCDB or MDB Access database file."))
    End If
    
    If fileToCheck <> vbNullString Then
        If FileExt(fileToCheck) = "accdb" Then
            cn.Provider = "Microsoft.ACE.OLEDB.12.0"   ' accdb
        ElseIf FileExt(fileToCheck) = "mdb" Then
            cn.Provider = "Microsoft.Jet.OLEDB.4.0"    ' mdb
        Else
            Debug.Print fileToCheck & vbCrLf & "Is not an Access database file"
            fileToCheck = vbNullString
        End If
        
        If fileToCheck <> vbNullString Then
            cn.Open "Data Source=" & fileToCheck
            ' The user roster is exposed as a provider-specific schema rowset
            ' in the Jet 4 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 header
            Debug.Print rs.Fields(0).Name
        
            While Not rs.EOF
                Debug.Print Trim$(rs.Fields(0))
                rs.MoveNext
            Wend
        End If
    End If
    
doneWhoHadBeOpen:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set cn = Nothing
    Exit Sub
    
errWhoHadBeOpen:
    Debug.Print Err.Description
    Resume doneWhoHadBeOpen
End Sub

[toc] | [prev] | [next] | [standalone]

#13575

On 4/3/2019 1:41 PM, Ron Paii wrote:
> This is a solution that you can try from VBA. I have't tried it in production but it seems to work and you only need access to the BE file (no admin). You will need to set a reference to "Microsoft ActiveX Data Objects 6.1 Library", push it's priority to the bottom or use DAO.Recordset when defining DAO objects in your code.
> 
(snipped for brevity)

Thanks again! One thing that caught my eye is:
>      
>      If fileToCheck <> vbNullString Then
>          If FileExt(fileToCheck) = "accdb" Then
>              cn.Provider = "Microsoft.ACE.OLEDB.12.0"   ' accdb
>          ElseIf FileExt(fileToCheck) = "mdb" Then
>              cn.Provider = "Microsoft.Jet.OLEDB.4.0"    ' mdb
>  
This implies a change in how OLEDB versions handle the different 
database formats. I didn't see any specific reference to that when I was 
updating the format from .mdb to .accdb in any of the driver references.

I did follow recommendations to stay with the more Access-specific 
"Microsoft Access Driver" and ADODB due to there being fewer layers and 
thus faster operation than OLEDB. Indeed, everything does run quickly 
and properly except for the lock files (.laccdb). If there's a quirk in 
the way it handles .accdb and .mdb file locking, that may be the source 
of my problems.

I also downloaded the sysinternalsSuite, but for some reason none of the 
.CHM files provided search results as they usually do (I didn't run any 
.exe files to see if that made a difference). The PDF file on the page 
was also invalid, and couldn't be opened. That would make it rather 
difficult to use without buying and studying one of the recommended books.

Any insights are appreciated!

-- 
best regards,

Neil

[toc] | [prev] | [next] | [standalone]

#13576

On Wednesday, April 3, 2019 at 2:54:26 PM UTC-5, Neil wrote:
> On 4/3/2019 1:41 PM, Ron Paii wrote:
> > This is a solution that you can try from VBA. I have't tried it in production but it seems to work and you only need access to the BE file (no admin). You will need to set a reference to "Microsoft ActiveX Data Objects 6.1 Library", push it's priority to the bottom or use DAO.Recordset when defining DAO objects in your code.
> > 
> (snipped for brevity)
> 
> Thanks again! One thing that caught my eye is:
> >      
> >      If fileToCheck <> vbNullString Then
> >          If FileExt(fileToCheck) = "accdb" Then
> >              cn.Provider = "Microsoft.ACE.OLEDB.12.0"   ' accdb
> >          ElseIf FileExt(fileToCheck) = "mdb" Then
> >              cn.Provider = "Microsoft.Jet.OLEDB.4.0"    ' mdb
> >  
> This implies a change in how OLEDB versions handle the different 
> database formats. I didn't see any specific reference to that when I was 
> updating the format from .mdb to .accdb in any of the driver references.
> 
> I did follow recommendations to stay with the more Access-specific 
> "Microsoft Access Driver" and ADODB due to there being fewer layers and 
> thus faster operation than OLEDB. Indeed, everything does run quickly 
> and properly except for the lock files (.laccdb). If there's a quirk in 
> the way it handles .accdb and .mdb file locking, that may be the source 
> of my problems.
> 
> I also downloaded the sysinternalsSuite, but for some reason none of the 
> .CHM files provided search results as they usually do (I didn't run any 
> .exe files to see if that made a difference). The PDF file on the page 
> was also invalid, and couldn't be opened. That would make it rather 
> difficult to use without buying and studying one of the recommended books.
> 
> Any insights are appreciated!
> 
> -- 
> best regards,
> 
> Neil

Are the CHM files on a mapped drive? Windows will not display CHM content from anything but a local drive.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web