Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Now what's Wrong with This SQL Statement?!? (VB6)

    I think I need to take up another field of study. The following code raises an error "Syntax Error in INSERT INTO statement". When I print it in the Debug window and paste it into the Access 2K2 database it runs just fine:

    Const cmConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=chocisvol2FIN_SHRPushStatsPush.mdb;"


    cn.Open cmConnStr

    cn.Execute ("INSERT INTO tblINIStat " _
    & "(ComputerName, INIValue, Action) " _
    & "VALUES ('" & sCompName & "', " _
    & "'" & siniValue & "', " _
    & "'" & sAction & "')"), , adCmdText

    Here is what prints out in the debug window when pasting the above in:

    INSERT INTO tblINIStat (ComputerName, INIValue, Action) VALUES ('ID001641-ISD', 'ID001641-ISD', 'No Change Necessary')

    Pasting this in the SQL window of an Access Database executes correctly.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Now what's Wrong with This SQL Statement?!? (VB6)

    Hi Mike,
    Do you have a reference set to one of the ADO libraries?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Now what's Wrong with This SQL Statement?!? (VB6)

    I don't see anything wrong with it. Have you tried assigning the commandtext to a variable and then using the variable in the Execute statement? Perhaps it doesn't like the parens around the commandtext.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now what's Wrong with This SQL Statement?!? (VB6)

    Thank you everyone for your replies.

    I did figure it out, after trying each field one at at time, it had a problem with calling one of the filed names "Action". I am assuming this is a reserved word somewhere. But simply changing the name of the column in the table (and corresponding code) it runs great.

Posting Permissions

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