No Primary Key error

We have created a script for v14 using the database functionality now available in eggPlant. We are simply reading contents from the db and assigning them to a variable, then writing a new value to one of the fields.

The db is in Access and it DOES have a primary key set to the first column.

We are able to read from the database and display the contents correctly in the Log display using the following code:

//Setting the taxNum variable
set taxNum to "000112222"
//Setting up the DB
set myDB to (type:"odbc",DSN:"eggPlantDSN",user:"",password:"")
//Puts table into infoTable variable
put table "CreatNewAccount" of myDB into infoTable
//Finds the table record that matches the variable taxNum
set clientInfo to the record of infoTable where TaxID is taxNum
//Shows the clientInfo record
log clientInfo

But when we attempt to write to the database using this command (which would appear immediately after the code above), we receive the attached error:

//Puts date into the Birthdate field of the client record
put "1/1/1966" into clientInfo's Birthdate

I am also attaching a pic of the db in design mode showing the primary key set to the 'ID' Field Name.

Comments

What does the RecordInfo property of the table show? Try adding this at the end of your first script:

log infoTable's recordInfo

Please post the result here, but my guess is that none of the columns will show any key information. If that's the case then apparently the ODBC interface to Access doesn't provide that information and we'll need to investigate further to see if there's a way to get that.

Yes, we would expect the ID column to show up as (KeyType:"Primary", Name:"ID", Type:"integer") if it's the primary key. For some reason the ODBC database connection isn't giving us that information.

So your only option for updating the database in that case is to (carefully!) use the "delete record" command to delete the old record and then an "add record" command to add the updated record back into the table. Those commands can be used on a table without a primary key.

We changed our script to the following: it reads the record to a variable, removes the record, adds the new info to the record, and then adds the record back to the DB.

set taxNum to "000112222" -- Setting the taxNum variable
set myDB to (type:"odbc",DSN:"eggPlantDSN",user:"",password:"") -- Setting up the DB
put table "CreatNewAccount" of myDB into infoTable -- Puts table into infoTable variable
set clientInfo to the record of infoTable where TaxID is taxNum -- Finds the record that matches taxNum
delete the record of infoTable where TaxID is taxNum -- Deletes the record from the table
log clientInfo -- Shows the clientInfo record
try -- The Try/Catch keeps the No Primary Key error from happening
set clientInfo's Birthdate to "12/11/1966" -- Puts the date into the Birthdate field of the record
catch failedUpdateExceptions -- Catches any exceptions generated
end try
add record clientInfo to infoTable -- Adds the record back into the infoTable
log clientInfo -- Shows the new clientInfo record

For future reference there are a couple of other ways you can avoid the exception. One is to copy the record into another variable, which will no longer have a connection to the database, before updating any values, like this:

put clientInfo into clientInfoCopy -- make a copy not connected to the db
set clientInfoCopy's Birthdate to "12/11/1966"
add record clientInfoCopy to infoTable

Another approach would be to set the table's autoSaveUpdates property to false:

set infoTable's autoSaveUpdates to false

or create the table that way in the first place:

put table "CreatNewAccount" of myDB with (autoSaveUpdates:NO) into infoTable

Then you can update properties in a record without SenseTalk automatically trying (and failing) to save them.