UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Vba Error 3022 But Not All Unique Data Has Been Appended, Access 2016    
 
   
Zaddicus
post May 21 2019, 11:05 AM
Post#1



Posts: 122
Joined: 3-April 19
From: Cardiff


Hello all again.

So again this is relating to an import helper module @Cheekybuddah created for me to go with my existing VBA import code - Original thread is here (Sorry it's a long thread)

And after the amendments / data clean-up I ran the process again and it has returned Error 3022 - "The changes you requested to the table were not successful"
After a quick google I have come to the conclusion that this is most likely because it has ran through the loop and returned to the start and believes all the data is there and therefore cannot add duplicates. (This is not the case, more on that soon)

I was considering building a case in the error handler for that error to just proceed straight to the next step however my CEO is concerned that there may be flags missing and doesn't want me to do this until we can be 100% sure they are all there. (GDPR related stuff)

As a result I created a 'not-in' query and it has returned 56766 results (Not bad considering there is a total of 443503 flags to add so it's done 93%).
SQL
SELECT tbl_Contacts.ContactID, tbl_ContactFlags.ContactID, tbl_Contacts.Suppress
FROM tbl_Contacts LEFT JOIN tbl_ContactFlags ON tbl_Contacts.ContactID = tbl_ContactFlags.ContactID
WHERE (((tbl_ContactFlags.ContactID) Is Null) AND ((tbl_Contacts.Suppress)=False));

This has caused concern as all contacts that exist (And not perm suppressed) should have a minimum of 1 flag (FlagID = 11) so the query should be returning 0. And I can't think of a reason why access may not be saving the data or is unable to save the data.

So the full code is in this thread but the error occurs on the ' myR2.Update' - this means that it's kind of correct as there are no more contacts to add/update however there are still flags that need adding in the connected table.

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post May 21 2019, 11:21 AM
Post#2


UtterAccess VIP
Posts: 11,292
Joined: 6-December 03
From: Telegraph Hill


>> the query should be returning 0 <<

Do you mean 0 records?

How many records are being returned?

--------------------


Regards,

David Marten
Go to the top of the page
 
gemmathehusky
post May 21 2019, 11:27 AM
Post#3


UtterAccess VIP
Posts: 4,679
Joined: 5-June 07
From: UK


error 3022 implies it cannot insert all records, because you are getting duplicates on a unique key.

try joining the existing data with the new data, on all parts of the unique key, to see if there are any duplicates
also try a totals query on the new data only, to see if that data has duplicates on the same fields. Set the key fields to "group by". Add any other field, and set this to "count", and put >1 in the criteria. This will show just the duplicates within the new data.


--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Zaddicus
post May 21 2019, 01:22 PM
Post#4



Posts: 122
Joined: 3-April 19
From: Cardiff


QUOTE
Do you mean 0 records?

How many records are being returned?

My fatal flaw of not being able to explain what the outcome should be. Yes, the 'not-in' query should return 0 as every contact should have flag 11 applied - at the moment I have 56766 records returning (approximately 7% of the total number expected)

QUOTE
error 3022 implies it cannot insert all records, because you are getting duplicates on a unique key


Google also suggested this but the key is an autonumber and not even touched with the VB code - the only thing it should upend is contactID / FlagID - the unique key (ConFID) should just do what it done with the other 200K records and increment when a new record is added.

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post May 21 2019, 01:28 PM
Post#5


UtterAccess VIP
Posts: 11,292
Joined: 6-December 03
From: Telegraph Hill


How many records are returned from the following query:
CODE
SELECT
  c.ContactID
FROM tbl_Contacts c
WHERE c.Suppress = False
  AND c.ID NOT IN (
    SELECT
      f.ContactID
    FROM tbl_ContactFlags f
  )
;

Just want to check you still get 56766.

--------------------


Regards,

David Marten
Go to the top of the page
 
Zaddicus
post May 21 2019, 01:52 PM
Post#6



Posts: 122
Joined: 3-April 19
From: Cardiff


QUOTE
How many records are returned from the following query


I'll have to put this on hold as I've left the office for the day (finally) and although I do have the database on a remote server the amount of initial data being imported (250k rows + 500k min flags) caused a connection to time out (I think - the errors wouldn't even write to the log due to no connection) so I had to use the backup "offline" to run this initial process and alas that's stored on my workstation.

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
Zaddicus
post May 22 2019, 03:08 AM
Post#7



Posts: 122
Joined: 3-April 19
From: Cardiff


Good morning cheekybuddah,

I tried the SQL query you suggested (Changed AND c.ID NOT IN to AND c.ContactID NOT IN) and Access just kept crashing. (I think it's currently still attempting to run, I've got it in the background) Sadly I have not been able to get results due to this.

On a side note I've never heard my workstation sound like an aircraft engine before, so that's pretty cool!

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
gemmathehusky
post May 22 2019, 03:49 AM
Post#8


UtterAccess VIP
Posts: 4,679
Joined: 5-June 07
From: UK


error 3022 is an issue with duplicate keys.

so - are you saying there is no unique key in this table other than the autonumber? That sounds very unlikely to me.

what fields do you have in the table? can you give us the query that is reporting the problem?


--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Zaddicus
post May 22 2019, 04:23 AM
Post#9



Posts: 122
Joined: 3-April 19
From: Cardiff


So the table has the following fields:
CODE
+------------+--------------+---------+--------------------+
| Field Name |     Type     | Indexed | Duplicates Allowed |
+------------+--------------+---------+--------------------+
| ConFID     | Autonumber   | PK      | No                 |
| ContactID  | Long Integer | FK      | Yes                |
| FlagID     | Long Integer | FK      | Yes                |
+------------+--------------+---------+--------------------+


The problem is occurring on line 63 (myR2.Update)

What should happen - Check if contact already exists in table, if yes update the null fields where applicable. If no create new and input all applicable data. Do this until end of all records then display the "complete" message and end sub

What is happening - It's getting to the end of the records and then returning error 3022 however it still has not appended all applicable data to the right tables (Contact table is correct, contactflags table is missing approximately 60K records)

Regardless of the issue where not all data is appended to the contactflags table error 3022 shouldn't happen - If the unique email exists the code will update that record, if it doesn't it creates new. It shouldn't even be attempting to create a new record with an existing email (Row 32 - myR2.FindFirst ("Email = '" & myR![Email] & "'"))




FULL CODE:
CODE
Private Sub RuncontactImport_Click()
On Error GoTo ErrorHandler

Dim ConID As Long
Dim db As DAO.Database
Dim arrFlags As Variant, i As Long
Dim myR As Recordset
Dim myR2 As Recordset

Set db = CurrentDb
Set myR = db.OpenRecordset("tbl_STG_ContactImport", dbOpenDynaset)
Set myR2 = db.OpenRecordset("tbl_Contacts", dbOpenDynaset)

If TempVars!AUSL = 1 Then
    GoTo AccessGranted
ElseIf TempVars!AUSL = 2 Then
    GoTo AccessGranted
ElseIf TempVars!AUSL = 10 Then
    GoTo AccessGranted
    
Else:
    MsgBox prompt:="You do not have the sufficient security level to access this, if you believe this to be an error please contact your system administrator.", buttons:=vbInformation, Title:="Insufficient Permissions"
    GoTo Exit_RuncontactImport

End If

AccessGranted:

If MsgBox("You are about to create new/merge existing contacts, this could take some time! Do you wish to continue?", vbYesNo, Title:="Run Update") = vbNo Then Exit Sub

Do Until myR.EOF = True
    myR2.FindFirst ("Email = '" & myR![Email] & "'")
    If myR2.NoMatch = True Then
      myR2.AddNew
      myR2![Email] = myR![Email]
      myR2![FirstName] = myR![FirstName]
      myR2![LastName] = myR![LastName]
      myR2![Position] = myR![Position]
      myR2![Company] = myR![Company]
      myR2![Industry] = myR![Industry]
      myR2![Size] = myR![Size]
      myR2![Website] = myR![Website]
      myR2![Location] = myR![Location]
      myR2![OfficeNumber] = myR![OfficeNumber]
      myR2![MobileNumber] = myR![MobileNumber]
      myR2![Source] = myR![Source]
      myR2![Suppress] = myR![Suppress]
    Else
      myR2.Edit
      myR2![FirstName] = Nz(myR2![FirstName], myR![FirstName])
      myR2![LastName] = Nz(myR2![LastName], myR![LastName])
      myR2![Position] = Nz(myR2![Position], myR![Position])
      myR2![Company] = Nz(myR2![Company], myR![Company])
      myR2![Industry] = Nz(myR2![Industry], myR![Industry])
      myR2![Size] = Nz(myR2![Size], myR![Size])
      myR2![Website] = Nz(myR2![Website], myR![Website])
      myR2![Location] = Nz(myR2![Location], myR![Location])
      myR2![OfficeNumber] = Nz(myR2![OfficeNumber], myR![OfficeNumber])
      myR2![MobileNumber] = Nz(myR2![MobileNumber], myR![MobileNumber])
      myR2![Source] = Nz(myR2![Source], myR![Source])
      myR2![Suppress] = IIf(myR2![Suppress] = False, myR![Suppress], True)
    End If
    myR2.Update
    ConID = myR2("ContactID")
      arrFlags = Array( _
      "CFO-DEL", _
      "CFO-SPON", _
      "DP-DEL", _
      "DP-SPON", _
      "HR-DEL", _
      "HR-SPON", _
      "CIO-DEL", _
      "CIO-SPON", _
      "CMO-DEL", _
      "CMO-SPON", _
      "CISO-DEL", _
      "CISO-SPON", _
      "CPO-DEL", _
      "CPO-SPON", _
      "NIS" _
    )
' ...
    
    For i = 0 To UBound(arrFlags)
      Call SetConFlag(ConID, CStr(arrFlags(i)), myR(CStr(arrFlags(i))), db)   ' coerce the array member to a string
    Next i
    myR.MoveNext
Loop
myR2.Close
myR.Close
Set myR2 = Nothing
Set myR = Nothing
Set db = CurrentDb

DoCmd.OpenQuery "del_ContactImport"

Set db = Nothing

Me.Refresh

MsgBox prompt:="Merge Complete - Staging Table Cleared", buttons:=vbInformation, Title:="Merge Complete"

Exit_RuncontactImport:
Exit Sub

ErrorHandler:
        MsgBox prompt:="E-Link encountered an error when processing the last action. E-Link has cancelled the last action and the error has been logged with the system administrator", buttons:=vbInformation, Title:="Database Process Error"
        Call logAutoErrors(Err.Number, Err.Description, "RuncontactImport()")
        Resume Exit_RuncontactImport

End Sub

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post May 22 2019, 04:56 AM
Post#10


UtterAccess VIP
Posts: 11,292
Joined: 6-December 03
From: Telegraph Hill


>> Changed AND c.ID NOT IN to AND c.ContactID NOT IN <<
You know I like to check that you are paying attention!! wary.gif

NOT IN queries are notoriously slow - is it still running?

If so, perhaps cancel it and try:
CODE
SELECT
  c.ContactID
FROM (
  SELECT
    ContactID
  FROM tbl_Contacts
  WHERE Suppress = FALSE
) c
LEFT JOIN (
  SELECT DISTINCT
    ContactID
  FROM tbl_ContactFlags
) f
       ON c.ContactID = f.ContactID
WHERE f.ContactID IS NULL
;

--------------------


Regards,

David Marten
Go to the top of the page
 
gemmathehusky
post May 22 2019, 04:59 AM
Post#11


UtterAccess VIP
Posts: 4,679
Joined: 5-June 07
From: UK


CODE
    If myR2.NoMatch = True Then
      myR2.AddNew
      myR2![Email] = myR![Email]
      myR2![FirstName] = myR![FirstName]
      myR2![LastName] = myR![LastName]
      myR2![Position] = myR![Position]
      myR2![Company] = myR![Company]
      myR2![Industry] = myR![Industry]
      myR2![Size] = myR![Size]
      myR2![Website] = myR![Website]
      myR2![Location] = myR![Location]
      myR2![OfficeNumber] = myR![OfficeNumber]
      myR2![MobileNumber] = myR![MobileNumber]
      myR2![Source] = myR![Source]
      myR2![Suppress] = myR![Suppress]



but you have all these fields that are being pooulated, but which aren't included in the table spec you have just given.
It isn't the autonumber - there must be another unique index involving a combination of some of the above fields.


Just a thought. Do you have a unique index on the Email itself. If you have a null, and then try to set it to a value that is already in the table it will fail with error 3022.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Zaddicus
post May 22 2019, 05:01 AM
Post#12



Posts: 122
Joined: 3-April 19
From: Cardiff


QUOTE
NOT IN queries are notoriously slow - is it still running?


Yes - my PC fan is having a heart attack (Even though access is only using 25% of the processing power and 24MB of the available 4GB RAM) So I ended the task after an hour of nothing but "running query"

Using the new query below it returns 56766 rows of data - matching the initial not-in query.

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
Zaddicus
post May 22 2019, 05:13 AM
Post#13



Posts: 122
Joined: 3-April 19
From: Cardiff


QUOTE
but you have all these fields that are being pooulated, but which aren't included in the table spec you have just given


Sorry the VBA code actually updates 2 tables - that part of the code refers to 'tbl_Contacts' where all the data has been added/updated correctly.
The tbl_contacts has a design structure like below:
CODE
+--------------------+------------+---------------------+----------+------------------------+
|     Field Name     |    Type    |       Indexed       | Required | Allow Zero Length/Null |
+--------------------+------------+---------------------+----------+------------------------+
| ContactID          | AutoNumber | PK                  | Yes      | No                     |
| Email              | Short Text | Yes - No Duplicates | Yes      | No                     |
| FirstName          | Short Text | No                  | No       | Yes                    |
| LastName           | Short Text | No                  | No       | Yes                    |
| Position           | Short Text | No                  | No       | Yes                    |
| Company            | Short Text | No                  | No       | Yes                    |
| Industry           | Short Text | No                  | No       | Yes                    |
| Size               | Short Text | No                  | No       | Yes                    |
| Website            | Short Text | No                  | No       | Yes                    |
| Location           | Short Text | No                  | No       | Yes                    |
| OfficeNumber       | Short Text | No                  | No       | Yes                    |
| MobileNumber       | Short Text | No                  | No       | Yes                    |
| TwitterHandle      | Short Text | No                  | No       | Yes                    |
| LinkedIn           | Short Text | No                  | No       | Yes                    |
| PAName             | Short Text | No                  | No       | Yes                    |
| PAEmail            | Short Text | No                  | No       | Yes                    |
| PAPhone            | Short Text | No                  | No       | Yes                    |
| Source             | Short Text | No                  | No       | Yes                    |
| Suppress           | Yes/No     | No                  | No       | Yes                    |
| TempSupp           | Yes/No     | No                  | No       | Yes                    |
| FailedVerification | Short Text | No                  | No       | Yes                    |
| Notes              | Short Text | No                  | No       | Yes                    |
| ImportDate         | Short Text | No                  | No       | Yes                    |
+--------------------+------------+---------------------+----------+------------------------+


Although there is an index on the email there are no null values in this field for any records (or duplicates) same for the document being imported - all records have at least something in the email even if it is just (and this is an actual example)
"�.🌐🚀@gecko.co.UK" <---- no doubt this will cause a host of other issues in the future

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
gemmathehusky
post May 22 2019, 05:21 AM
Post#14


UtterAccess VIP
Posts: 4,679
Joined: 5-June 07
From: UK


Well I am pretty sure that somehow you are trying to add a duplicate Email, and the error 3022 is because of that.
It clearly can't be the autonumber PK. (well occasionally they go wrong, but I doubt it in this case)

I would change your error message for error 3022 to show you the data so you can check it when it happens.


[EDIT]
[not sure technically about the graphical characters - maybe you could get a false duplicate depending on how the ascii codes are managed.]

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Zaddicus
post May 22 2019, 05:31 AM
Post#15



Posts: 122
Joined: 3-April 19
From: Cardiff


QUOTE
Well I am pretty sure that somehow you are trying to add a duplicate Email, and the error 3022 is because of that.


I see where you are coming from with this however the VBA findfirst if/else clause should completely prevent Error 3022
CODE
Do Until myR.EOF = True
    myR2.FindFirst ("Email = '" & myR![Email] & "'")
    If myR2.NoMatch = True Then
      myR2.AddNew
      myR2![Email] = myR![Email]
      myR2![FirstName] = myR![FirstName]
      myR2![LastName] = myR![LastName]
      myR2![Position] = myR![Position]
      myR2![Company] = myR![Company]
      myR2![Industry] = myR![Industry]
      myR2![Size] = myR![Size]
      myR2![Website] = myR![Website]
      myR2![Location] = myR![Location]
      myR2![OfficeNumber] = myR![OfficeNumber]
      myR2![MobileNumber] = myR![MobileNumber]
      myR2![Source] = myR![Source]
      myR2![Suppress] = myR![Suppress]
    Else
      myR2.Edit
      myR2![FirstName] = Nz(myR2![FirstName], myR![FirstName])
      myR2![LastName] = Nz(myR2![LastName], myR![LastName])
      myR2![Position] = Nz(myR2![Position], myR![Position])
      myR2![Company] = Nz(myR2![Company], myR![Company])
      myR2![Industry] = Nz(myR2![Industry], myR![Industry])
      myR2![Size] = Nz(myR2![Size], myR![Size])
      myR2![Website] = Nz(myR2![Website], myR![Website])
      myR2![Location] = Nz(myR2![Location], myR![Location])
      myR2![OfficeNumber] = Nz(myR2![OfficeNumber], myR![OfficeNumber])
      myR2![MobileNumber] = Nz(myR2![MobileNumber], myR![MobileNumber])
      myR2![Source] = Nz(myR2![Source], myR![Source])
      myR2![Suppress] = IIf(myR2![Suppress] = False, myR![Suppress], True)
    End If
    myR2.Update

If the email doesn't exist, add all records, if it does update that record. (and this is what it does as I've just ran the first 15K rows of data through 5 times all existing emails no error) And as you can see if it finds that the email does exist it shouldn't attempt to add it or update it as it's not even coded to touch it in the else statement.

Edit: Checked the 3 source files I've imported through this method so far, none contain a null value for email. Also double-checked the existing data in the contacts table and can confirm there are also no null values.
This post has been edited by Zaddicus: May 22 2019, 05:52 AM

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
gemmathehusky
post May 22 2019, 06:05 AM
Post#16


UtterAccess VIP
Posts: 4,679
Joined: 5-June 07
From: UK


Ok Perhaps the 3022 error you described at the top was caused by a processing loop issue of some sort, that just hasn't repeated.
If you do get a 3022 error again, I am sure it will be to do with the email address.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Zaddicus
post May 23 2019, 02:30 AM
Post#17



Posts: 122
Joined: 3-April 19
From: Cardiff


Well I left the process running over night for the whole data and returned to work this morning to see Error 3022 again.

I'm going to try and go through the multiple documents and clean up the emails with random symbols, not sure if that is causing the issue but no harm in trying.

In other news the 'not-in' query is returning 0 results now so at least that seems to be working (I'm not going to question why or how)

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
gemmathehusky
post May 23 2019, 04:25 AM
Post#18


UtterAccess VIP
Posts: 4,679
Joined: 5-June 07
From: UK


100%, I would change your error trap to record additional detail about the row that gives the 3022 errors. I assume you can't tell at the moment.




--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Zaddicus
post May 23 2019, 06:16 AM
Post#19



Posts: 122
Joined: 3-April 19
From: Cardiff


That would be correct, my error reporting is basic atm (it won't even print to the right table because "Error 3146 ODBC Call Failed") and in the offline version I did disable the errorhandler to debug the error but it doesn't help with what record is causing the error.


--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
gemmathehusky
post May 23 2019, 06:36 AM
Post#20


UtterAccess VIP
Posts: 4,679
Joined: 5-June 07
From: UK


you need the error handler

CODE
if err=3022 then
    msgbox "error with row: " & whatever & "   Email: " & whatever"
end if

logerror
resume resumepoint



that's why I suggested you copy the row that is erroring to row 2, so you can trace what happens when you get an error. If the error is caused by a logic problem, rather than a data problem, it might not throw the error when you do this.



--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    18th June 2019 - 05:50 PM