Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INsert SQL Statement (AccessXP)

    I keep getting a syntax error when I try to run the code below. However, if I paste the insert statement into a query, it works. What am I doing wrong?

    Dim oRecordset As New ADODB.Recordset
    Dim sSQL As String

    sSQL = "INSERT INTO PAYSUMM1 (WORK, WORKH, LEAVE, LEAVEH, lngEmployeeID) VALUES ("2,355.52", "52:30", "1,009.50", "22:30", 5)"
    oRecordset.Open sSQL, CurrentProject.Connection

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

    Re: INsert SQL Statement (AccessXP)

    The problem is that you have double quotes inside a string delimited by double quotes. this confuses the VB interpreter. If you paste into a query, you don't paste the " before INSERT and after the closing ), so the error doesn't occur.

    The easiest solution is to use single quotes within the string:<pre>sSQL = "INSERT ... VALUES ('2,355.52'; '52:30', '1,009.50', '22:30', 5)"</pre>

    BTW Are you inserting numeric and time values as strings intentionally?

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INsert SQL Statement (AccessXP)

    Hans,

    I am importing a large text file into a database to make a simple report. I am treating everything as text.
    The reason I didn't want to use single quotes is because some of the text fields have single quotes in them. Is there a way around this?

    Thanks

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

    Re: INsert SQL Statement (AccessXP)

    Instead of single quotes, you can use double double quotes:<pre>sSQL = "INSERT ... VALUES (""2,355.52""; ""52:30"", ""1,009.50"", ""22:30"", 5)"</pre>

    A pair of double quotes within a string is interpreted as a double quote. This will raise problems if some of the text fields contain double quotes.

  5. #5
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INsert SQL Statement (AccessXP)

    Hans,
    None of the text fields contain double quotes. However, now I am getting a syntax error (missing operator) on the first record! The SQL statement is:

    INSERT INTO HEADER (IFACE, RUNDATE, COMPANY, COMPANYNO) VALUES (""Field 1"", ""12-MAY-2003"", ""Value 3"", ""92661124436"")

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

    Re: INsert SQL Statement (AccessXP)

    Looks OK to me... Anyone else?

  7. #7
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INsert SQL Statement (AccessXP)

    Hans,

    I finally found the problem in the first statement. It was in the name of the field "Work". For some reason, this isn't acceptable. When I renamed it to "txtWork", then all was okay.

    It took me 2 days to figure it out! But I appreciate your input. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    Marie-Therese

Posting Permissions

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