dynamic sql INSERT and trailing commas

I'm writing a sub that creates a version history of parts as they are changed. I am dynamically creating the input fields and value fields based off three parameters, VersionID, the main table and the history table. During this process i wind up with a trailing comma that throw an error when I build my INSERT string.

I can fix it with a quick right(x,y), but would like to know a better way.
Public Sub VerCtrl(VerID As Integer, tblName_main As String, tblName_history As String)
    
    Dim dbVersion As Database
    Dim rstVersion As Recordset
    Dim rstVersion_history As Recordset
    Dim sql1 As String
    Dim sql2 As String
    Dim sql3 As String
    Dim sql4 As String
        
    Dim rstColumns() As String
    Dim rstColumns_history() As String
    
    Dim columnCnt As Integer
    Dim i As Integer
    
    'create initial sql string
    sql1 = "SELECT *" _
        & " FROM " & tblName_main & "" _
        & " WHERE " & tblName_main & ".id=" & VerID & ""
        
    sql2 = "SELECT * FROM " & tblName_history & ""
        
    'use for troubleshooting
    'Call QueryBuild("qryVersionControl", sql1)

    Set dbVersion = CurrentDb
    Set rstVersion = dbVersion.OpenRecordset(sql1)
    Set rstVersion_history = dbVersion.OpenRecordset(sql2)
    
    'use for troubleshooting
    'Set rstVersion = dbVersion.OpenRecordset("qryVersionControl")
    
    
        
        'determine number of columns and redim columnCnt array to hold the data
        columnCnt = rstVersion.Fields.Count
        ReDim rstColumns(0 To columnCnt)
            
        Do While rstVersion.EOF = False
        
            'loop through rstVersion and populate rstColumns array with current data
            For i = 0 To (rstVersion.Fields.Count - 1)
            
                rstColumns(i) = rstVersion.Fields(i)
                
                sql3 = sql3 & " " & Chr(34) & rstVersion.Fields(i) & Chr(34) & ", "
                
                'for troubleshooting
                'MsgBox rstColumns(i) & " " & .Fields(i), vbInformation, "test"
                'MsgBox sql3, vbInformation, "test"
                        
            Next i
                                    
            'loop though columns and build insert string.
            For i = 2 To (rstVersion_history.Fields.Count - 1)
            
                sql4 = sql4 & "" & rstVersion_history.Fields(i).Name & ", "
                'MsgBox sql4, vbInformation, "test"
                            
            Next i
            
        rstVersion.MoveNext
        
        Loop
        
        'remove wierdo comma
        'insert
        sql3 = Left(sql3, (Len(sql3) - 2))
        'values
        sql4 = Left(sql4, (Len(sql4) - 2))
                      
        'insert data into history table
        dbVersion.Execute " INSERT INTO " & tblName_history & "" _
        & "(" & sql4 & ") VALUES " _
        & "(" & sql3 & ");"
        
        

End Sub

Open in new window

jclark33Asked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
If you are pedantic about even that test in each loop iteration, you can move the first seed outside of the loops.


            'loop through rstVersion and populate rstColumns array with current data
            sql3 = Chr(34) & rstVersion.Fields(0) & Chr(34)
            For i = 1 To (rstVersion.Fields.Count - 1)
           
                rstColumns(i) = rstVersion.Fields(i)
               
                sql3 = sql3 & ", " & Chr(34) & rstVersion.Fields(i) & Chr(34)
               
                'for troubleshooting
                'MsgBox rstColumns(i) & " " & .Fields(i), vbInformation, "test"
                'MsgBox sql3, vbInformation, "test"
                       
            Next i
 
0
 
Patrick MatthewsCommented:
Replace:



        Do While rstVersion.EOF = False
       
            'loop through rstVersion and populate rstColumns array with current data
            For i = 0 To (rstVersion.Fields.Count - 1)
           
                rstColumns(i) = rstVersion.Fields(i)
               
                sql3 = sql3 & " " & Chr(34) & rstVersion.Fields(i) & Chr(34) & ", "
               
                'for troubleshooting
                'MsgBox rstColumns(i) & " " & .Fields(i), vbInformation, "test"
                'MsgBox sql3, vbInformation, "test"
                       
            Next i
                                   
            'loop though columns and build insert string.
            For i = 2 To (rstVersion_history.Fields.Count - 1)
           
                sql4 = sql4 & "" & rstVersion_history.Fields(i).Name & ", "
                'MsgBox sql4, vbInformation, "test"
                           
            Next i
           
        rstVersion.MoveNext
       
        Loop
       
        'remove wierdo comma
        'insert
        sql3 = Left(sql3, (Len(sql3) - 2))
        'values
        sql4 = Left(sql4, (Len(sql4) - 2))




with:

        Do While rstVersion.EOF = False
       
            'loop through rstVersion and populate rstColumns array with current data
            For i = 0 To (rstVersion.Fields.Count - 1)
           
                rstColumns(i) = rstVersion.Fields(i)
               
                sql3 = sql3 & ", " & Chr(34) & rstVersion.Fields(i) & Chr(34)
               
                'for troubleshooting
                'MsgBox rstColumns(i) & " " & .Fields(i), vbInformation, "test"
                'MsgBox sql3, vbInformation, "test"
                       
            Next i
                                   
            'loop though columns and build insert string.
            For i = 2 To (rstVersion_history.Fields.Count - 1)
           
                sql4 = sql4 & ", " & rstVersion_history.Fields(i).Name
                'MsgBox sql4, vbInformation, "test"
                           
            Next i
           
        rstVersion.MoveNext
       
        Loop
       
        'remove wierdo comma
        'insert
        sql3 = Mid(sql3, 3)
        'values
        sql4 = Mid(sql4, 3)
0
 
jclark33Author Commented:
thanks for the response, but thats (a little better) just another iteration of my original solution. I have been trying to not have to manicure the string after the loops
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
if sql3 <> "" then sql3 = sql3 + ", "
sql3 = sql3 & Chr(34) & rstVersion.Fields(i) & Chr(34)

same for sql4
0
 
Patrick MatthewsCommented:
The only alternative would be something like this, which I do not think is an improvement:



        Do While rstVersion.EOF = False
       
            'loop through rstVersion and populate rstColumns array with current data
            For i = 0 To (rstVersion.Fields.Count - 1)
           
                rstColumns(i) = rstVersion.Fields(i)
               
                sql3 = Chr(34) & rstVersion.Fields(i) & Chr(34)
                If i < (rstVersion.Fields.Count - 1) Then sql3 = sql3 & ", "
               
                'for troubleshooting
                'MsgBox rstColumns(i) & " " & .Fields(i), vbInformation, "test"
                'MsgBox sql3, vbInformation, "test"
                       
            Next i
                                   
            'loop though columns and build insert string.
            For i = 2 To (rstVersion_history.Fields.Count - 1)
           
                sql4 = sql4 & rstVersion_history.Fields(i).Name
                If i < (rstVersion_history.Fields.Count - 1) Then sql4 = sql4 & ", "

                'MsgBox sql4, vbInformation, "test"
                           
            Next i
           
        rstVersion.MoveNext
       
        Loop
     
0
 
jclark33Author Commented:
Keeeeeweeeeeeeeezy you got it.


0
 
jclark33Author Commented:
Even better, you get +10 heady points for using pedantic, thanks for the help.
0
 
cyberkiwiCommented:
lol... cheers & many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.