Problems inserting records

[Brad]

I am working on an Access 2007 application. We have a customer table which
has a relationship with our State table.

Originally, I was using a simple SQL Insert like this (stripped down for
this example)

Mysql = “Insert into TblOrders (Order_ID, Order_State) Values
(“My_Order_IDâ€, “My_Order_Stateâ€)â€
DoCmd.RunSQL mysql

This method works fine, except if I have
DoCmd.SetWarnings False
I receive no error messages.

If I have
DoCmd.SetWarnings True
then I receive a message for every insert (we will have many good inserts
and only a small number of errors, so this isn’t going to work very well).
Perhaps there is a way to only see messages if there is a problem???

Also, as I understand it, the “Insert Into†method does not support “ON
ERROR†for better error trapping.
_ _ _

I then switched horses and changed the code to use a RecordSet to add the
records.
The code looks like this…

My_Recordset.AddNew
My_Recordset.Fields(“Order_IDâ€) = My_Order_ID
My_Recordset.Fields(“Order_Stateâ€) = My_Order_State
My_Recordset.Update

I like this method better because I can do better error trapping.

HOWEVER -
I now am receiving the following error for “some†of the records that we are
trying to add.

“You cannot add or change a record because a related record is required in
table 'tblStates'.â€

We have defined a relationship between the Order table and the States table.
The catch is that this error is being generated even when we have a
corresponding record in our state table. For example order #1 has a state
code of “MN†and it is accepted. Order #2 also has a state code on “MN†and
it is being rejected.

I have double checked the data and I have done a “Compact and Repair†on the
database.

I am really puzzled.

I am relatively new to using Access. What is the best way to insert
records? It feels like the “Record Set†method is a bit unpredictable, but
perhaps I am missing something.

Thanks for your help.

Brad

 

[John Spencer]

I prefer using something like the following snippet

Dim MyDb as DAO.Database

On error goto Proc_Error:
Set MyDb = CurrentDB()

Mysql = "Insert into TblOrders (Order_ID, Order_State)" & _
" Values (“"" & My_Order_ID & """, """ & My_Order_State & """)"

MyDb.Execute MySql, dbFailOnError

Exit Sub (or Exit Function)

Proc_Error:
MsgBox Err.Number & " : " & Err.Description

End Sub (or END Function)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Brad]

John,

Thanks for your help. About an hour after I posted my question, I believe
that I found the source of the problem, but I am not sure how to resolve it.

When I use a “Record Set†to add records, I use the “On Error†command to
trap the error. In the “Error Handler†logic I use a MsgBox to display info
about the error.

After an error is detected, I then want to continue processing the remaining
records in the input file. It appears that the “On Error†is still set for
the remaining records, thus making it appear that even valid “record addsâ€
are failing.

Is there a way to shut off the “On Error†setting after an error has been
handled and there is a need to process the next record? (The application
that I am working on is reading an input file and trying to insert records
into an Access table).

Thanks, I appreciate your assistance,
Brad

 

[Philip Herlihy]

John,

Thanks for your help. About an hour after I posted my question, I believe
that I found the source of the problem, but I am not sure how to resolve it.

When I use a “Record Set†to add records, I use the “On Error†command to
trap the error. In the “Error Handler†logic I use a MsgBox to display info
about the error.

After an error is detected, I then want to continue processing the remaining
records in the input file. It appears that the “On Error†is still set for
the remaining records, thus making it appear that even valid “record addsâ€
are failing.

Is there a way to shut off the “On Error†setting after an error has been
handled and there is a need to process the next record? (The application
that I am working on is reading an input file and trying to insert records
into an Access table).

Thanks, I appreciate your assistance,
Brad

The Err object, which can give you useful information from the
Err.Description property, also has a "Clear" method, so calling
Err.Clear will reset the error status.

It's always worth trying to detect an error as early as possible in the
overall process. A truism, but one that bears repeating.

Phil, London

 

[Brad]

Phil,

Thanks for your help, I really appreciate it.

I have read about Err.Clear, but I cannot get it to work. I wonder if I am
running into some confusion because of ADO and DAO.

The existing system uses ADO. I am fairly new to Access and I am still
trying to understand DAO vs ADO.

Could you post an example of how to do an Err.Clear with ADO?

Thanks!!!

Brad

 

[Philip Herlihy]

Phil,

Thanks for your help, I really appreciate it.

I have read about Err.Clear, but I cannot get it to work. I wonder if I am
running into some confusion because of ADO and DAO.

The existing system uses ADO. I am fairly new to Access and I am still
trying to understand DAO vs ADO.

Could you post an example of how to do an Err.Clear with ADO?

