rst.update: run-time error -2147217887(80040e21), ODBC-call failed

[Jiwei06xie]

Dear Expert,

Im using SQL server (2000) as my backend and MS Access (2000) as F.E..

I use two data entry froms to add new records, form1 to enter details of delivery (tbldelivery) and form2 to enter details of delivery items (tbldeliveryitem). Previously everything is fine, but about 2 weeks ago an error message keeps popping out "run-time error -2147217887(80040e21), ODBC -call failed" after I add one delivery record and tried to add the detail of delivery items (one-to-many).

The situation is:
(1) I got no problem with entering new delivery record,
(2) But everytime I have one new delivery record added by form1, I have to restart the database to add an delivery item record, otherwise, the above error message occurs at rst.update
(3) I can manually input a new delivery item record.

The problematic part of the code as follwoing:

/ Function Save()


Dim rst As ADODB.Recordset
Dim x As Integer
Dim rst3 As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
Set rst3 = New ADODB.Recordset

rst3.ActiveConnection = CurrentProject.Connection

rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open "select * from dbo_tblDelivery_items"

rst.MoveLast
x = rst!DeliveryItem_ID

rst.AddNew

rst!DeliveryItem_ID = x + 1
rst!Delivery_No = Delivery_No
rst!Product_ID = Product_ID
rst!Short_name = Short_name
rst![Type/Model] = [Type/Model]
rst!SerialNumber = SerialNumber
rst!RadioModuleSerialNo = RadioModuleSerialNo
rst!Radio_Power = Radio_Power
rst!Solar_or_mains = Solar_or_mains
rst!Box_SerialNo = Box_SerialNo
rst!Firmware_Version = Firmware_Version
rst!Hardware_Version = Hardware_Version
rst!Manufacture_Date = Manufacture_Date
rst!Comments = Comments
rst!Actions = Actions
rst!Action_date = Action_date

rst.Update
rst.Close
Set rst = Nothing

End Function /

Please help me in this regard..Please..

Many thanks.

kindest regards, Jay

[ADezii]

Dear Expert,

Im using SQL server (2000) as my backend and MS Access (2000) as F.E..

I use two data entry froms to add new records, form1 to enter details of delivery (tbldelivery) and form2 to enter details of delivery items (tbldeliveryitem). Previously everything is fine, but about 2 weeks ago an error message keeps popping out "run-time error -2147217887(80040e21), ODBC -call failed" after I add one delivery record and tried to add the detail of delivery items (one-to-many).

The situation is:
(1) I got no problem with entering new delivery record,
(2) But everytime I have one new delivery record added by form1, I have to restart the database to add an delivery item record, otherwise, the above error message occurs at rst.update
(3) I can manually input a new delivery item record.

The problematic part of the code as follwoing:

/ Function Save()


Dim rst As ADODB.Recordset
Dim x As Integer
Dim rst3 As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
Set rst3 = New ADODB.Recordset

rst3.ActiveConnection = CurrentProject.Connection

rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open "select * from dbo_tblDelivery_items"

rst.MoveLast
x = rst!DeliveryItem_ID

rst.AddNew

rst!DeliveryItem_ID = x + 1
rst!Delivery_No = Delivery_No
rst!Product_ID = Product_ID
rst!Short_name = Short_name
rst![Type/Model] = [Type/Model]
rst!SerialNumber = SerialNumber
rst!RadioModuleSerialNo = RadioModuleSerialNo
rst!Radio_Power = Radio_Power
rst!Solar_or_mains = Solar_or_mains
rst!Box_SerialNo = Box_SerialNo
rst!Firmware_Version = Firmware_Version
rst!Hardware_Version = Hardware_Version
rst!Manufacture_Date = Manufacture_Date
rst!Comments = Comments
rst!Actions = Actions
rst!Action_date = Action_date

rst.Update
rst.Close
Set rst = Nothing

End Function /

Please help me in this regard..Please..

Many thanks.

kindest regards, Jay

1. What function does the rst3 Recordset serve in the above code?
2. Is Form1 closed when you are adding delivery items to tblDeliveryItem via Form2? If it isn't try closing it, then add the related Records.

[Jiwei06xie]

1. What function does the rst3 Recordset serve in the above code?
2. Is Form1 closed when you are adding delivery items to tblDeliveryItem via Form2? If it isn't try closing it, then add the related Records.

Dear ADezii

Thank you very much for your reply.

(1) I am sorry for confusing you by the rst3. It is used to copy field "deliveryNo" in tblDelivery into the second tblDeliveryItem, which is the link between two tables. Some key fields in the two tables are:

tblDelivery

delivery_ID (Autonumber)
delivery_No (text)
deliveryDestination_ID (number)
DeliveryDestination (text)
....

tbleDeliveryItems

deliveryItem_ID (autonumber)
delivery_NO(text)
Product_ID
ProductDescription

....

(2) I am quite sure form1 is closed as I used a command button to link the two forms. When that command button is clicked, a messsage asks "do you want to close this form to add a delivery item?" if yes, close the form1 and open form2. If no then just close form1.

anything wrong with closing recordset or ODBC link?

Many thanks.

Jay

[ADezii]

Dear ADezii

Thank you very much for your reply.

(1) I am sorry for confusing you by the rst3. It is used to copy field "deliveryNo" in tblDelivery into the second tblDeliveryItem, which is the link between two tables. Some key fields in the two tables are:

tblDelivery

delivery_ID (Autonumber)
delivery_No (text)
deliveryDestination_ID (number)
DeliveryDestination (text)
....

tbleDeliveryItems

deliveryItem_ID (autonumber)
delivery_NO(text)
Product_ID
ProductDescription

....

(2) I am quite sure form1 is closed as I used a command button to link the two forms. When that command button is clicked, a messsage asks "do you want to close this form to add a delivery item?" if yes, close the form1 and open form2. If no then just close form1.

anything wrong with closing recordset or ODBC link?

Many thanks.

Jay

Hello Jay, prior to closing Form1, and after Updating the Recordset that adds a Record to tblDelivery, Requery the Rercordset as in:

<Your Recordset Name>.Requery

It's a long shot, but it may be a case where SQL Server is not properly Updating tblDelivery, and when you attempt to Add a Record to the Child Table (tblDeliveryItems), it sees it as adding an Orphan Record. Like I said, a long shot.

[Jiwei06xie]

Hello Jay, prior to closing Form1, and after Updating the Recordset that adds a Record to tblDelivery, Requery the Rercordset as in:

<Your Recordset Name>.Requery

It's a long shot, but it may be a case where SQL Server is not properly Updating tblDelivery, and when you attempt to Add a Record to the Child Table (tblDeliveryItems), it sees it as adding an Orphan Record. Like I said, a long shot.

Hi ADezii,

I have added the code something like:

rst.update
rst.requery
rst.close

but I got another similar error message after waiting for about 1 minutes, "run-time error '-2147467259(80004005)' ODBC-call failed". Without that code, no problem in adding a new delivery record.

Any further help? Many thanks.

Regards, Jay

[Jiwei06xie]

Hi ADezii,

I have added the code something like:

rst.update
rst.requery
rst.close

but I got another similar error message after waiting for about 1 minutes, "run-time error '-2147467259(80004005)' ODBC-call failed". Without that code, no problem in adding a new delivery record.

Any further help? Many thanks.

Regards, Jay

I have found the answer. Nothing wrong with my code, it is the primary keys of the two tables in SQL. By creating two similar tables, I have managed to get everything back to normal. Many thanks anyway.

Jay