Missing semicolon (;) at end of SQL statement.

Hi Experts,

I need to create dummy records in order to get my id to 99997 and test some things with the following code

Private Sub cmd99997_Click()
    
    lbl.Caption = "wait..."
    
    Dim lngLow As Long
    Dim lngHigh As Long
    'Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim i As Long
    Dim lngCount As Long
    
    lngLow = 55136
    lngHigh = 99997
    
    lngCount = 0
    
    strSQL = "INSERT INTO tblReservations (fldCity, fldReservComment) VALUES "
    
    For i = lngLow To lngHigh
    
        strSQL = strSQL & "(1, '" & i & "'),"
        lngCount = lngCount + 1
        
        If (lngCount = 999) Or (lngCount = i) Then
    
            strSQL = Trim(strSQL)
            strSQL = Left(strSQL, Len(strSQL) - 1)
            strSQL = strSQL & ";"
            
            CurrentDb.Execute strSQL, dbSeeChanges
                        
            lngCount = 0
            strSQL = "INSERT INTO tblReservations (fldCity, fldReservComment) VALUES "
        
        End If
    
    Next
    
    
    MsgBox "DONE!!!"
    
    lbl.Caption = "ok"
    
End Sub

Open in new window


... but I keep getting
Missing semicolon (;) at end of SQL statement.

Open in new window


However when I do ?strSQL in the immediate window in SQL's New Query, and Execute - it works.

What am I doing wrong?
APD TorontoSoftware DeveloperAsked:
Who is Participating?
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.

Rey Obrero (Capricorn1)Commented:
try this codes first
    Dim lngLow As Long
    Dim lngHigh As Long
    'Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim i As Long
    Dim lngCount As Long
    
    lngLow = 55136
    lngHigh = 99997
    
    lngCount = 0
    
    
    For i = lngLow To lngHigh
    strSQL = "INSERT INTO tblReservations (fldCity, fldReservComment) VALUES "

        strSQL = strSQL & "(1, '" & i & "')"
            CurrentDb.Execute strSQL, dbFailOnError
        lngCount = lngCount + 1
        
'        If (lngCount = 999) Or (lngCount = i) Then
'            strSQL = Trim(strSQL)
'            strSQL = Left(strSQL, Len(strSQL) - 1)
'            strSQL = strSQL & ";"
'
'            CurrentDb.Execute strSQL, dbFailOnError
'
'            lngCount = 0
'            strSQL = "INSERT INTO tblReservations (fldCity, fldReservComment) VALUES "
'
'        End If
    
    Next
    
    
    MsgBox "DONE!!!"
    
    lbl.Caption = "ok"

Open in new window



your looping is off..
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
sameer2010Commented:
There is nothing wrong from SQL perspective. However, your logic would not insert all the values due to a check of 999 and i. The check should be
If (lngCount = 999) Or (lngHigh = i) Then

Open in new window

0
ArkCommented:
Why not using array and then join it?
    Dim arrValues() As String
    ReDim arrValues(998)
    lngLow = 55136
    lngHigh = 99997
    
    lngCount = 0
    
    strSQL = "INSERT INTO tblReservations (fldCity, fldReservComment) VALUES "
    
    For i = lngLow To lngHigh
    
        arrValues(lngCount) =  "(1, '" & i & "')"
        lngCount = lngCount + 1
        
        If (lngCount = 999) Or (i = lngHigh) Then
            If lngCount < 999 Then Redim Preserve arrValues(lngCount - 1)
            CurrentDb.Execute strSQL & Join(arrValues,",") & ";", dbSeeChanges
            lngCount = 0
            ReDim arrValues(998)
        End If
    
    Next

Open in new window

0
sameer2010Commented:
This executes singleton queries. Why are we not able to execute multiple statements or it is not supported through VBA?
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
Microsoft Access

From novice to tech pro — start learning today.