update method creates phantom record

[placek]

Hi there,

I'm updating a table of mine and Access is creating a
phantom record with random values in the fields. My table
has 4 fields: lngBorrowerNumberCnt,
lngAcquisitionNumberCnt, dtmDateReserved and
dtmDateBorrowed. The table is called tblLoanRelation.

The code is:
Set recLoanRelation = dbshigham.OpenRecordset
("tblLoanRelation", dbOpenDynaset)
recLoanRelation.AddNew
recLoanRelation("lngBorrowerNumberCnt") = lngBorrowerNumber
recLoanRelation("lngAcquisitionNumberCnt") =
lngAcquisitionNumber
recLoanRelation("dtmDateBorrowed") = Date
recLoanRelation.Update

Although Access creates a new record with the specified
values, it created a second row, although i did not
specify this. It then proceeded to insert a random number
in lngAcquisitionNumberCnt, while leaving the remaining
fields null. This then generated an error as the composite
primary key fields (lngBorrowerNumberCnt and
lngAcquisitionNumberCnt) are specified as not null in the
design.

Does anyone have a clue why this is happening? I read
somewhere that when using update and addnew methods, the
underlying table must contain a unique index. Does a
composite primary key count as a unique index?

Thanks in advance
Martin

 

[Guest]

Just a strong suggestion. I'd use DAO or better yet ADO,
and pure sql to add this new record to your table. It is
faster, and more reliable,more portable if you change
Databases, and you won't have these type of issues. You'll
have to use an insert statement, and then something like

sql_str = "Insert into tablename values(x,y,z,...);"

dbs.Execute sql_str (DAO) (dbs is database object)

cn.Execute sql_str,adcmdtext (ADO) (cn is a connection string)

good luck,
Acie

 

[Marshall Barton]

I'm updating a table of mine and Access is creating a
phantom record with random values in the fields. My table
has 4 fields: lngBorrowerNumberCnt,
lngAcquisitionNumberCnt, dtmDateReserved and
dtmDateBorrowed. The table is called tblLoanRelation.

The code is:
Set recLoanRelation = dbshigham.OpenRecordset
("tblLoanRelation", dbOpenDynaset)
recLoanRelation.AddNew
recLoanRelation("lngBorrowerNumberCnt") = lngBorrowerNumber
recLoanRelation("lngAcquisitionNumberCnt") =
lngAcquisitionNumber
recLoanRelation("dtmDateBorrowed") = Date
recLoanRelation.Update

Although Access creates a new record with the specified
values, it created a second row, although i did not
specify this. It then proceeded to insert a random number
in lngAcquisitionNumberCnt, while leaving the remaining
fields null. This then generated an error as the composite
primary key fields (lngBorrowerNumberCnt and
lngAcquisitionNumberCnt) are specified as not null in the
design.

Does anyone have a clue why this is happening? I read
somewhere that when using update and addnew methods, the
underlying table must contain a unique index. Does a
composite primary key count as a unique index?

Yes, a composite key can be a unique key.

What you're seeing is completely out of bounds for the code
you've posted. Are you sure that there is nothing going on
somewhere else that can create a record? Perhaps an open
form?

The code above seems incomplete, did you close the secordset
and set the recordset variable to Nothing?

Lacking any other clues, I might begin to suspect that your
app is corrupted. Try creating a new blank mdb, setting all
the options and references. Then import everything from the
broken app into the new one. If that doesn't clear things
up, try decompiling, etc. as described at:
http://www.granite.ab.ca/access/corruptmdbs.htm

 

[placek]

Thanks, Marsh. I shall follow your advice.

-----Original Message-----



Yes, a composite key can be a unique key.

What you're seeing is completely out of bounds for the code
you've posted. Are you sure that there is nothing going on
somewhere else that can create a record? Perhaps an open
form?

The code above seems incomplete, did you close the secordset
and set the recordset variable to Nothing?

Lacking any other clues, I might begin to suspect that your
app is corrupted. Try creating a new blank mdb, setting all
the options and references. Then import everything from the
broken app into the new one. If that doesn't clear things
up, try decompiling, etc. as described at:
http://www.granite.ab.ca/access/corruptmdbs.htm