Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cannot open table with ADO (2003)

    If I try to run the Public Sub AddDays1() in Module1 in the attached mdb, the code stops at the line
    <font color=448800>rst.Open "Day 33", cnn, adOpenKeyset, adLockOptimistic</font color=448800>
    with the message:
    <font color=red>Run-time error '-2147217900 (80040e14)':
    Invalid SQL statement. Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.</font color=red>
    but I cannot make out what the cause of that is.

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

    Re: Cannot open table with ADO (2003)

    You must specify that "Day 33" is a table, not an SQL string:
    <pre>rst.Open "Day 33", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect</pre>


  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot open table with ADO (2003)

    Thank you Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot open table with ADO (2003)

    Hans,
    How come another table(Gorlago, please see Public Sub AddDays1() in the attached mdb) can be open without specifying adCmdTableDirect?
    rst.Open "gorlago", cnn, adOpenKeyset, adLockOptimistic

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

    Re: Cannot open table with ADO (2003)

    If you don't specify the Options argument to rst.Open, ADO tries to guess what the Source argument means. Because the name "Day 33" contains a space, it is interpreted as a command text (SQL string), while "Gorlago" (without spaces) is interpreted as a table name. An alternative would be to enclose the table name in square brackets:<pre>rst.Open "[Day 33]", cnn, adOpenKeyset, adLockOptimistic</pre>

    This forces ADO to see the name as a whole.

Posting Permissions

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