Solved

Excel VBA - SQL Statement 'missing ;' error

Posted on 2016-10-31
20
44 Views
Last Modified: 2016-11-04
Loane, or anyone.

See my question here
https://www.experts-exchange.com/questions/28979854/Excel-2016-VBA-loop-slows-down.html?anchor=a41865904&notificationFollowed=178394133#a41865904

When I run the code as per below I get this message.
Error messageI ran it in Access 2016 in the query window and got the same message. It points to the line after the VALUES list, where currently there's a ','. Replacing the ',' with a ';' didn't help.

I have attached the Excel workbook also.
Thanks Loane or anyone.

Sub PutData_perEE_viaArray()

    'Thanks to Loane on E-E
    'https://www.experts-exchange.com/questions/28979854/Excel-2016-VBA-loop-slows-down.html#a41866766

    Dim rsData As ADODB.Recordset


    Dim varHeaders() As Variant, varData() As Variant, varFieldType() As Variant, varRowData() As Variant
    Dim sSQL As String, strTable As String, strRowData As String
    Dim i As Long, iRow As Long, iCol As Long
    Dim booNull As Boolean
     
    Range("K9").Value = ""

    varFieldType = Sheet1.Range("Fieldlist").Offset(1, 0)
    strTable = "tblData"

'    MsgBox iLastRow

    sSQL = "INSERT INTO " & strTable & " ("
    
    'Read Headers into array
    varHeaders = Sheet1.Range("FieldList")
    
    'Write Headers to SQL string
    For iCol = 1 To UBound(varHeaders, 2)
        If varHeaders(1, iCol) <> "" Then
            strRowData = strRowData & "," & varHeaders(1, iCol)
        End If
    Next iCol
    
    sSQL = sSQL & Mid(strRowData, 2) & ")" & "VALUES "
    strRowData = ""
    
    'Read row Values to array
    varData = Sheet1.Range("DataRange")
    ReDim varRowData(1 To UBound(varData, 1))
    
    i = 0
    For iRow = 1 To UBound(varData, 1)
        strRowData = ""
        booNull = True
        For iCol = 1 To UBound(varHeaders, 2)
        
            If varHeaders(1, iCol) <> "" Then
                
                If varData(iRow, iCol) <> "" And varData(iRow, iCol) <> "Null" Then
                    booNull = False
                End If
                
                Select Case varFieldType(1, iCol)
                'if TEXT or LONG TEXT
                Case "202", "203"
                    strRowData = strRowData & ",'" & varData(iRow, iCol) & "'"
                    
                'if NUMBER
                Case "3"
                    strRowData = strRowData & "," & varData(iRow, iCol)
                
                'if DATE
                Case "7"
                    strRowData = strRowData & "," & SQLDate(varData(iRow, iCol))
                End Select
                
            End If
        Next iCol
        
        If booNull = False Then
            i = i + 1
            varRowData(i) = "(" & Mid(strRowData, 2) & ")"
        End If
        
        'Not sure why you need this line as will impact performance...
        Sheet1.Range("K9").Value = i
               
    Next iRow
    
    'Restrict array to dataset
    ReDim Preserve varRowData(1 To i)
    
    
    Call DBConnectionAccess
    
    
    
    'Write Values to SQL string
    sSQL = sSQL & Join(varRowData, ",")
    
    Sheets("Debug").Range("A3").Value = sSQL
    
    'run the query
    Set rsData = New ADODB.Recordset
    'rsData.Open sSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    rsData.Open sSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText
    
    Call DBConnectionClose
    
    
    MsgBox "Done"

End Sub

Open in new window

0
Comment
Question by:hindersaliva
  • 7
  • 5
  • 5
  • +3
20 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41866861
Hi,

pls try

sSQL = sSQL & Join(varRowData, ",") & ";"

Open in new window

Regards
0
 

Author Comment

by:hindersaliva
ID: 41866889
The SQL string looks like this
INSERT INTO tblTest (Field1,Field2,Field3) 
VALUES
('one','two','three'),
('one','two','three'),
('one','two','three'),

Open in new window


Running that in Access 2016 query window gives me the error shows in question.

The error points to the first ',' of the VALUES after the brackets.
When I put a ';' I get a message saying 'found characters after the semi colon'.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41866893
Maybe
sSQL = sSQL & Join(varRowData, ",")
sSQL = IIf(Right(sSQL, 1) = ",", Left(sSQL, Len(sSQL) - 1), sSQL) & ";

Open in new window

0
 

Author Comment

by:hindersaliva
ID: 41866920
Rgonzo, nope. Same error.

See my small example SQL INSERT above. What should the correct syntax for that be? ie. where does SQL expect to find the ';'?
Thanks
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41866938
isn't it

INSERT INTO tblTest (Field1,Field2,Field3)
VALUES
('one','two','three'),
('one','two','three'),
('one','two','three');
0
 

Author Comment

by:hindersaliva
ID: 41866953
No. Access 2016 doesn't like that.

(personally I have always done INSERT SQL with just one field list and one values list. So I don't know what is right. Actually I didn't even know it's possible to have many values lists until Loane's answer in the previous question in above link)
0
 
LVL 33

Expert Comment

by:Norie
ID: 41866956
Does the code work with only a few lines of data?
0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 62 total points
ID: 41866959
I don't think Access allows for multiple dataset inserts (MSSQL in recent releases does). You will have to insert one row at a time, with a SQL looking like:
INSERT INTO tblTest (Field1,Field2,Field3) VALUES ('one','two','three');
INSERT INTO tblTest (Field1,Field2,Field3) VALUES ('one','two','three');
INSERT INTO tblTest (Field1,Field2,Field3) VALUES ('one','two','three');

Open in new window

0
 

Author Comment

by:hindersaliva
ID: 41866974
Rgonzo and Norie, Access 2016 works with one INSERT statement at a time. Looks like.

Qlemo, Access 2016 doesn't seem to like multiple statements, with or without the ';'

In that case, I'd like to modify Loane's code (see the question) to send one INSERT at a time on each pass of the loop.
Any help appreciated.
Thanks
0
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 62 total points
ID: 41866985
I think access would only accept one Insert at a time
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 62 total points
ID: 41866999
You can't insert multiple row values that way.

You can Union several Select statements as your 'table source'.
Example:
INSERT INTO tblTest (Field1,Field2,Field3) 
Select * From (
SELECT top 1 "one" , "two" , "three"  FROM msysobjects WHERE msysobjects.[type]=3
UNION ALL
SELECT top 1 "four" , "five" , "six"  FROM msysobjects WHERE msysobjects.[type]=3
UNION ALL
SELECT top 1 "seven" , "eight" , "nine"  FROM msysobjects WHERE msysobjects.[type]=3
)

Open in new window

0
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 62 total points
ID: 41867009
then try
Sub PutData_perEE_viaArray()

    'Thanks to Loane on E-E
    'https://www.experts-exchange.com/questions/28979854/Excel-2016-VBA-loop-slows-down.html#a41866766

    Dim rsData As ADODB.Recordset


    Dim varHeaders() As Variant, varData() As Variant, varFieldType() As Variant, varRowData() As Variant
    Dim sSQL As String, strTable As String, strRowData As String
    Dim i As Long, iRow As Long, iCol As Long
    Dim booNull As Boolean
     
    Range("K9").Value = ""

    varFieldType = Sheet1.Range("Fieldlist").Offset(1, 0)
    strTable = "tblData"

'    MsgBox iLastRow

    sSQL = "INSERT INTO " & strTable & " ("
    
    'Read Headers into array
    varHeaders = Sheet1.Range("FieldList")
    
    'Write Headers to SQL string
    For iCol = 1 To UBound(varHeaders, 2)
        If varHeaders(1, iCol) <> "" Then
            strRowData = strRowData & "," & varHeaders(1, iCol)
        End If
    Next iCol
    
    sSQL = sSQL & Mid(strRowData, 2) & ")" & " VALUES "
    strRowData = ""
    
    'Read row Values to array
    varData = Sheet1.Range("DataRange")
    ReDim varRowData(1 To UBound(varData, 1))
    
    i = 0
    For iRow = 1 To UBound(varData, 1)
        strRowData = ""
        booNull = True
        For iCol = 1 To UBound(varHeaders, 2)
        
            If varHeaders(1, iCol) <> "" Then
                
                If varData(iRow, iCol) <> "" And varData(iRow, iCol) <> "Null" Then
                    booNull = False
                End If
                
                Select Case varFieldType(1, iCol)
                'if TEXT or LONG TEXT
                Case "202", "203"
                    strRowData = strRowData & ",'" & varData(iRow, iCol) & "'"
                    
                'if NUMBER
                Case "3"
                    strRowData = strRowData & "," & varData(iRow, iCol)
                
                'if DATE
                Case "7"
                    strRowData = strRowData & "," & SQLDate(varData(iRow, iCol))
                End Select
                
            End If
        Next iCol
        
        If booNull = False Then
            i = i + 1
            varRowData(i) = "(" & Mid(strRowData, 2) & ")"
        End If
        
        'Not sure why you need this line as will impact performance...
        Sheet1.Range("K9").Value = i
               
    Next iRow
    
    'Restrict array to dataset
    ReDim Preserve varRowData(1 To i)
    
        
    'Write Values to SQL string
    For Idx = LBound(varRowData) To UBound(varRowData)
        Call DBConnectionAccess
    
        sSQL1 = sSQL & varRowData(Idx)
    
        Sheets("Debug").Range("A3").Value = ssql1
    
        'run the query
        Set rsData = New ADODB.Recordset
        'rsData.Open sSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
        rsData.Open sSQL1, objConn, adOpenStatic, adLockReadOnly, adCmdText
        Call DBConnectionClose
    Next

    
    
    MsgBox "Done"

