Updating existing Record with a ID (AutoNumber) and ComputerName columns having Primary key

I have a Table named "Computer" with an ID (AutoNumber) Column that has a Primary Key and a ComputerName Column (Data type "TEXT(30)") which I have also set with a Primary key. On the Table design, I setup Required = Yes, Allow Zero Length = No and Indexed = (Yes)No Duplicates. I've done this so that the Computer name will always be unique but I would still like to update any of the other fields within that Table. If I set Indexed option to (Yes)Duplicates OK, well, then it adds the record as a new record and of course as I understand it, you don't want that otherwise it's not a unique record. Can someone tell me how to design the table so that it can update the other fields data?
Thanks Experts,
Wallace
'Adding records to MS-Access Inventory Database

Const adOpenStatic = 3
Const adLockOptimistic= 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
	"Provider = Microsoft.Jet.OLEDB.4.0; " & _
		"Data Source = c:\databases\Inventory.mdb"
		
objRecordSet.Open "SELECT * FROM Computer" , _
	objConnection, adOpenStatic, adLockOptimistic
	
objRecordSet.AddNew
objRecordSet("ComputerName") = "Famm"
objRecordSet("Department") = "Human Resources"
objRecordSet("OSName") = "Microsoft Windows XP Professional"
objRecordSet("OSVersion") = "5.1 (2600)"
objRecordSet("OSServicePack") = "Service Pack 4"
objRecordSet("OSManufacturer") = "Microsoft Corporation"
objRecordSet.Update

Open in new window

wally_davisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard DanekeTrainerCommented:
Will your souce contain some new records and some updates?
If so, you need logic to locate existing records, then update, else addnew.
0
Richard DanekeTrainerCommented:
Where are you running this?
If I were in Access, I would link to the source data and use two action queries - one to add and one to update.  Place the two queries into a macro and run the macro from a command button off the switchboard or some other form.
0
hieloCommented:
>> but I would still like to update any of the other fields within that Table
If you need to do an UPDATE, then you cannot use addNew. The name says it all - it adds a New Record.

If you want to update a SPECIFIC record (say id=3):
Set objConnection = CreateObject("ADODB.Connection")


objConnection.Open _
        "Provider = Microsoft.Jet.OLEDB.4.0; " & _
                "Data Source = c:\databases\Inventory.mdb"
                
objConnection.Execute "UPDATE Table SET Computername='some name', Department=' some dept' WHERE id=3"

Open in new window

0
Eric ShermanAccountant/DeveloperCommented:
Try this ...

'Adding records to MS-Access Inventory Database
 
Const adOpenStatic = 3
Const adLockOptimistic= 3
 
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
 
objConnection.Open _
        "Provider = Microsoft.Jet.OLEDB.4.0; " & _
                "Data Source = c:\databases\Inventory.mdb"
                 
objRecordSet.Open "SELECT * FROM Computer" , _
        objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst
objRecordSet.Find "[ComputerName]='Famm'"
If objRecordSet.EOF Then  'ComputerName does not exist      
  objRecordSet.AddNew
  objRecordSet("ComputerName") = "Famm"
  objRecordSet("Department") = "Human Resources"
  objRecordSet("OSName") = "Microsoft Windows XP Professional"
  objRecordSet("OSVersion") = "5.1 (2600)"
  objRecordSet("OSServicePack") = "Service Pack 4"
  objRecordSet("OSManufacturer") = "Microsoft Corporation"
  objRecordSet.Update
Else   'ComputerName already exist
  objRecordSet("Department") = "Human Resources"
  objRecordSet("OSName") = "Microsoft Windows XP Professional"
  objRecordSet("OSVersion") = "5.1 (2600)"
  objRecordSet("OSServicePack") = "Service Pack 4"
  objRecordSet("OSManufacturer") = "Microsoft Corporation"
  objRecordSet.Update
End If

Open in New WindowSelect All

ET
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wally_davisAuthor Commented:
Etsherman, Thank you for the solution and stepping me through the process.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.