Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2011
    Posts
    6

    Unanswered: Name field is not allowed Access 2002

    Hello, I have a problem with an Access DB 2002. This DB has a table with a field called Date Cons. (including space and dot). I should make this query db with a program in java that I'm writing, but does not take into SELECT Date Cons. (the query fails) and I can not change the name because the DB is connected to another program that I can not change. How it works??
    Thanks for the help!!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Fields with inadvisable spaces and symbols need to be bracketed:

    SELECT [Date Cons.]
    Paul

  3. #3
    Join Date
    Jul 2011
    Posts
    6
    Thanks for the response but does not go! Now the error is: "" is not a valid name. Make sure that does not include invalid characters or punctuation and that is not too long. for the sake I ran the same query in Access (Access 2010) not in the program I'm writing in Java, and the error is the same!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The problem comes from the dot. If you cannot change the names in the database, you probably can create a query in the database (or have it created for you) which will replace the offending name using an alias:
    Code:
    SELECT ... [Date Cons.] AS Date_Cons FROM ...
    However you should first check the name of the field in the database: normally Access does not accept field names with dots ('.') in them and you get an error as soon as you try to create a field with such a name, at least in Access 2003.
    Attached Thumbnails Attached Thumbnails ScreenShot001.jpg  
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    6
    SELECT ... [Date Cons.] AS Date_Cons FROM ...
    does not work even with the alias.

    However you should first check the name of the field in the database: normally Access does not accept field names with dots ('.') in them and you get an error as soon as you try to create a field with such a name, at least in Access 2003.
    The column is called Dates Cons. I know it seems strange, but the dot is! Again, the DB was not created by me but is connected to a management software purchased, so I can not change the DB!

  6. #6
    Join Date
    Jul 2011
    Posts
    6
    One question: the error could also be due to Access 2010 or the drive that I use in java? (sun.jdbc.odbc.JdbcOdbcDriver)

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    According to me the first question is: what's the format of the original database (the one with a table having a column named 'Date Cons.'

    I've tried to create such a column in an Access database (.mdb and .accdb) but it seems to be impossible: nor using the Access interface, nor using DAO, nor using ADO, nor using RDO, nor using Jet SQL.

    SQL Server accepts [Date Cons.] as the name for a column (it automatically add the square brackets it you do not type them yourself) but you cannot link the table in Access nor import it: Access returns an error message with the error code 3125. If you search for explanations about this error you'll find that it is due to the fact that a name is invalid, comprises incorrect punctuation characters or is too long.

    If you try to import a csv file with the first line containing 'Date Cons.' in it and specify that the first line of the text file contains the name of the columns, Access will import the file but the dot '.' will be removed from the table created during the import process.

    Up to now, the only solution I can imagine would consist in creating a view in the original database that will substitute a valid alias to the offending name. Such a view would not interfere with the normal functions of the original application working with the database.
    Have a nice day!

  8. #8
    Join Date
    Jul 2011
    Posts
    6
    Sinndho thanks for the responses! I uploaded the db (obviously cleaned up and with only the offending table present) here MEGAUPLOAD - The leading online storage and file delivery service .
    Check yourself and see the point in the field Data Cons. in CONSEGNE table.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    but thats not the point
    the point is how the ... did you manage to create the columns with that name?
    it looks like you will have to recreate your schema
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Now I understand: the real name of the column in the table is 'DATAC'. 'Data Cons.' is just the caption that was specified for the column in the table definition. When you address the table in a Query or by code, you have to use the real name, not the caption.

    Pay attention to the fact that there are other columns in that table that have a defined caption. If you want to enumerate them, just paste the following code into a new module and run it:
    Code:
    Function EnumTableDefinition()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim pty As DAO.Property
        
        On Error Resume Next
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("CONSEGNE")
        For Each fld In tdf.Fields
            Debug.Print fld.Name, ,
            For Each pty In fld.Properties
                If pty.Name = "Caption" Then Debug.Print pty.Value;
            Next pty
            Debug.Print
        Next fld
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Function
    Attached Thumbnails Attached Thumbnails ScreenShot002.bmp  
    Have a nice day!

  11. #11
    Join Date
    Jul 2011
    Posts
    6
    Now I understand: the real name of the column in the table is 'DATAC'. 'Data Cons.' is just the caption that was specified for the column in the table definition.
    Thanks Sinndho you are my savior! Sorry ... how did you know that the real name is DATAC and "Data Cons." is just an alias?

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You just need to open the table in Design view to see it.
    Have a nice day!

Posting Permissions

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