Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Naming Tables - error 3131 (access97/SR2)

    I'm flummoxed.

    My Access97/VBA code loops through all non-system tables in a database (and loops through all fields in that table).

    The tables were created by importing TXT files, and one of the tables generated errors. My code baulks, apparently because of the name of this table.

    When the table name is : T1-Main_Errors (note the hyphen!)

    I get: Run-time error '3131' Syntax error in FROM clause

    on the following line of code:
    <pre> Set rstOpenRecordset = dbs.OpenRecordset(strSelect)
    </pre>


    where:
    ?strselect
    SELECT * from T1-Main_Errors




    When the table name is : T1Main_Errors (note the lack of hyphen!)

    the code runs to completion (looping through all four tables - 3 data and one error)



    (From the Access97 Help files)

    Guidelines for naming fields, controls, and objects

    Names of fields, controls, and objects in Microsoft Access:

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming Tables - error 3131 (access97/SR2)

    Chris,

    When you use spaces or operators in the name of an object (wich is a bad pratcice in my opinion), you must put the name of that object between brackets to refere to it.

    SELECT * from [T1-Main_Errors]

    will do it.
    Francois

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming Tables - error 3131 (access97/SR2)

    > put the name of that object between brackets

    Thenk Yew!


    >which is a bad pratcice in my opinion

    I agree, but in this case I have no choice. I'm doing a survey on a clien't data and haven't the authority (or the desire right now) to change things. Later, when I get hold of the operation.

    Also, to cater for this and any future contracts, I'm developing a generalised data base cleanser, table-driven in that the use and I supply a Table in a Word Document and the cleanser applies the rules to the nominated database file.

    Thanks again for the tip on names. A simple
    If left-most char of strTable is not [ then
    strTable = "[" & strTable & "]"
    did the trick.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Naming Tables - error 3131 (access97/SR2)

    Chris,

    Why not just use brackets all the time? It doesn't hurt anything when the table names don't require it, and it will be there when they do. No need to test anything. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    As an afterthought, you might want to do the same thing with fields, if you haven't already done so. Anyone who puts spaces and hyphens into table names is likely to use them in field names as well.
    Charlotte

Posting Permissions

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