Corkyballs
MIS
I am a newbie to DAO (I'm an NT Net Admin by trade) and have created a VB6 .exe that collects various hardware, software and environment details when run from the logon script.
I originally had the details appending to a text file on the server and decided to write them to an Access db instead - my first attempts have suceeded in writing new records to the database but not altering a record if a record for the pc in question already exists. I am using the computername field of the DB as a key and trying to match it to the strComputerName variable captured from Windows (The name captured has been TRIMed of all nulls, spaces, etc.). My code follows ,don't laugh ;-)
-----------------------------------------------
Private Sub OpenAuditorDataBase()
Set cn = New ADODB.Connection
cn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\my documents\access\db1.mdb"
Set rsRecordSet = New ADODB.Recordset
REM Use SQL to return a recordset containing 1 item if it exists. Auditor is a single table in db1.mdb.
rsRecordSet.Open "SELECT * From Auditor WHERE ComputerName = 'strComputerName'", cn, adOpenStatic, adLockOptimistic
If rsRecordSet.EOF Then
rsRecordSet.AddNew
Writedata
Else:
Writedata
End If
rsRecordSet.Close
cn.Close
Set rsRecordSet = Nothing
Set cn = Nothing
End Sub
-----------------------------------------------
Private Sub Writedata()
rsRecordSet!Date = Date
rsRecordSet!Time = Time
rsRecordSet!computername = strComputerName
rsRecordSet!email = strEmailAddress
rsRecordSet!loggedonas = strLoggedIn
rsRecordSet!workgroup = strWorkgroup
rsRecordSet!domain = strDomain
rsRecordSet!DNS = strDNSServer
rsRecordSet!Mac = strMACAddress
rsRecordSet!OS = strOS
rsRecordSet!OSDescription = strOS_Version
rsRecordSet!OSVersionNo = strOS_Version_Number
rsRecordSet!ProductID = strProduct_ID
rsRecordSet!ProductKey = strProduct_Key
rsRecordSet!RegOrg = strRegistered_Org
rsRecordSet!RegOwner = strRegistered_Owner
rsRecordSet!IEVersion = strIEVersion
rsRecordSet!Sophosversion = strSophos_Version
rsRecordSet!memory = strMemory
rsRecordSet!video = strVideo
rsRecordSet!HDSerial = strHardDriveSerialNUmber
rsRecordSet!Volume = Left$(strHardDriveName, 16)
rsRecordSet!VolumeType = Left$(strHardDriveFAT, 8)
rsRecordSet!HDSize = strHardDriveSize
rsRecordSet!HDFree = strHardDriveFree
rsRecordSet!Word = strWordVersion
rsRecordSet!Excel = strExcelVersion
rsRecordSet!Powerpoint = strPowerpointVersion
rsRecordSet!Access = strAccessVersion
rsRecordSet!Outlook = strOutlookVersion
rsRecordSet!Visio = strVisioVersion
rsRecordSet!Query = strMSQueryVersion
rsRecordSet.Update
End Sub
I originally had the details appending to a text file on the server and decided to write them to an Access db instead - my first attempts have suceeded in writing new records to the database but not altering a record if a record for the pc in question already exists. I am using the computername field of the DB as a key and trying to match it to the strComputerName variable captured from Windows (The name captured has been TRIMed of all nulls, spaces, etc.). My code follows ,don't laugh ;-)
-----------------------------------------------
Private Sub OpenAuditorDataBase()
Set cn = New ADODB.Connection
cn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\my documents\access\db1.mdb"
Set rsRecordSet = New ADODB.Recordset
REM Use SQL to return a recordset containing 1 item if it exists. Auditor is a single table in db1.mdb.
rsRecordSet.Open "SELECT * From Auditor WHERE ComputerName = 'strComputerName'", cn, adOpenStatic, adLockOptimistic
If rsRecordSet.EOF Then
rsRecordSet.AddNew
Writedata
Else:
Writedata
End If
rsRecordSet.Close
cn.Close
Set rsRecordSet = Nothing
Set cn = Nothing
End Sub
-----------------------------------------------
Private Sub Writedata()
rsRecordSet!Date = Date
rsRecordSet!Time = Time
rsRecordSet!computername = strComputerName
rsRecordSet!email = strEmailAddress
rsRecordSet!loggedonas = strLoggedIn
rsRecordSet!workgroup = strWorkgroup
rsRecordSet!domain = strDomain
rsRecordSet!DNS = strDNSServer
rsRecordSet!Mac = strMACAddress
rsRecordSet!OS = strOS
rsRecordSet!OSDescription = strOS_Version
rsRecordSet!OSVersionNo = strOS_Version_Number
rsRecordSet!ProductID = strProduct_ID
rsRecordSet!ProductKey = strProduct_Key
rsRecordSet!RegOrg = strRegistered_Org
rsRecordSet!RegOwner = strRegistered_Owner
rsRecordSet!IEVersion = strIEVersion
rsRecordSet!Sophosversion = strSophos_Version
rsRecordSet!memory = strMemory
rsRecordSet!video = strVideo
rsRecordSet!HDSerial = strHardDriveSerialNUmber
rsRecordSet!Volume = Left$(strHardDriveName, 16)
rsRecordSet!VolumeType = Left$(strHardDriveFAT, 8)
rsRecordSet!HDSize = strHardDriveSize
rsRecordSet!HDFree = strHardDriveFree
rsRecordSet!Word = strWordVersion
rsRecordSet!Excel = strExcelVersion
rsRecordSet!Powerpoint = strPowerpointVersion
rsRecordSet!Access = strAccessVersion
rsRecordSet!Outlook = strOutlookVersion
rsRecordSet!Visio = strVisioVersion
rsRecordSet!Query = strMSQueryVersion
rsRecordSet.Update
End Sub