1 Replies - 3888 Views - Last Post: 07 April 2011 - 09:31 AM Rate Topic: -----

#1 MichaelKim  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 07-April 11

VB6-ADO - addnew,update - error - record cannot be deleted or changed

Posted 07 April 2011 - 07:22 AM

Hi All!,
here's my problem.
i'm working with Visual basic 6 and microsoft Access 2003 database.
I use ADO objects for connection and manipulation of the database.
In my database there are 3 tables .2 tables(Customers and Events) are the parent tables and 1 table(Orders) is a child table.The order table has one primary key(and 2 foreign keys).
i have no problem with the 2 parent tables.
My Main concern is with the Order table.

In my VB6 program , i have a form with text boxes used for inputing and outputing data and some command butons to save record or add a new record.

In fact ,i am able to use the Addnew and update (ADO)methods to save data entered in text boxes on a VB6 form.
The saving operation(update) goes well when the referential integrity is not enforced between the 3 tables mentionned above.

THE PROBLEM is when i enforce the referential integrity between the 3 tables.IN fact, the save(update method) operation "goes well" BUT a ERROR MESSAGE is always displayed on screen.(Error -2147467259)

in Fact, the complete error message displayed is "Microsoft OLE DB Provider for ODBC Drivers reports[Microsoft][ODBC Microsoft Access Driver]. The record cannot be deleted or changed because table ’orders’ includes related record."

Could you please explain to me why i have this error message ?
What can i do so that the error message doesn't appear any more?
:confused:How could i solve this problem ? :confused:

here are some lines from my code :
Dim objADOConn As ADODB.Connection
Dim objADORst As ADODB.Recordset

...
objADORst.AddNew
...
objADORst.Fields("Order_ID") = Val(Trim$(txtOrderID.Text))
objADORst.Fields("Event_ID") = Trim$(txtEvent_ID2)
objADORst.Fields("Customer_ID") = Trim$(txtCustomerID.Text)
objADORst.Update

.....

i've tryed to search for a solution through google. some suggest to use sql insert instead of addnew and update, or remove connection options such as opendynamic,...but nothing seems to help!

Thanks in advance for taking your time to look at my problem!
have a nice day!

Is This A Good Question/Topic? 0
  • +

Replies To: VB6-ADO - addnew,update - error - record cannot be deleted or changed

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: VB6-ADO - addnew,update - error - record cannot be deleted or changed

Posted 07 April 2011 - 09:31 AM

I don't have enough information here to know exactly why you're having the problem. However, the reason that you are having it is because you are trying to change a primary key value that is set up as a foreign key in Orders (perhaps Events?). There's a setting somewhere in Access that disallows or allows "cascading updates". If you want to change the primary key in a table that has records in another table that depend on it, it follows that you would also need to change the foreign key value for all those records in that other table.

Generally, it's not a good idea at all to allow the user to mess with key values anyway. Consider using an AutoNumber field for your primary key in all of your tables.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1