[RESOLVED] Syntax error in FROM clause Error Msg

[manofsteel00]

I have a VB6 program that interfaces with a MS Access 2002 database. I am getting a "Runtime Error '2147217900 (80040e14)' Syntax error in FROM clause". I am out putting the sql in a message box and it is: SELECT * FROM [ALTER] WHERE [PROD] = 292611 I have verified that the product number 292611 is, in fact, in the Alter table of the CycleCount database. I looked for this code on the internet and I kept getting back that somehow it came across a NULL value. If anyone can help I would be greatly appreciative. Here's my code:

'*INALTER()********************************************************************
'Name: inALTER()
'Desc: Returns true if the item is in the ALTER table, false otherwise
Private Function inAlter(PROD As Double) As Boolean
    Dim sql As String
    
    Set newConnection = New ADODB.connection
    newConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= C:\Documents and Settings\p6b0438\My Documents\CycleCount\DB\CycleCount.mdb"
    newConnection.Open
    Set newRecordSet = New ADODB.recordSet
    
    sql = "SELECT * " & _
          "FROM [ALTER] " & _
          "WHERE [PROD] = " & PROD
MsgBox (sql)
    newRecordSet.Open sql, newConnection, adOpenKeyset, adLockPessimistic, adCmdTable
Debug.Print sql
    
    If (newRecordSet.RecordCount > 0) Then
        inAlter = True
    ElseIf (newRecordSet.RecordCount = 0) Then
        inAlter = False
    Else
        MsgBox ("INALTER(): Unable to retrieve data.")
        Exit Function
    End If
    
    newRecordSet.Close
    newConnection.Close
    Set newRecordSet = Nothing
    Set newConnection = Nothing
End Function
'*ENDOF*INALTER()**************************************************************

[brucevde]

When opening the recordset, you need to specify adCmdText not adCmdTable.

When you use adCmdTable, the Source argument must be a Table name not a SQL statement.

[manofsteel00]

Now that's embarassing, I should have known that. Thanks!