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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba Suddenly Producing Error 3077/3021, Access 2016    
 
   
Zaddicus
post May 20 2019, 09:13 AM
Post#1



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


Hi all,

First of all sorry for the long post. So this is some code that I wrote with a lot of help from cheekybuddha - the code initially ran with no problems at all (I have previously imported 150000 rows of data with no issue last week) however something is now causing it to produce 2 errors and I can't figure out for the life of me how to adapt the code.

Error 3077 - syntax error in string in expression
This is occurring in the line
CODE
    myR2.FindFirst ("Email = '" & myR![Email] & "'")

Only thing is... It imports some of the data then pops the error (Basically it will go 4000 rows no problem then throw the error, run it again it does another 3-4000 and bam error)

Error 3021 - No Current Record
I'm not sure what caused this as I forgot to disable the log error to table rather than display code (my bad) but I can only assume it is to do with the loop - This should be an easy fix
My Idea for this is to run the create new / update existing first, then when that is complete re-run through the data and then apply all the "flags"

FULL CODE:
CODE
'------------------------------------------------------------
' RuncontactImport_Click
'
'------------------------------------------------------------
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


So regarding the error 3021 I was thinking to not run the loop (code below) until the Do Until myR.EOF has completed and then have it return to the start and process the updating of flags. This will ensure that the records ARE there.
CODE
    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


With the 3077 error I'm stumped as it works, but also doesn't. The syntax is fine and google isn't helpful with anything else that could trigger this error.

As always thank-you in advance.

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


Skype: live:castielswift
Go to the top of the page
 
ADezii
post May 20 2019, 09:30 AM
Post#2



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


Could there possibly be a NULL in the [EMail] Field?
Go to the top of the page
 
gemmathehusky
post May 20 2019, 09:34 AM
Post#3


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


I think the second error is probably a corollary to the first not being perfectly handled.

given this
myR2.FindFirst ("Email = '" & myR![Email] & "'")

I would check your email table for
a) null's and
b) embedded quote characters.


I can't see any error handling - when you get the error, display the myR![email] value that is causing the problem (and a record ID to help you)









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

(Gemma was my dog)
Go to the top of the page
 
GroverParkGeorge
post May 20 2019, 09:39 AM
Post#4


UA Admin
Posts: 35,123
Joined: 20-June 02
From: Newcastle, WA


A good assumption when a problem like this appears, i.e. a process that has worked correctly for some time suddenly fails, is that "bad" data has found its way into your source file. This is especially likely when you are bulk processing (importing, exporting, updating) many records. One or more is likely to fall outside the original expectations for that data.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
BruceM
post May 20 2019, 09:45 AM
Post#5


UtterAccess VIP
Posts: 7,885
Joined: 24-May 10
From: Downeast Maine


Do any of the EMail fields contain apostrophes? I believe this is what Dave was getting at when he mentioned embedded quote characters.

You could substitute two double quotes for each single quote:

