Re: A problem with updating access entries

timbo wrote on Thu, 20 Oct 2005 15:06:02 -0700:

> I'm having trouble handling the even of an entry already existing in the
> database. I know the problem has to do with the .AddNew method adding a
> key that already exists, but i'm not sure how to go about fixing it. If
> the primary key already exists, i'd like to find a way to remove the
> entire entry and replace it with new data. Here is the code i'm working
> with to place the data.

Do a query to find an existing record for the key - if you find no records,
use AddNew, otherwise just update the existing record.

As you haven't specified what the key is, I'll assume it's Asset. If so, try this:

objRecord.CursorType = CursorType
objRecord.LockType = LockType
objRecord.Source = "select * from OSInfo where Asset = '" & strComputer &
"'"
objRecord.ActiveConnection = objConnection
objRecord.Open

If objRecord.EOF Then
'no existing record for strComputer, add new record
objRecord.AddNew
objRecord("Asset") = strComputer
End If

objRecord("OS") = strOS
objRecord("Service Pack") = strSPV
objRecord("Version") = strVN
objRecord("Serial Number") = strSN
objRecord("Windows Dir") = strWD
objRecord("Install Date") = strID
objRecord("Registered User") = strRU
objRecord("Organization") = strO
objRecord("Last Boot Time") = strLBT
objRecord.Update
objRecord.Close


This will add a new record only if there is no Asset value matching
strComputer, if there is then all the other fields will get updated with the
new data. To improve this I would build a parameterised query for the lookup
and use a Command object, this works around the possible issues of SQL
injection vulnerabilities via the strComputer value.

Dan


.