Results 1 to 8 of 8
  1. #1
    aaron47 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    11

    Question bad syntax error on sql statement used in a openrecordset statement

    I have the following code in a Virtual Basic module being used in an Access form to get data from a linked table with an ODBC connection.

    Criteria = "[CDATE] >= " & parmFromDate & " And [CDATE] <= " & parmToDate
    Sel_stmt = "Select * From SYSADM.DB_A02 Where " & Criteria

    Set ODBC_Workspace = CreateWorkspace("ODBC_Workspace01", "CQ11", "", dbUseODBC)
    Set DB = DBEngine.Workspaces(0).OpenDatabase(CurrentDb().Na me)
    Set ODBC_Connection = ODBC_Workspace.OpenConnection("ODBC_Connection01", _
    dbDriverPrompt, True)
    ODBC_Connection.QueryTimeout = 0


    'Debug.Print ODBC_Connection.Connect
    'Set RS_In = ODBC_Connection.OpenRecordset("SYSADM.DB_A02", dbOpenForwardOnly)
    Set RS_In = ODBC_Connection.OpenRecordset(Sel_stmt, dbOpenForwardOnly)
    Set RS_Out = DB.OpenRecordset("tblStatisticalCounts", DB_OPEN_DYNASET)

    When I execute the form, I get an ODBC call failed. While debugging, I discovered that the statement in red is erroring out with a bad syntax error and the words "Cursor for " have been added to what was moved to Sel_stmt. I am trying to learn this stuff but I don't have a lot of resources at my office to which to turn. Can somebody out there please help me?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi

    I don't know if this helps and it is offered without any great expectations of success but as far as I know variables that are holding date values need a # at the begining and end. So maybe this line
    Code:
    Criteria = "[CDATE] >= " & parmFromDate & " And [CDATE] <= " & parmToDate
    Should be changed to
    Code:
    Criteria = "[CDATE] >= #" & parmFromDate & "# And [CDATE] <= #" & parmToDate & "#"
    Just my two pennies worth but it may be worth trying.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    aaron47 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    11
    Bob Fitz,

    I made the suggested change and executed the form again but it still errored out. Here is the exact error that I am getting:
    -20
    S1000: [CA][ODBC CA-Datacom/DB Driver][CA-DATACOM/DB] BAD SYNTAX: <SQL_CUR_CA_1 CURSOR FOR SELECT * FROM SYSADM.DB_A02 WHERE [C

    In the error message, the string in Sel_stmt has been truncated but I see the full string when I display the field in the debug window. Do you have any further suggestions that I can try?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Take the criteria out altogether and see if you can get through it.

    I'll point out that if you've already got the table linked into access you can simplify your code

    Code:
    dim db
    dim rst 
    
    set db= currentdb
    set rst=db.openrecordset("<table or query name>")
    
    rst.movefirst
    do while rst.eof <> true
       <do what you want to do with the record>
       rst.movenext
    loop

  5. #5
    aaron47 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    11
    Yes, taking out the criteria altogether made the openrecordset in red in my original post work but I really need to limit my recordset. The linked table already has millions of records in it and is constantly being updated. I am new to Access but I have some experience with SQL and the resulting SQL looks right to me. Is there something I am missing? Also, since the table is constantly changing, I need the link to the table in Access to be refreshed every time the request is made.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If the table is truly linked you don't need to refresh the data, Access will see the most recent information when your data is touched.

    Try for your criteria a static value to start

    and use the criteria:


    "((CDATE) between #1/1/2011# and #1/31/2011#)"

    and see if you get a result.

    If that works try inserting your search values:

    "((CDATE) between #" & parmfromdate & "# and #" & parmtodate & "#)"

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Oh, one more very important thing

    CDATE is a reserved word

    cdate changes a text value to a date field, you may be experiencing some trouble because of it. You may have to reference the field by stating the tablename along with it

    for instance

    ([tablename].[cdate]) between blah blah

  8. #8
    aaron47 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    11

    solved

    Thank you for your help. After trying the suggestion of doing a static value in the criteria field, I discovered the true issue was with the fact that the table field name that I was referencing CDATE is a reserved word and after prefixing the field name with the table name, the code worked and I got the results I needed.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. UPDATE Statement Correct Syntax
    By OMGsh Y did I say Yes in forum Forms
    Replies: 2
    Last Post: 02-23-2011, 01:42 PM
  2. Case Statement Syntax
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 11-16-2010, 07:18 PM
  3. IIf Statement Syntax Error
    By shexe in forum Queries
    Replies: 2
    Last Post: 10-06-2010, 08:35 AM
  4. Select statement syntax?
    By ksmith in forum Programming
    Replies: 3
    Last Post: 06-24-2010, 09:21 AM
  5. Syntax Error In Insert Into Statement
    By frankvfox in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums