Solved Problem Inserting Data into Table

[jdraw]

I have a routine to create a table (LKX), the table has 1 field(firstName), that field is a unique index. I want LKX to have only DISTINCT values.
When I try to loop through other tables(A,B,C) and Insert the selected field(firstName) values from A,B and C to Table LKX, the firstnames from A are appended. The values from B and C are not. There are 27 unique firstnames in A, 45 firstnames in B (6 are duplicates) so 39 new values are expected--giving 27+39 =66 values in LKX. Table C is a copy of A with 1 changed firstname, so I expect 1 new value from C. So eexpecting 66 +1 = 67 records in LKX final count.
BUT there are only 27 records in LKX???
I do have code to trap error 3022 (attempt to add duplicate) and Resume Next.(bypassing the error).

Any ideas, suggestions? Attempting to help poster here
Thanks in advance.

 

[MajP]

Without seeing the code, my first guess is you may be focusing on the wrong error. It might not be a duplicate index error. Maybe in B, and C the problem is not a duplicate index but some other problem like a data type mismatch.

 

[The_Doc_Man]

Are you using the equivalent of CurrentDB.Execute of an SQL INSERT INTO query using dbFailOnError?

If you are, and if I recall correctly, that option causes a rollback on failure, and your description suggests that an error will occur. So the issue might be that you get the insert but then get a rollback. Can you tell if the trap actually trips? Because I recall there is an issue when you use "Fail on Error" option.

 

[jdraw]

Thanks for responding Doc.
Yes re currentdb.execute and dbFailOnError.
I laced it with DoEvents thinking there may be a timing issue.
I have adjusted to use PK rather than Unique index, but same result.

Here is the code

' ----------------------------------------------------------------
' Procedure Name: CreateLKTables
' Purpose: Create the LK_ tables and populate with data from the Org tables.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 17-Mar-21
' ----------------------------------------------------------------
Sub CreateLKTables()
10        On Error GoTo CreateLKTables_Error
          Dim db As DAO.Database
          Dim rsFields As DAO.Recordset
          Dim rsFldUsage As DAO.Recordset
          Dim CreateTableSql As String, CreateTableSqlNumeric As String
          Dim CreateIndexSQL  As String, tmpT As String, TmpUQ As String
          Dim PKSQL As String
          PKSQL = " CONSTRAINT XXX PRIMARY KEY );"
20        CreateTableSql = " create table LK_XXX ( XXX text(50)  CONSTRAINT XXX PRIMARY KEY );"
30        CreateTableSqlNumeric = " create table LK_XXX ( XXX Number   CONSTRAINT XXX PRIMARY KEY);"
40     '   CreateIndexSQL = "CREATE UNIQUE INDEX QQZ " _
              & "ON  LK_XXX " & "(  XXX    );"

50        Set db = CurrentDb
60        Set rsFields = db.OpenRecordset("FieldnamesQ", dbReadOnly)
70        Do While Not rsFields.EOF
80            If rsFields!Data_type = "text" Then
90                tmpT = Replace(CreateTableSql, "XXX", rsFields!field_name)
100           Else
110               tmpT = Replace(CreateTableSqlNumeric, "XXX", rsFields!field_name)
120           End If
130           Debug.Print tmpT
140           db.Execute tmpT, dbFailOnError
'150         '  TmpUQ = Replace(CreateIndexSQL, "XXX", rsFields!field_name)
'160         '  Debug.Print TmpUQ
'170         '  db.Execute TmpUQ, dbFailOnError
180           rsFields.MoveNext
DoEvents
190       Loop
200       MsgBox "LK_ tables created with PKs", vbOKOnly   'unique indexes", vbOKOnly
210       tmpT = ""
          'now fill the tables
220       Set rsFldUsage = db.OpenRecordset("FieldNameUsageQ", dbReadOnly)
230       Do While Not rsFldUsage.EOF
240       DoEvents
250              tmpT = ""
260           tmpT = "INSERT into LK_" & rsFldUsage!field_name _
                  & "   SELECT  DISTINCT " & rsFldUsage!field_name & " from " & rsFldUsage!table_name  ' _
                '  & " WHERE  " & rsFldUsage!field_name & " Is Not Null;"
270           Debug.Print tmpT
DoEvents
280           db.Execute tmpT, dbFailOnError
290           DoEvents
300       rsFldUsage.MoveNext
310       Loop
320       RefreshDatabaseWindow
330       MsgBox "LK_ Tables filled with data", vbOKOnly
340       On Error GoTo 0
CreateLKTables_Exit:
350       Exit Sub

CreateLKTables_Error:
360       If Err.Number > 0 Then
370            Debug.Print "*ERROR** " & rsFldUsage!table_name & "  " & Err.Number & vbCrLf & "  " & Err.Description
380           Resume Next
390       Else
400           MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateLKTables, line " & Erl & "."
410           GoTo CreateLKTables_Exit
420       End If

End Sub

Here is part of my debug.print "log"
.....
create table LK_Email ( Email text(50) CONSTRAINT Email PRIMARY KEY );
create table LK_FirstName ( FirstName text(50) CONSTRAINT FirstName PRIMARY KEY );
create table LK_Gender ( Gender text(50) CONSTRAINT Gender PRIMARY KEY );
....
INSERT into LK_FirstName SELECT DISTINCT FirstName from OrgA
INSERT into LK_FirstName SELECT DISTINCT FirstName from OrgB
*ERROR** OrgB 3022
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
INSERT into LK_FirstName SELECT DISTINCT FirstName from OrgD
*ERROR** OrgD 3022
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

 

[Minty]

as @The_Doc_Man has suggested you would definitely need to remove the dbFailOnError as that will roll back any changes.

 

[moke123]

NSERT into LK_FirstName SELECT DISTINCT FirstName from OrgD
*ERROR** OrgD 3022

not sure it's relevant but is there an OrgD

 

[jdraw]

Yes, I wrote the issue as table A, B and C. But it's really A, B and D that are relevant here.

Just tried again and removed the dbFailOnError and I now get 67 records in LK_FirstNames.
I did not know that DBFailOnError would effectively back out "good data". Seems it is acting with Transaction logic. I'll try to register that in some longer term memory.
Thanks all for looking into this.!!

 

[jdraw]

I just found this thread via Google. Wish I had known it existed. Seems there is some confusion with details and context.
From my issue in this thread, removing the dbFailOnError resolved the issue. It was rolling back and inserts from tables B and D.

I am attaching the accdb used for testing in the zip. Add/remove the dbFailOnError to see the result.
Thanks again!

 

[The_Doc_Man]

Glad I could point the way.