"Email = """ & myR![Email] & """")

and try again (assuming none of the fields include double quotes, and records with Null in that field are filtered out).
Go to the top of the page
 
cheekybuddha
post May 20 2019, 10:22 AM
Post#6


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


Since you are dealing with an email address I would avoid Bruce's solution because your imported data would then continue to contain single quote characters that are the likely cause of the error.

Perhaps better:
CODE
' ...
  Dim strEmail As String

' ...
Do Until myR.EOF = True
    strEmail = Replace(Nz(myR![Email], vbNullString), "'", vbNullString)
    myR2.FindFirst ("Email = '" & strEmail & "'")
    If myR2.NoMatch = True Then
      myR2.AddNew
      myR2![Email] = strEmail
' ...


hth,

d

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


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post May 20 2019, 10:26 AM
Post#7


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


Also, on a separate note, if any of your import data contain an empty email address, you might quote likely have one record in tblContacts with the data for last such record processed, (and the previous ones' details would have been overwritten)

blush.gif

d

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


Regards,

David Marten
Go to the top of the page
 
BruceM
post May 20 2019, 10:42 AM
Post#8


UtterAccess VIP
Posts: 7,885
Joined: 24-May 10
From: Downeast Maine


Wouldn't that have the effect of changing a correct email address into an incorrect one, if the correct address includes an apostrophe?
Go to the top of the page
 
cheekybuddha
post May 20 2019, 10:59 AM
Post#9


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


True, I thought apostrophes were illegal in email addresses, but I see from the RFC that they can technically come before the @ symbol.

My suspicion was more that the 'bad' addresses were coming from a csv type output that uses single quotes as a string delimiter. You wouldn't really want to store the surrounding quotes.

So perhaps a better algorithm to trim off preceding and trailing single quotes and doubling up the internal ones for the SQL is required.

thumbup.gif

d

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


Regards,

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


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


Yes, I thought there might be apostrophes or other problematic characters in an email address.
(just because the character is illegal in the email doesn't mean the data doesn't contain them)

I think you need some decent error handling going forward to recover from this sort of issue.

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

(Gemma was my dog)
Go to the top of the page
 
Zaddicus
post May 20 2019, 01:54 PM
Post#11



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


Firstly I apologize for the delay in responce, although I been monitoring this thread on my phone I been in a conference call for the last (*explicit* lifetime) good knows how long...

When I first imported the 150K rows of data I cleaned it myself prior to the import, this is probably why it worked. I am certain the cause as you all mentioned is the fact some email addresses contain the horrid ' character. I would usually do a find and replace prior to data entry but as I didn't clean the data personally (feedback will be provided to the data handler) and was rushed to import before C.O.P I overlooked it.

1. I am going to re-write the code tomorrow to accept ' in the email
2.
QUOTE
I think you need some decent error handling going forward to recover from this sort of issue
a lot of my other modules including this one has custom error handling, but as I can't anticipate what errors would be "common" I haven't set the option to select case. Going forward error 3077 will be added to prompt an issue with the data and end the module advising the user to check the data
3. I will also be introducing a "redundant check" code (Don't ask how I'm still working on it) to check the quality of the email address/highlight issues when imported to the staging table to highlight potential issues before running the create/update process

Again, ever grateful for the feedback as it does help me learn from my rookie mistakes.


Just curious, if I fix the error 3077 will that mean I will no longer encounter the wild Error 3021?
This post has been edited by Zaddicus: May 20 2019, 01:57 PM

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


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post May 20 2019, 01:57 PM
Post#12


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


>> Just curious, if I fix the error 3077 will that mean I will no longer encounter the wild Error 3021? <<

Most likely.

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


Regards,

David Marten
Go to the top of the page
 
BruceM
post May 20 2019, 02:24 PM
Post#13


UtterAccess VIP
Posts: 7,885
Joined: 24-May 10
From: Downeast Maine


I routinely use this function, which is posted in the UA function library, to resolve quotes within quotes. I don't think it has let me down yet.
Go to the top of the page
 
gemmathehusky
post May 20 2019, 03:28 PM
Post#14


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


I looked again, and you do have error handling.

So I imagine in this case the process stops after the error 3077. You aren't returning a "false" value for the function (it's a sub). You aren't tidying up the recordsets and so on after the error. You now return to the calling code, which doesn't know there was an error, and I imagine whatever comes next produces the "no current record" error.


so you could pre-vet the email address for double and single quotes, and just discard any (and report) any not formatted properly.
" is chr(34)
' is chr(39)

instead of aborting you could report the error and ask whether you want to continue or not. If you continue, you resume at the end of the loop, and then the process will read the next record and continue.

That's what I meant about the error handling.


You could even pre-process the file to check all the email addresses first before starting the full process.

if you want to trace it, deliberately put an invalid item in the second email, and step through the code - then you can see exactly what it does when it gets a bad email.

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

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



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


QUOTE
instead of aborting you could report the error and ask whether you want to continue or not. If you continue, you resume at the end of the loop, and then the process will read the next record and continue.

Very good idea - I may work on this kind of solution but code it so it somehow exports the emails that were "skipped" to a file for the data team to recheck/clean-up.

QUOTE
I routinely use this function, which is posted in the UA function library, to resolve quotes within quotes. I don't think it has let me down yet.

I will have a look into this, however the database I'm currently using is what I refer to as a "dirty version" and I'm probably going to move over to visual basic (C# .net framework) and a mySQL server rather than MSSQL

QUOTE
You could even pre-process the file to check all the email addresses first before starting the full process

This is a great idea as it's best practice not to rely on the fact the data handed over is 100% correct/cleaned - The data is loaded into a staging table first so no doubt I can play with this idea in the mean time
This post has been edited by Zaddicus: May 21 2019, 03:21 AM

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


Skype: live:castielswift
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th June 2019 - 06:38 PM