End Sub

Open in new window

0
 

Author Comment

by:hindersaliva
ID: 41867036
Rgonzo, it crashed my Excel, but I get the idea.
0
 
LVL 33

Accepted Solution

by:
Norie earned 252 total points
ID: 41867039
Perhaps this will work, I'm pretty sure it's creating valid Access INSERT SQL statements.
Sub PutData_perEE_viaArray()
Dim rsData As ADODB.Recordset
Dim varHeaders() As Variant, varData() As Variant, varFieldType() As Variant, varRowData() As Variant
Dim strSQL As String, strFields As String, strValues As String, strTable As String, strData As String, arrRowData()
Dim cnt As Long, iRow As Long, iCol As Long
Dim booNull As Boolean

    Range("K9").Value = ""

    varFieldType = Sheet1.Range("Fieldlist").Offset(1, 0)
    strTable = "tblData"

    '    MsgBox iLastRow

    strSQL = "INSERT INTO " & strTable & " ("

    'Read Headers into array
    strFields = Join(Application.Transpose(Application.Transpose(Sheet1.Range("FieldList").Value)), ",")

    strSQL = strSQL & strFields & ") VALUES "

    'Read row Values to array
    varData = Sheet1.Range("DataRange")
    
    ReDim varRowData(1 To UBound(varData, 1))

    Call DBConnectionAccess

    For iRow = 1 To UBound(varData, 1)

        For iCol = 1 To UBound(varData, 2)

            Select Case varFieldType(1, iCol)
                    'if TEXT or LONG TEXT
                Case "202", "203"
                    strData = "'" & varData(iRow, iCol) & "'"

                    'if NUMBER
                Case "3"
                    strData = varData(iRow, iCol)

                    'if DATE
                Case "7"
                    strData = SQLDate(varData(iRow, iCol))
            End Select

            ReDim Preserve arrRowData(cnt)

            arrRowData(cnt) = strData

            cnt = cnt + 1
        Next iCol

        strData = "(" & Join(arrRowData, ",") & ");"

        cnt = 0

        Erase arrRowData

        Set rsData = New ADODB.Recordset
        
        rsData.Open strSQL & strData, objConn, adOpenStatic, adLockReadOnly, adCmdText
        
    Next iRow

    Call DBConnectionClose

    MsgBox "Done"

End Sub

Open in new window

0
 

Author Comment

by:hindersaliva
ID: 41867071
Norrie, that worked!

I still get the 'not responding' while it's running, but I shall add VBA.DoEvents as Rgonzo suggested and try. I shall report back in a while.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 252 total points
ID: 41867105
The way I coded it was that it keeps the connection to the database open throughout.

You might want to try changing that so it opens the connection, runs the 'current query, closes the connection then moves onto the next query.

Not sure if that will actually help or hinder though.:)

PS Another idea - write all the SQL to a separate sheet, with a query per row, then loop through the rows on that sheet and run the queries.
0
 
LVL 33

Expert Comment

by:Norie
ID: 41867109
Another idea - instead of creating a new recordset for each query, re-use the existing one.
0
 

Author Comment

by:hindersaliva
ID: 41867134
I shall try all those.

I think, however, that storing the SQL in an array is faster than on a sheet. But the latter is good for debugging.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 252 total points
ID: 41867226
Array, sheet, whatever.:)

The idea was to separate the creation of the SQL statements from their execution.
0
 
LVL 13

Assisted Solution

by:ioane
ioane earned 62 total points
ID: 41867622
Hi hindersaliva,

 Apologies, from your original code I assumed you were using SQL Server.

 I can't work on your issue until tonight, but there is a simple way you can do this in MS Access.

 Forget about the SQL string, it will be too slow going row by row.

 1. Use VBA to write your data into a csv file, (the code I have provided previously should do half of this already)
 2. Import the file using: DoCmd.TransferText

 This will be a much faster solution.

 If you have no luck by tonight, I will post some code to do this.

 Cheers.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now