Run-time error '-2147217900(80040e14)' Invalid SQL Statement

[Rick Stanich]

I am too new to figure this out. (Code was supplied by a member of this forum, thank you for all your help ).
In the code below it errors on one specific line in Red.
The line in Green I added, seems there is/was a missing end if. Best assumption of where the end if should be.

Public Sub transferdata()
'set up connection
    Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection

    Dim mytbl As AccessObject
    'set up variables to hold the parsed values of the table name holding imported data
    Dim mypart As String
    Dim myrev As String
    Dim myjob As String
    Dim myop As String
    Dim mysn As String
    'set up variable that will hold the position number of the spaces in the table names
    Dim firstspaceposition As Long
    Dim secondspaceposition As Long
    Dim thirdspaceposition As Long
    Dim fourthspaceposition As Long
    Dim fifthspaceposition As Long
    'loop through the table names to find the imported tables; ignore system tables and tables beginning with tbl
    For Each mytbl In CurrentData.AllTables
        If Not Left(mytbl.Name, 4) = "Msys" Then
            If Not Left(mytbl.Name, 3) = "tbl" Then
                'if an imported table is found, parse out the names into their respective variables
                firstspaceposition = InStr(1, mytbl.Name, " ")
                secondspaceposition = InStr(firstspaceposition + 1, mytbl.Name, " ")
                thirdspaceposition = InStr(secondspaceposition + 1, mytbl.Name, " ")
                fourthspaceposition = InStr(thirdspaceposition + 1, mytbl.Name, " ")
                fifthspaceposition = InStr(fourthspaceposition + 1, mytbl.Name, " ")
                mypart = Mid(mytbl.Name, 1, firstspaceposition)
                myrev = Mid(mytbl.Name, firstspaceposition + 1, secondspaceposition - 1 - firstspaceposition + 1)
                myop = Mid(mytbl.Name, secondspaceposition + 1, thirdspaceposition - 1 - secondspaceposition + 1)
                myjob = Mid(mytbl.Name, thirdspaceposition + 1, fourthspaceposition - 1 - thirdspaceposition + 1)
                mysn = Mid(mytbl.Name, fourthspaceposition + 1, fifthspaceposition - 1 - fourthspaceposition + 1)
                'check to see if the job has been previously created if so get pk; if not create new
                If DCount("*", "tblJobs", "txtJobNo='" & myjob & "'") > 0 Then
                    holdJobpk = DLookup("pkJobID", "tblJobs", "txtJobNo='" & myjob & "'")
                    holdPartIDpk = DLookup("pkPartID", "tblParts", "txtPartNo='" & mypart & "'")
                    holdPartRevIDpk = DLookup("pkPartRevID", "tblPartRev", "txtRev='" & myrev & "'")
                Else
                    Dim myrecset1 As New ADODB.Recordset
                    myrecset1.ActiveConnection = cnn1
                    myrecset1.Open "tblJobs", , adOpenDynamic, adLockOptimistic
                    With myrecset1
                        .AddNew
                        !txtJobNo = myjob
                        holdJobpk = !pkJobID
                        .Update
                        .Close
                    End With
                                        
                    myrecset1.Open "tblParts", , adOpenDynamic, adLockOptimistic
                    With myrecset1
                        .AddNew
                        !txtPartNo = mypart
                        holdPartIDpk = !pkPartID
                        .Update
                        .Close
                    End With
                    Set myrecset1 = Nothing
                    
                    [COLOR=red]myrecset1.Open "tblPartRev", , adOpenDynamic, adLockOptimistic[/COLOR]
                    With myrecset1
                        .AddNew
                        
                        !txtRev = myrev
                        
                        .Update
                        .Close
                    End With
                    Set myrecset1 = Nothing
                    
                End If
            End If
            'reset the variables for the next table
            firstspaceposition = 0
            secondspaceposition = 0
            thirdspaceposition = 0
            fourthspaceposition = 0
            fifthspaceposition = 0
            mypart = ""
            myrev = ""
            myjob = ""
            myop = ""
            mysn = ""
        [COLOR=lime]End If    'added 06.09.09[/COLOR]
    Next mytbl

End Sub

The actual error:

Run-time error '-2147217900(80040e14)'
Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

If I can get past this problem I should be able to make progress with the rest of the data transfer by modifying the code above. Hopefully it will be a good training excersize.

Any hints, tips or examples are appreciated.

 

[gemma-the-husky]

is this what your code realy looks like, or is it indented

its really hard to see things like unpaired if ... endifs without indentation.

it should be realtively easy.

however an unpaired if/end if should give you a compile error- so it may well be what it says - a sql statement error.

 

[Rick Stanich]

Wierd. I re-pasted the code indented and the forum changes it to unindented.

 

[Rick Stanich]

HA!
I had a bad table name.

 

[gemma-the-husky]

In the forums, to show code as indented when posting, you need to go to advanced tab, and mark the code segment as "CODE" (the # symbol)

 

[Rick Stanich]

Thanks
I would have picked up on that eventually, Im quick like that.