Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Into Values SQL Syntax Error (2000 SP3)

    What is wrong with the syntax? I've tried multiple things....

    strSQL = "INSERT INTO [tblUpdatedBy] (RequestNbr, UserID, DateTimeStamp, Action) VALUES (" & intRequestNbr & ", " & strUserID & ", #" & Now() & "#, 'sometext' )"

    I get an error when I execute this SQL using ADOConnection.Execute, "Syntax Error in Insert Into Statement" This statement worked fine until I added the Action column to the mix. Below is what the statement looked like before the Action field was added.

    strSQL = "INSERT INTO [tblUpdatedBy] (RequestNbr, UserID, DateTimeStamp) VALUES (" & intRequestNbr & ", " & strUserID & ", #" & Now() & "#)"

    I just want to add some text to the Action field. The tblUpdatedBy is just a log of who added a new record at what time. Here is the whole Form_AfterInsert event:

    ================================================== ==
    Private Sub Form_AfterInsert()
    Dim cnn As ADODB.Connection
    Dim strUserID As String
    Dim intRequestNbr As Integer
    Dim strSQL As String

    strUserID = Chr(39) & acbNetworkUserName & Chr(39)
    intRequestNbr = Me.RequestNbr

    strSQL = "INSERT INTO [tblUpdatedBy] (RequestNbr, UserID, DateTimeStamp, Action) VALUES (" & intRequestNbr & ", " & strUserID & ", #" & Now() & "#, 'sometext' )"

    Debug.Print strSQL

    Set cnn = CurrentProject.Connection

    On Error GoTo Form_AfterInsert_Error
    cnn.Errors.Clear
    cnn.Execute strSQL
    Set cnn = Nothing

    Form_AfterInsert_Exit:
    Exit Sub

    Form_AfterInsert_Error:
    MsgBox "Error occured in the AfterInsert event of the form. " & vbLf & _
    "Error Nbr: " & Err.Number & vbLf & _
    "Error Description: " & Err.Description, vbOKOnly, "Error"
    Set cnn = Nothing
    Resume Form_AfterInsert_Exit

    End Sub
    ==========================================

    I've tried adding double quotes, single quotes, and no quotes around the value I want to insert in the VALUES portion of the SQL statement. I've read about the INSERT INTO SELECT statement, but I'm not inserting from any table, just variables and some hard coded text.

    What's wrong?

    Sarah

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert Into Values SQL Syntax Error (2000 SP3)

    You already have a Debug.Print statement in your code. Could you copy the output from a failed run in the Immediate window and post it into a reply?

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Into Values SQL Syntax Error (2000 SP3)

    Here it is.

    INSERT INTO [tblUpdatedBy] (RequestNbr, UserID, DateTimeStamp, Action) VALUES (9999, 'A1234', #01/29/2004 9:21:40 AM#, 'sometext')

    Here's the error I get is shown below. I have copy/paste this into the SQL of an Access query and it runs fine. I'm not sure what I'm missing....


    Sarah
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert Into Values SQL Syntax Error (2000 SP3)

    Apparently, ADO or SQL objects to the use of Action as a field name; you can get around this by enclosing it in square brackets:

    strSQL = "INSERT INTO [tblUpdatedBy] (RequestNbr, UserID, DateTimeStamp, [Action]) VALUES ...

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Into Values SQL Syntax Error (2000 SP3)

    Have the struserid not to be between quotes ? <pre>strSQL = "INSERT INTO [tblUpdatedBy] (RequestNbr, UserID, DateTimeStamp, Action)
    VALUES (" & intRequestNbr & ",'" & strUserID & "', #" & Now() & "#, 'sometext' )"
    </pre>

    Francois

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Into Values SQL Syntax Error (2000 SP3)

    That worked!

    Hadn't thought about the field name. I put Action between brackets [Action], and that worked without a hitch.

    Thanks Hans.
    Sarah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •