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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
cyberkiwiCommented:
if sql3 <> "" then sql3 = sql3 + ", "
sql3 = sql3 & Chr(34) & rstVersion.Fields(i) & Chr(34)

same for sql4
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
cyberkiwiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.