Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013

    Unanswered: Strange problem with INSERT

    Dim cn As ADODB.Connection
    Set cn = CurrentProject.Connection
    car = "b"
    nbr = 1652
    SQL = "INSERT INTO caracteres (car, nbr) VALUES ('" + Replace(car, "'", "''") + "', " + CStr(nbr) + ")"
    cn.Execute SQL, NumOfRec
    Debug.Print "INSERT " + car + " " + CStr(NumOfRec)
    Debug.Print SQL
    INSERT b 1
    INSERT INTO caracteres (car, nbr) VALUES ('b', 1652)

    But, in the table, I get this :
    car = b
    nbr = 1

    In fact, I have an idear, but don't know how to do with it.

    Because later in the code, I do a UPDATE with car="B" and nbr=1 WHERE car='B'

    Is there a syntax like :
    WHERE BinaryComp(car,"B")... ?

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    what datatype is nbr in the underlying table
    Im not sure why you are converting nbr to string
    reaplce isn't a fully supported function in JET databases, it works OK in Access A2000 and on, but it doesn't work in VB talking to JET

    incidentally its not a smart call in my books to name your variables to the same as the column names, it can cause confusion
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    I'm using Access 2010 *.mdb file
    nbr field is type Long Integer

    The problem is there is no difference with
    INSERT ... WHERE car="b"
    INSERT ... WHERE car="B"

    But still don't know how to do.

  4. #4
    Join Date
    Mar 2013
    I did that and it works
    SQL = "UPDATE caracteres SET nbr=" + nbr + "  WHERE AscW(car)=" + CStr(AscW(car))
    But, I'm still wondering how to manage CASE with Access SQL ?

Posting Permissions

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