查找使用Powershell登录到Access数据库的用户 [英] Find users logged into Access database using Powershell

问题描述

我有一个多用户Access数据库，每天使用Powershell脚本对其进行压缩.如果仍有用户登录，此脚本将无法压缩数据库.如果有用户登录，我希望能够找出忘记退出数据库的用户，并提醒他们在以下时间注销一天结束.

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.

如果我要用VB编写此代码，它将像这样工作:

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.

我想做的就是将其翻译为PowerShell，以便它可以与紧凑型脚本一起运行.我尝试了以下方法:

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.

ps代码在OpenSchema行上显示以下错误:

The ps code errors out on the OpenSchema line with:

使用"3"个参数调用"OpenSchema"的异常:对象或提供者无法执行所请求的操作." 在FindUsers.ps1:8 char:27 + $ objRs = $ objCon.OpenSchema<<<< ([ADODB.SchemaEnum] :: adSchemaProviderSpecific，$ null，'{947bb102-5d43-11d1-bdbf-00c04fb92675}') + CategoryInfo:未指定:(:) []，MethodInvocationException + FullyQualifiedErrorId:DotNetMethodException

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

如果删除中间的$ null变量或将其替换为''，脚本将无法执行，并且我不知道命令是否已从VB正确转换为powershell.我已经在Google和SO上进行搜索，但没有找到任何解决方案.使用OpenSchema命令需要做什么?

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?

推荐答案

这适用于我使用Office 2007及更高版本随附的较新ACE驱动程序的情况.

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   

这篇关于查找使用Powershell登录到Access数据库的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！