Find users logged into Access database using Powershell

Question

I have a multi-user Access database which is compacted daily using a powershell script. This script can't compact the database if there are still users logged in. If there is a user logged in, I would like be able to idenify the user(s) who forgot to logout of the database and remind them to log out at the end of the day.

If I were to write this in VB it would work like this:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
cn.open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=...mdb")
Set rs = cn.OpenSchema(adSchemaProviderSpecific, ,"{947bb102-5d43-11d1-bdbf-00c04fb92675}")

Then loop through the recordset and get the information I want.

What I would like to do is translate this to PowerShell so it can run with the compact script. I have tried the following:

$objCon = New-Object -ComObject ADODB.Connection
$objRs = New-Object -ComObject ADODB.Recordset
$objCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=...mdb")
$objRs= $objCon.OpenSchema([ADODB.SchemaEnum]::adSchemaProviderSpecific,$null,'{947bb102-5d43-11d1-bdbf-00c04fb92675}')
$objRs.MoveFirst()

Then loop through the recordset and find the information I need.

The ps code errors out on the OpenSchema line with:

Exception calling "OpenSchema" with "3" argument(s): "Object or provider is not capable of performing requested operation." At FindUsers.ps1:8 char:27 + $objRs= $objCon.OpenSchema <<<< ([ADODB.SchemaEnum]::adSchemaProviderSpecific,$null,'{947bb102-5d43-11d1-bdbf-00c04fb92675}') + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

The script won't execute if I remove the middle $null variable or replace it with '', and I don't know if the command is translated from VB to powershell correctly. I've searched on Google and SO and haven't found any solutions. What do I need to do to use the OpenSchema command?

If I do the same idea in VBScript the openschema line looks like: Set rs = cn.OpenSchema(-1, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}") and works fine. — KFleschner
– KFleschner, Commented Nov 20, 2012 at 22:25

Chad Miller · Accepted Answer · 2012-11-21 00:20:04Z

1

This works for me using the newer ACE drivers included with Office 2007 and higher:

$filepath = "C:\Users\u00\Documents\Northwind.mdb"
[guid]$guid = '947bb102-5d43-11d1-bdbf-00c04fb92675'

$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Persist Security Info=False;"
$conn = new-object System.Data.OleDb.OleDbConnection($ConnectionString)
$conn.open()
$conn.GetOleDbSchemaTable($guid,$null) 
$conn.close()

COMPUTER_NAME                    LOGIN_NAME 
-------------                    ---------- 
Z002                             Admin

answered Nov 21, 2012 at 0:20

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

KFleschner Over a year ago

It's connecting, but as wrtten it's only returning the first user. I'll play around with it some more.

Chad Miller Over a year ago

I can't repo that issue. If open the MS Access file and then run the Powershell code I get back two rows:

KFleschner Over a year ago

I'm returning the GetOleDBSchema by changing the line to $rt = $conn.GetOleDbSchema($guid,$null), and then going through a foreach($dr in $dt){$u=$u + $dr.COMPUTER_NAME + "rn"}. I'm just getting the first computer name on Write-Hose $u. When I look at $u there are funny box characters I can't remove using .Trim().

KFleschner Over a year ago

I'm seeing everything except suspectstate if I pipe $conn.GetOe...| Out-GridView. Am I looping through the rows correctly if I want to make a list of users connected?

KFleschner Over a year ago

Found issue. I'm using PowerGUI, and the Console doesn't like something in the output. Using the acutal ps it looks fine

|

Collectives™ on Stack Overflow

Find users logged into Access database using Powershell

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related