Solved SQL Append Syntax Error

[arnelgp]

what are you using in your Code?

Currentdb.Execute

the above function will not complain or throw an error if there is a duplicate.

Docmd.RunSQL?

you need to SetWarnings to False first.

 

[arnelgp]

Or... you can use Update query:

Update Target_Table As T Right Join Source_Table As S On T.CommonField = S.CommonField Set T.Field1 = S.Field1,
T.Field2 = S.Field2
[, T.XField = S.XField];

 

[LanaR]

The kludge I've developed to work around this is to;

1. SetWarnings False
2. On Error Resume Next
3. SetWarning True (at the end of the procedure

 

[LanaR]

Thanks to all for their input

 

[LanaR]

Following is the final code if anyone is interested in the future. It still contains a couple of MsgBox (commented out) used during development to prove code was picking up what I expected.

Probably should mention that TBL_Grape2, had two filed GrapeID (PK) and Grape (text filed that was indexed, Duplicates not allowed).

    DoCmd.SetWarnings False
    On Error Resume Next
   
    Dim GrapeCnt, IntGrapeID, IntPunctPos As Integer
    Dim GrapeOrgStr, GrapeTrimStr, GrapeSendStr As String
    Dim QryAppend, QryDelRec As String
   
   
   

'determine number of grapes to be normalised
    GrapeCnt = DCount("grapeid", "tbl_grape1")
   
   
 
    While GrapeCnt > 0
   
 
'determine next recrod to be nomalised
    IntGrapeID = DLookup("grapeID", "tbl_grape1", "grapeID = " & DMin("grapeID", "tbl_grape1"))
   
   

'Determine String to be normalised
    GrapeOrgStr = DLookup("Grape", "tbl_grape1", "grapeID = " & IntGrapeID)
    'MsgBox "Next text to be normalised is " & GrapeOrgStr

'Check record for commas or ampersands
'to work this portion of the code relies on commas always proceeding ampersands

    If InStr(1, GrapeOrgStr, ",") > 0 Then          'Check if text contains a comma and record postion
        IntPunctPos = InStr(1, GrapeOrgStr, ",")
    ElseIf InStr(1, GrapeOrgStr, "&") > 0 Then      'Check if text contains a ampersand and record postion
        IntPunctPos = InStr(1, GrapeOrgStr, "&")
    End If
       
    If IntPunctPos <> 0 Then                        'Check if commas or ampersands detected then cycle through text removing and append data to new table
        'MsgBox "we have work to do"
        While IntPunctPos > 0
       

               
               
                GrapeSendStr = Left(GrapeOrgStr, IntPunctPos - 1)
               
               
                QryAppend = "INSERT INTO TBL_Grape2 ( Grape )" & _
                            "values ( " & Chr(34) & GrapeSendStr & Chr(34) & " ) ;"
                           
               
                If DCount("[Grape]", "tbl_grape2", "[grape] = '" & Chr(34) & GrapeSendStr & Chr(34) & "'") < 1 Then 'check if this grape already exsists and skip
               
                    DoCmd.RunSQL (QryAppend)
                End If
               
                If (Len(GrapeOrgStr) - (IntPunctPos + 1)) > 1 Then
               
                   
                    GrapeTrimStr = Right(GrapeOrgStr, (Len(GrapeOrgStr) - (IntPunctPos + 1)))
                    GrapeOrgStr = GrapeTrimStr
                End If
               
            IntPunctPos = 0
                       
            If InStr(1, GrapeOrgStr, ",") > 0 Then          'Check if text contains a comma and record postion
                IntPunctPos = InStr(1, GrapeOrgStr, ",")
            ElseIf InStr(1, GrapeOrgStr, "&") > 0 Then      'Check if text contains a ampersand and record postion
                IntPunctPos = InStr(1, GrapeOrgStr, "&")
            End If
               
        Wend
       
    Else                                            'If no commas or ampersands detected simply append current record to new table
     
                QryAppend = "INSERT INTO TBL_Grape2 (  Grape ) " & _
                            "SELECT TBL_Grape1.Grape " & _
                            "FROM TBL_Grape1 " & _
                            "WHERE (((TBL_Grape1.GrapeID)= " & IntGrapeID & " ));"

                DoCmd.RunSQL (QryAppend)
       
        End If

   End If
   
   
'Delete first record once appended to new Table
    QryDelRec = "DELETE TBL_Grape1.GrapeID, TBL_Grape1.Grape " & _
                "FROM TBL_Grape1 " & _
                "WHERE (((TBL_Grape1.GrapeID)= " & IntGrapeID & "));"
               
    DoCmd.RunSQL (QryDelRec)
   
'Update GrapeCnt with new record total
    GrapeCnt = DCount("grapeid", "tbl_grape1")
   
Wend



DoCmd.SetWarnings True

End Sub

 

[arnelgp]

you could have used Recordset for tbl_grape1.
you could have used Split() for those "," and "&".
you could have used Currentdb.Execute, so there is no need to SetWarning On/off.

anyway, its your db.

 

[arnelgp]

more or less like this:

Private Sub Test000()
    Const APND_QUERY = _
         "INSERT INTO TBL_Grape2 ( Grape ) " & _
                            "SELECT p1;"
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim GrapeCnt As Long
    Dim GrapeOrgStr As String
    Dim varValue As Variant
    Dim i As Integer
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_grape1", dbOpenSnapshot, dbReadOnly)
 
    With rs
        If Not (.BOF And .EOF) Then
            .MoveLast
            GrapeCnt = .RecordCount
            .MoveFirst
        End If
        Do Until .EOF
            GrapeOrgStr = !Grape & ""
            varValue = Split(GrapeOrgStr, ",")
            If UBound(varValue) = 0 Then
                varValue = Split(GrapeOrgStr, "&")
            End If
         
            For i = 0 To UBound(varValue)
                With db.CreateQueryDef("", APND_QUERY)
                    .Parameters(0) = Trim$(varValue(i))
                    .Execute
                End With
            Next
         
            .MoveNext
            GrapeCnt = GrapeCnt - 1
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

 

[LanaR]

Thanks for that, I've pasted that code verbatim into my DB, however this line;

Set rs = db.OpenRecordset("tbl_grape1", dbOpenSnapshot, dbReadOnly)

is throwing a Run time error 3001 (invalid argument). This is all new ground for me, so I am floundering somewhat

P.S. who said trivial lockdown projects couldn't be the source of learning

 

[arnelgp]

what version do you have?
you can remove the other parameters:


Set rs = db.OpenRecordset("tbl_grape1")

also check if the code has "same" table name as you have.

 

[LanaR]

OK now it's throwing a 3129 error

on this line;

With db.CreateQueryDef("", APND_QUERY)

As I mentioned before, this is uncharted territory for me. So I'm going to guess that APND_QUERY needs to be defined somewhere as the Append SQL. Given that this is not referencing any tables, I'm not sure how that gets worded

 

[arnelgp]

i define it on My sub:

Private Sub Test000()
    Const APND_QUERY = _
         "INSERT INTO TBL_Grape2 ( Grape ) " & _
                            "SELECT p1;"

 

[LanaR]

Sorry, that's my bad Somehow I missed that with my cut and paste

 

[LanaR]

That works a treat, considerably faster than my code

However, there seems to be a flaw in the logic of this section of code

GrapeOrgStr = !Grape & ""
            varValue = Split(GrapeOrgStr, ",")
            If UBound(varValue) = 0 Then
                varValue = Split(GrapeOrgStr, "&")
            End If

As it's creating records containing & TBH I can't see why it's failing

 

[arnelgp]

create two loop perhaps:

...
...
    Dim varValue1 As Variant
    Dim varValue2 As Variant
    Dim i As Integer, k As Integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_grape1")
 
    With rs
        If Not (.BOF And .EOF) Then
            .MoveLast
            GrapeCnt = .RecordCount
            .MoveFirst
        End If
        Do Until .EOF
            GrapeOrgStr = !Grape & ""
            varValue1 = Split(GrapeOrgStr, ",")
            
            For i = 0 To UBound(varValue1)
                varValue2 = Split(varValue1(i), "&")
                For k = 0 To UBound(varValue2)
                    With db.CreateQueryDef("", APND_QUERY)
                        .Parameters(0) = Trim$(varValue2(k))
                        .Execute
                    End With
                Next k
            Next i
        
            .MoveNext
            GrapeCnt = GrapeCnt - 1
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

 

[LanaR]

Bingo that's done the trick

Thank you so much for that, I've learned a whole lot from you about using DAO

 

[LanaR]

Post Script: If I've learned one thing from all this it is the importance of properly normalising your DB from the very start. Even after spending over two day (on and off) struggling with this issue, there is still data that no (sensible) amount of coding would be able to normalise. Lucky that amounts to only two records wherein there were no delimiters of any kind and then there's also the issue of fingergraphical errors eg. Chenin Blanc and Chenin Blac

Bear in mind however, this is a DB that I started building almost 18 years ago and have tweaked and improved as my knowledge of DB design improves. New ideas on what data would be nice to store and how to display/access that data, pop into my head

 

[LanaR]

Oh dear now the Procrastination monkey has come to visit

 

[LanaR]

@arnelgp to aid my self in understanding how your nifty piece of code works, I've added comments to it. I'd be every so grateful if you could take a look and correct anything that I've misunderstood.

Private Sub Command4_Click()

'define append query SQL
    Const APND_QUERY = _
         "INSERT INTO TBL_Grape2 ( Grape ) " & _
                            "SELECT p1;"
  
'Define various variables that will be required for this process
    Dim GrapeCnt As Long            'Count of records in data set
    Dim GrapeOrgStr As String       'Original record string to be searched
    Dim varValue1 As Variant        'variable to store string with commas removes
    Dim varValue2 As Variant        'Variable to store string with ampersands removed
    Dim i As Integer, k As Integer  'variables to be used as counters


'Define general location of data
    Set db = CurrentDb
  
'Define specific location of data, and open the record set
    Set rs = db.OpenRecordset("tbl_grape1")

'Start working on the record set as defined above
    With rs
  
'Count number of records and save as GrapeCnt
        If Not (.BOF And .EOF) Then
            .MoveLast                   'move to last record
            GrapeCnt = .RecordCount     'assing record count to GrapeCnt variable
            .MoveFirst                  'move to first record
        End If
      
'This is were the real work happens
        Do Until .EOF                                           'Start cycleing through record set until the end is reached (Do Loop)
            GrapeOrgStr = !Grape & ""                           'Assign next record in Field[Grape] to variable GrapeOrgStr
            varValue1 = Split(GrapeOrgStr, ",")                 'Assign split string (split on commas) to variable VarVal1
          
            For i = 0 To UBound(varValue1)                      '(loop i) - will loop the number detected by UBound()
                varValue2 = Split(varValue1(i), "&")            'Assign split string (split on Ampersand) to variable VarVal2 - This variable now has no "," or "&" just a number of discrete strings
                For k = 0 To UBound(varValue2)                  '(loop k) - will loop the number detected by UBound()
                    With db.CreateQueryDef("", APND_QUERY)      'Create append query based on SQL previous defined (at the start)
                        .Parameters(0) = Trim$(varValue2(k))    'Insert string to be append in Append Query
                        .Execute                                'Run append query
                    End With                                    '
                Next k                                          'End of loop k, return to start
            Next i                                              'End of loop i, return to start
      
            .MoveNext                                           'Move to next record
            GrapeCnt = GrapeCnt - 1                             'Incriment GrapeCnt down by one -Not sure why? as GrapeCnt dosen't seem to be in use??
        Loop                                                    'Return start of loop "Do" Loop
      
    
'We're done here, now let's close the data set
        .Close
'Tidy up before exiting the sub-routine
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub