What is the Best Way to Insert a Large Nbr of Recs With No Message

[Brad]

What is the Best Way to Insert a Large Number of Records With No Messages?

I posted a question yesterday, but after reading my post, I thought that it
would be good to better explain the issue that I am running into… sorry about
that.

Here is what I am trying to do with Access 2007 VBA.

Read a sequential input file with many records

Insert these records into an Access table after doing some manipulation of
the data

Display NO messages to the end-user except at the end of the input file
(because there are many records)

Because of a Referential Integrity constraint, some record inserts will
fail. When this happens, I need to write an “error record†to another Access
table, with NO message to the end-user at the time of the insert failure AND
I need to continue processing the remaining records in the input file.

The existing programs in the system already uses ADO, so I would prefer to
stay with ADO rather than DAO, unless there is no other choice.

It seems like this should be a fairly easy thing to do. I have spent a lot
of time reading and experimenting and cannot figure out how to do this.

An example would be most appreciated.

Thanks in advance for your help.

Brad

 

[Arvin Meyer MVP]

For some of the activities you desire, the JET engine is not your ideal
database engine. Since it does not have triggers, the only way that JET can
handle failures is by using a form and a recordset to add 1 record at a time
by looping through a recordset. This is very slow on large numbers of
records.

Normally, if you do an insert query from the query interface, you will get a
message something like:

"Access can't append all the records, ... n records due to key violations,
do you want to run the query anyway"

You will not get a write of errors to another table. Even during an import,
where you can get row numbers, the only data that's used is the row number
(not the PK unless you have a contiguous autonumber) and why it won't
append.

 

[Noëlla Gabriël]

Hi,

using the command docmd.setwarnings false in combination with a good error
handling routine (you can use the Err object to create your own errors and
handle them as needed) will get you there.

 

[Tony Toews [MVP]]

using the command docmd.setwarnings false in combination with a good error
handling routine (you can use the Err object to create your own errors and
handle them as needed) will get you there.

The problem with DoCmd.RunSQL is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony

 

[Brad]

Everyone,

Thanks for your help. After doing some more reading and experimenting, here
is the "test" code that I came up with (It is a bit stripped down for this
example). This code works, but I am still not sure that it is the best way
to get the job done.
_ _

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
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 Insert ERR.Nbr=" & Err.Number & "Err.Desc=" &
Err.Description

End Sub
_ _

Thanks again for your help! If you see a flaw in this Test code, please let
me know.
Brad