Thanks!!!

Brad

The real problem for me with some of the enquiries here is the time they
can take (given the length of my "main" ToDo list!). It's often
tempting to wade in and make some superficial comment when I've not
given the original posting the time it ideally deserves, especially when
the posting is a complex one. Alternatively there's often a follow-up,
where although I'd like to assist, I simply can't justify the time to
give a properly considered (and tested) answer. This is one such situation.

This page: http://allenbrowne.com/ser-23a.html
... gives some good pointers (especially the nine numbered lines of code)
on how to use Error handling in VBA (with DAO, not ADO). The Err object
exists only in VBA, not in Macros, and is independent of individual
object libraries like ADO, DAO and whatever, although those libraries
may interact with Err under the covers (setting its values). Err has
two methods: Err.Raise and Err.Clear, and a number of properties. For
what it's worth I'd be a bit suspicious if someone new to Err was using
the Raise and Clear methods, as (my) gut feeling suggests that the
person is trying to handle a very complex set of conditions and there
may be a simpler solution if you stand back. As I suggested earlier,
it's always a good idea to detect exceptions as early in the chain as
possible (so input validation is such a good idea). If you're taking
potentially dirty data and throwing it at the database and then trying
to bat back the errors you may have an octopus to wrestle with. Can you
clean your data before you attempt to insert?

Obviously it's quicker and more efficient to append a body of records in
one operation, but if you have problems with some records it might be
worth wrapping the append operation in a function which can test the
Error and return a status code, enabling you to manage the problem
record before the others whizz past?

Phil
(Offline now for a couple of days, as I have a database of my own
needing some TLC! There are other commentators here who are likely to
have better ideas anyway!)

 

[KenSheridan via AccessMonster.com]

The procedure below should handle it. As written it lists the errors to the
debug window, but you'd might want to write them to a separate log table.
Popping up a message box for each error is another option, but that would
interrupt the process each time until the user closes the message box.

The procedure would be called by looping through whatever the source of the
data to be inserted is (a table imported from Excel or a text file for
instance) and calling the function at each iteration of the loop, passing the
Order_Id and State values into it, e.g.

Dim strOrderID As String
Dim strState As String

<Do until reach end of source>
strOrderID = <get order Id from somewhere>
strState = <get state name from somewhere>

InsertOrder strOrderID, strState

<move to next row in source >
Loop

Here's the procedure:

