Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2006
    Posts
    34

    Unanswered: Runtime Error 3075 Syntax error (was "Help with SQL")

    Hi All, I wonder if anyone can tel me what is wrong with this ?

    Many Thanks in advance

    SQLmktbl = "SELECT (qrylkuporderlines.pcode, qrylkuporderlines.order_qty, qrylkuporderlines.order_id)"
    SQLmktbl = SQLmktb & " INTO tempdel IN '" & vartemppath & "'"
    SQLmktbl = SQLmktbl & " FROM qrylkuporderlines;"

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What are you trying to do with this query?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2006
    Posts
    34
    enter query results into table in another db. the errors I get apart from my typo is Runtime Error 3075 Syntax error (comma) in query expression.

    My debug.print is : SELECT (qrylkuporderlines.pcode, qrylkuporderlines.order_qty, qrylkuporderlines.supplierstockcode, qrylkuporderlines.order_id) INTO tempdel IN 'c:\temp.mdb' FROM qrylkuporderlines;

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Drop the parens ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Sep 2006
    Posts
    34
    Thanks but still no joy, now I get

    Runtime 3061
    Too few paramaters 1 expected

    Debug.print is now : SELECT qrylkuporderlines.pcode, qrylkuporderlines.order_qty, qrylkuporderlines.supplierstockcode, qrylkuporderlines.order_id INTO tempdel IN 'c:\temp.mdb' FROM qrylkuporderlines;

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Bit of a standard dao error. Copy and paste your sql into the sql pane of a query and try to execute. I bet either one of your fields is wrong or there is no c:\temp.mdb.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2006
    Posts
    34
    It executes in the sql pane fine, but still not in code.

    The only thing I can think of is that where qrylkuporderlines is a query and requires a field from the form that calls the code that I am having problems with.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by jamesf248
    Hi All, I wonder if anyone can tel me what is wrong with this ?

    Many Thanks in advance

    SQLmktbl = "SELECT (qrylkuporderlines.pcode, qrylkuporderlines.order_qty, qrylkuporderlines.order_id)"
    SQLmktbl = SQLmktb & " INTO tempdel IN '" & vartemppath & "'"
    SQLmktbl = SQLmktbl & " FROM qrylkuporderlines;"
    what actually are you seeing in SQLmktbl

    unless its a typo Im expecting to see
    " INTO tempdel IN '<vartemppath>' FROM qrylkuporderlines;"
    Id suggest changing
    SQLmktbl = SQLmktb & " INTO tempdel IN '" & vartemppath & "'"
    to
    SQLmktbl = SQLmktbl & " INTO tempdel IN '" & vartemppath & "'"

    if you are hitting problems with this sort of error I always think its a smart option to view the SQL in a messagebox...
    that way round you can see what you are actually sending as opposed to what you think you are sending

    incidentally are you using "option explicit" in front of all you modules... arent you?
    http://www.mvps.org/access/tencommandments.htm
    Last edited by healdem; 01-16-07 at 07:24.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2006
    Posts
    34
    What I actuall see is :

    SELECT qrylkuporderlines.pcode, qrylkuporderlines.order_qty, qrylkuporderlines.supplierstockcode, qrylkuporderlines.order_id INTO tempdel IN 'c:\temp.mdb' FROM qrylkuporderlines;

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jamesf248
    The only thing I can think of is that where qrylkuporderlines is a query and requires a field from the form that calls the code that I am having problems with.
    Sorry - yes that is your problem. You need to explicitly state your parameters in the query (Query -> parameters in the query buiilder as I recall).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2006
    Posts
    34
    The sql of the other query now looks like this:
    PARAMETERS [Forms]![frmdelivery1]![Combo3] Text ( 255 );
    SELECT order_itm.pcode, order_itm.order_qty, order_itm.supplierstockcode, forms!frmdelivery1!combo3 AS Order_ID
    FROM order_hdr INNER JOIN order_itm ON order_hdr.order_no = order_itm.order_no
    WHERE (((order_hdr.hoc_ref)=[Forms]![frmdelivery1]![Combo3]));

    But I am still getting the error, do I need to define the parameter in code as well.

  12. #12
    Join Date
    Sep 2006
    Posts
    265
    I have never got the parameter to work but try something along the lines of I've ommitted the brackets for clarity!!

    WHERE order_hdr.hoc_ref = '" & [Forms]![frmdelivery1]![Combo3] & '" ;

    or

    with CodeContextObject
    .....
    WHERE order_hdr.hoc_ref= '" & .[Combo3] & "';

Posting Permissions

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