Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Switzerland
    Posts
    2

    Unanswered: SQL-Statement in VB

    Hi Everyone

    this seems to be a simple question, but the below sql-code isnt working.
    I cant seem to insert a Long-Variable into a Access-table:


    VBA codings...
    ...DoCmd.RunSQL ("INSERT INTO SPRQ(FieldLong) Values('" & ValueLong & "')") ...
    ...codings VBA

    Can anyone help?
    thx alot in advance!

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i'm not familiar with that syntax of a SQL statement, i would use

    runsql("INSERT INTO <TableName>(<comma delimited field list) select <comma delimited value list>;")

    ie
    DoCmd.RunSQL ("INSERT INTO SPRQ(<field>) select '" & ValueLong & "';")
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Apr 2003
    Location
    UK
    Posts
    11

    Thumbs up Re: SQL-Statement in VB

    Originally posted by atish_s
    Hi Everyone

    this seems to be a simple question, but the below sql-code isnt working.
    I cant seem to insert a Long-Variable into a Access-table:


    VBA codings...
    ...DoCmd.RunSQL ("INSERT INTO SPRQ(FieldLong) Values('" & ValueLong & "')") ...
    ...codings VBA

    Can anyone help?
    thx alot in advance!
    The basic synax is correct but for one small error. The resulting SQL string has the value surrounded in single quotes. ACCESS has to have double quotes for string values, so the statement will run correctly if you change all the double quotes to single, and single quotes to double.

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Re: SQL-Statement in VB

    Originally posted by Spudly
    The basic synax is correct but for one small error. The resulting SQL string has the value surrounded in single quotes. ACCESS has to have double quotes for string values, so the statement will run correctly if you change all the double quotes to single, and single quotes to double.
    atm this is the sql string as access gives it to sql
    INSERT INTO SPRQ(FieldLong) Values('ValueLong')

    if he swaps the quoteing around it wont work at all as VB will read it as
    DoCmd.RunSQL (

    everything after the ' is concidered a remark

    if the above syntax is right as you say the only thing that i can see missing is the ;
    Last edited by m.timoney; 05-21-03 at 10:08.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Apr 2003
    Location
    UK
    Posts
    11

    Re: SQL-Statement in VB

    Originally posted by m.timoney
    atm this is the sql string as access gives it to sql
    INSERT INTO SPRQ(FieldLong) Values('ValueLong')

    if he swaps the quoteing around it wont work at all as VB will read it as
    DoCmd.RunSQL (

    everything after the ' is concidered a remark

    if the above syntax is right as you say the only thing that i can see missing is the ;
    Appologies for not checking the syntax before posting. I've spent too much time coding Java and C# recently!

    m.timoney was right in that it would not work, but the following code will.

    DoCmd.RunSQL ("INSERT INTO SPRQ(FieldLong) Values(""" & ValueLong & """)")

    Sorry for the confusion

Posting Permissions

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