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
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
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.
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.
>> 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):
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Etsherman, Thank you for the solution and stepping me through the process.
If so, you need logic to locate existing records, then update, else addnew.