VBS 脚本，该脚本使用当前 PC 的序列号、用户名和时间修改 accdb 数据库表

Question

我希望为最终用户的PC制作一个登录脚本，该脚本将在accdb文件的表（名为硬件）中执行以下操作：

1. 如果表不包含其序列号 #，请使用 PC 的序列号 #、当前日期和时间以及用户的登录名创建新记录。
2. 如果表已有与其序列号匹配的记录，请更新现有日期和时间以及登录名。

我

能够使用以下代码完成这项工作，但唯一的问题是我必须在脚本的这一行中手动设置 serial #

strSearchCriteria = "serial = '9BFD9A5'"

这是整个事情：

DateInfo = DateInfo & Now
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = \serverInventory.accdb"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
    adOpenStatic, adLockOptimistic
objRecordset.MoveFirst
strSearchCriteria = "serial = '9BFD9A5'"
objRecordSet.Find strSearchCriteria
If objRecordset.EOF Then 
    Wscript.Echo "Record cannot be found."
Set colBios = GetObject("winmgmts:").ExecQuery _
    ("Select * from Win32_Bios")
For Each objBios in colBios
    objRecordset.AddNew
    objRecordset("Serial") = objBios.SerialNumber
Next
'
Set colNetworkLoginProfile = GetObject("winmgmts:").ExecQuery _
    ("Select * from Win32_NetworkLoginProfile")
For Each ObjNetworkLoginProfile in colNetworkLoginProfile
    objRecordset("logonid") = objNetworkLoginProfile.Caption
Next
    objRecordset("logondate") = DateInfo
    objRecordset.Update

Else
    Wscript.Echo "Record found."
    Set colBios = GetObject("winmgmts:").ExecQuery _
    ("Select * from Win32_Bios")
For Each objBios in colBios
    objRecordset("Serial") = objBios.SerialNumber
Next
'
Set colNetworkLoginProfile = GetObject("winmgmts:").ExecQuery _
    ("Select * from Win32_NetworkLoginProfile")
For Each ObjNetworkLoginProfile in colNetworkLoginProfile
    objRecordset("logonid") = objNetworkLoginProfile.Caption
Next
    objRecordset("logondate") = DateInfo
    objRecordset.Update
End If
objRecordset.Close
objConnection.Close

我想将其设置为它在数据库中搜索的序列号#是运行脚本的计算机的序列号的位置。看起来很容易，但我是一个新手。

谢谢

Answer 1

重要的是在筛选记录集之前查询序列号。然后，可以将其用作筛选记录集的条件。我修改了您的代码并突出显示了重要的更改。我也愿意稍微重组代码以消除重复项。

DateInfo = DateInfo & Now
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = \serverInventory.accdb"
SerialNo = ""
LogonId = ""
' Get the SerialNo first
Set colBios = GetObject("winmgmts:").ExecQuery _
    ("Select * from Win32_Bios")
For Each objBios in colBios
    SerialNo = objBios.SerialNumber
Next
Set colNetworkLoginProfile = GetObject("winmgmts:").ExecQuery _
    ("Select * from Win32_NetworkLoginProfile")
For Each ObjNetworkLoginProfile in colNetworkLoginProfile
    LogonId = objNetworkLoginProfile.Caption
Next
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
    adOpenStatic, adLockOptimistic
objRecordset.MoveFirst
' Now use the Serial to filter the recordset
strSearchCriteria = "serial = '" & SerialNo &  "'"
objRecordSet.Find strSearchCriteria
If objRecordset.EOF Then 
    Wscript.Echo "Record cannot be found."
    objRecordset.AddNew
    objRecordset("Serial") = SerialNo
Else
    Wscript.Echo "Record found."
    ' Serial does not need to be set, we just found it.
End If
objRecordset("logonid") = LogonId
objRecordset("logondate") = DateInfo
objRecordset.Update
objRecordset.Close
objConnection.Close