Public Sub InsertOrder(strOrderID As String, strState As String)

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = _
"INSERT INTO tblOrders " & _
"(Order_Id,Order_State) " & _
"VALUES(""" & strOrderID & """,""" & _
strState & """)"

cmd.CommandText = strSQL
On Error Resume Next
cmd.Execute
If Err.Number <> 0 Then
Debug.Print "Error inserting OrderId " & strOrderID & "; State " &
strState
Debug.Print "........" & Err.Description
End If

End Sub

This assumes Order_Id and State are both text data types as shown in your
example.

Ken Sheridan
Stafford, England

 

[Brad]

Ken,

Thanks for your assistance. I did quite a bit of reading and experimenting
over the weekend. Below is a stripped down version of the test code that I
came up with. It gets the job done, but I am still not 100% sure that it is
the best way to do it. In the "real" system that I am working on there will
be many many fields involved (the sample only has one). Because of the large
number of fields, the "Record Set" method seems a little more understandable
where the field values are set - one per line. The “Insert Into†method
works also, but because of the large number of field names, the syntax of the
field name and field values gets a little messy.

Thanks again for your help. Please let me know what you think of this
sample code.

Brad
- - - - - - -
Sub INSERT_ORDER()
On Error GoTo ERROR_TRAP

Dim My_DB_Connection_1 As ADODB.Connection
Set My_DB_Connection_1 = CurrentProject.Connection

Dim My_Record_Set_1 As ADODB.Recordset
Set My_Record_Set_1 = New ADODB.Recordset

My_Record_Set_1.Open _
"TBLORDERS", _
My_DB_Connection_1, _
adOpenDynamic, _
adLockOptimistic

My_Record_Set_1.AddNew
My_Record_Set_1![Order_ID] = my_order_id ' Field from "calling" sub
' there will be many many more set statements for other fields in the real
system
My_Record_Set_1.Update

My_Record_Set_1.Close
Set My_Record_Set_1 = Nothing
Set My_DB_Connection_1 = Nothing

MsgBox "**** Good Insert"

Exit Sub

ERROR_TRAP:

Msgbox "---- BAD ERR.Nbr=" & Err.Number & "Err.Desc=" & Err.Description

End Sub
--- - - - -

 

[KenSheridan via AccessMonster.com]

You are establishing a recordset object each time you insert a row. It would
be more efficient to establish the recordset only once and then insert all
the rows before closing it. To do this declare the object variables at
module level, then open the recordset in a procedure in the module. In the
same procedure next loop through the source rows, calling another procedure
in which the recorset's AddNew and Update methods are called to insert the
current row. Finally after exiting the loop close the recordset.

Ken Sheridan
Stafford, England

Ken,

Thanks for your assistance. I did quite a bit of reading and experimenting
over the weekend. Below is a stripped down version of the test code that I
came up with. It gets the job done, but I am still not 100% sure that it is
the best way to do it. In the "real" system that I am working on there will
be many many fields involved (the sample only has one). Because of the large
number of fields, the "Record Set" method seems a little more understandable
where the field values are set - one per line. The “Insert Into†method
works also, but because of the large number of field names, the syntax of the
field name and field values gets a little messy.

Thanks again for your help. Please let me know what you think of this
sample code.

Brad
- - - - - - -
Sub INSERT_ORDER()
On Error GoTo ERROR_TRAP

Dim My_DB_Connection_1 As ADODB.Connection
Set My_DB_Connection_1 = CurrentProject.Connection

Dim My_Record_Set_1 As ADODB.Recordset
Set My_Record_Set_1 = New ADODB.Recordset

My_Record_Set_1.Open _
"TBLORDERS", _
My_DB_Connection_1, _
adOpenDynamic, _
adLockOptimistic

My_Record_Set_1.AddNew
My_Record_Set_1![Order_ID] = my_order_id ' Field from "calling" sub
' there will be many many more set statements for other fields in the real
system
My_Record_Set_1.Update

My_Record_Set_1.Close
Set My_Record_Set_1 = Nothing
Set My_DB_Connection_1 = Nothing

MsgBox "**** Good Insert"

Exit Sub

ERROR_TRAP:

Msgbox "---- BAD ERR.Nbr=" & Err.Number & "Err.Desc=" & Err.Description

End Sub
--- - - - -

The procedure below should handle it. As written it lists the errors to the
debug window, but you'd might want to write them to a separate log table.

[quoted text clipped - 112 lines]

 

[Brad]

Ken,

Thanks for your help and advice. I really appreciate it.

I am slowly but surely learning Access. The ADO RecordSet concept etc seems
a bit complicated. In addition, I seem to be seeing different methods in the
books and online posts that I have read. I sure don't want to get going down
the wrong path this early in the game.

Thanks again!

Brad

You are establishing a recordset object each time you insert a row. It would
be more efficient to establish the recordset only once and then insert all
the rows before closing it. To do this declare the object variables at
module level, then open the recordset in a procedure in the module. In the
same procedure next loop through the source rows, calling another procedure
in which the recorset's AddNew and Update methods are called to insert the
current row. Finally after exiting the loop close the recordset.

Ken Sheridan
Stafford, England

Ken,

Thanks for your assistance. I did quite a bit of reading and experimenting
over the weekend. Below is a stripped down version of the test code that I
came up with. It gets the job done, but I am still not 100% sure that it is
the best way to do it. In the "real" system that I am working on there will
be many many fields involved (the sample only has one). Because of the large
number of fields, the "Record Set" method seems a little more understandable
where the field values are set - one per line. The “Insert Into†method
works also, but because of the large number of field names, the syntax of the
field name and field values gets a little messy.

Thanks again for your help. Please let me know what you think of this
sample code.

Brad
- - - - - - -
Sub INSERT_ORDER()
On Error GoTo ERROR_TRAP

Dim My_DB_Connection_1 As ADODB.Connection
Set My_DB_Connection_1 = CurrentProject.Connection

Dim My_Record_Set_1 As ADODB.Recordset
Set My_Record_Set_1 = New ADODB.Recordset

My_Record_Set_1.Open _
"TBLORDERS", _
My_DB_Connection_1, _
adOpenDynamic, _
adLockOptimistic

My_Record_Set_1.AddNew
My_Record_Set_1![Order_ID] = my_order_id ' Field from "calling" sub
' there will be many many more set statements for other fields in the real
system
My_Record_Set_1.Update

My_Record_Set_1.Close
Set My_Record_Set_1 = Nothing
Set My_DB_Connection_1 = Nothing

MsgBox "**** Good Insert"

Exit Sub

ERROR_TRAP:

Msgbox "---- BAD ERR.Nbr=" & Err.Number & "Err.Desc=" & Err.Description

End Sub
--- - - - -

The procedure below should handle it. As written it lists the errors to the
debug window, but you'd might want to write them to a separate log table.

[quoted text clipped - 112 lines]

Brad