Re: Deleting a record with ADO .... HELP
I find using the adodb.command easier than the adodb.recordset to delete
When tables are linked, delete the dependent records 1st.
For example you have 2 tables, one a customer table and one a invoice table.
database integrity forces you to delete the invoices related to a customer
before you can delete the customer.
Find the Unique Identifier for that customer and delete the invoices related
to that customer then delete the customer.
you may want to prompt the user before deleting the records like: "deleting
this customer will delete 5 invoices, do you wish to continue?"
ado command to delete a record:
Dim adodel As ADODB.Command
Set adodel = New ADODB.Command
.ActiveConnection = activeconnection
.CommandType = adCmdText
.CommandText = "Delete from table where uniqueid = " & idstr
.CommandTimeout = 30
Set adodel = nothing
Chemical Information Services, Inc.
Mailing Address: P.O. Box 743512 Dallas, TX U.S.A 75374
Corporate Offices: 9550 Forest Lane, Suite 321 Dallas, TX U.S.A. 75243
Tel: 214-349-6200 Ext. 106 Fax: 214-349-6286
Narayan <firstname.lastname@example.org> wrote in message news:email@example.com...
> The moment you say the table is related the related table becomes foreign
> key table right. So the only way is remove the relationship which
> you cant afford. So the only way out is remove all dependent rows in the
> related table and then remove the master record in the Primary Key table.
> Thats all I can think. If you find any other feasible solution pl. let me
> know at firstname.lastname@example.org. I am sorry this is the only solution I know.
> >Hi Narayan,
> >Thank you for the response.
> >This particular table that I'm trying to delete a record from doesn't
> >any foreign keys. But it's primary key is related to 2 other tables as a
> >foreign key.
> >Any thoughts as to how to get around this issue?
> >"Narayan" <email@example.com> wrote:
> >>Hi Brian,
> >>Pl. check your MS-Access 97 table for any Primary Foreign Key
> >>between tables. There may be referential integrity which means if you
> >>record that corresponds to one of the records in your master table and
> >>try to delete that record in master table you will get this error. There
> >>is no problem in your code. So delete all the records corresponding to
> >>master record in the related table and then delete the record in the
> >>table or remove the relationships between the tables in your Access
> >>This should solve your problem. I hope this helps you.
> >>"Brian Aubert" <firstname.lastname@example.org> wrote:
> >>>I'm new to ADO and I'm having a problem deleting a record with ADO. I'm
> >>>VB6.0 and Access97. My code is as follows.
> >>>Set rstData = New AdoDb.Recordset
> >>>rstData.CursorType = adOpenKeyset
> >>>rstData.LockType = adLockOptimistic
> >>>When VB executes the rstData.Delete line of code I get a runtime error,
> >>>"the record cannot be deleted or changed because table 'Task' includes
> >>>Please help, this is kicking my b***!