VB Forum / Database Access / April 2007

I have an insert statement in a function that I call repeatedly.

Let's say I call it 80 times and then there are 57 new records in my
table.

Presumably, the other 23 errored out for duplicate or other reasons.

How can I get the error message when an insert statement has errors?

Here is my code:

Public Function Run_Insert(sqlInsert As String) As ADODB.Recordset
On Error GoTo ExecuteError
Dim recSet As ADODB.Recordset
   
                               
   Get_Connection
   Set recSet = dbConn.Execute(sqlInsert)
   Set Run_Insert = recSet
   Exit Function
   
ExecuteError:

   strError = "Error in sub Utilities.Run_Insert" & vbCrLf &
Err.Number & _
               vbCrLf & Err.Description & vbCrLf & sqlInsert
                   
   Utilities.ErrorToFile (strError)
   
   Exit Function
End Function

Private Sub Get_Connection()
On Error GoTo ExecuteError
   
   'dbConn is a global variable.

   'Set the database
   db_file = "C:\Current Database\Test.mdb"
   
   'Open a connection to the database
   If dbConn Is Nothing Then
       Set dbConn = New ADODB.Connection
   
       dbConn.ConnectionString = _
           "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & db_file & ";" & _
           "Persist Security Info=False"
       
       dbConn.Open (dbConn.ConnectionString)
       
   End If
   
   Exit Sub
   
ExecuteError:

   strError = "Error in Utilities.Get_Connection" & vbCrLf &
Err.Number &  vbCrLf & Err.Description
                   
   Utilities.ErrorToFile (strError)
   
   Exit Sub
   
End Sub

Thanks for your help.