ADO routine adding record rather than updating if record exists

[Corkyballs]

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

 

[Terpsfan]

If you are using DAO to add records, then you have to tell DAO you are editing a record before changing any values.
--Use this line below in WriteData.

.Edit
rsRecordSet!Date = Date
--other fields

You will have to modify WriteData to account for the fact in one case you are adding records, and in the other you are updating.

 

[Corkyballs]

Tnx Omega36

Can you be a bit more explicit please ?

Forgive my ignorance

Tnx

 

[Terpsfan]

Private Sub Writedata()
'only for editing existing records
rsRecordset.Edit
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

 

[Corkyballs]

Tnx Omega

Tried that before but got an error as follows..

"Method or Data member not found" error for recordset.edit under WriteData()!

I guess it's a reference I've not included...at the minute I've got DAO 3.6 Objects Library + other std stuff.

Any ideas ?

Tnx for your time

Rgds

corkyballs

 

[Guest_imported]

that error message means that you tried editing a column in your table which is not existing, or might have been mispelled. check all the fields within the .edit and .update statements.

 

[Corkyballs]

The spellings spot on, its the .edit bit it doesn't like. :-(

Edit doesn't appear as a valid attribute in the list that appears when I type rsRecordSet.

I've had similar problems when I haven't added a reference

Tnx

corkyballs

 

[MarkSweetland]

You are using ADO (not DAO) and Edit is not an ADO method. ADO always performs operations on the current record. Your SQL statement always returns EOF because the strComputerName is a string literal inside the statement rather than concatenating the contents of the variable to the string:

rsRecordSet.Open "SELECT * From Auditor WHERE ComputerName ='" & strComputerName & "'", cn, adOpenStatic, adLockOptimistic


Mark

 

[Corkyballs]

Thanks Mark

That seems to have done the job.

Cheers