Results 1 to 8 of 8

Thread: Table renaming

  1. #1
    Join Date
    Jan 2009
    Posts
    13

    Unanswered: Table renaming

    Accidentally when i started working on a very complicated database, i named a table with a space between eg: 'registry detailsgz'. As now the database is nearly finished, i made a form with the purpose to delete all the data from the table. i wrote this code:

    DoCmd.RunSQL "DELETE *from registry detailsgz;"

    but when i try to run the code i get the error "Microsoft jet database engine cannot find the input table or query 'registry'. Make sure it exists and that its name is spelled correctly"

    It is because there is the space in the above named table? The price for renaming the table again is too high. Is there something i need to do in the coding? Any help ppl?

    Thanks and regards

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can try enclosing the table name in []
    eg

    Code:
    DoCmd.RunSQL "DELETE *from [registry detailsgz];
    Code:
    select my, [spaced out column], in, my, column, list from [registry detailsgz];
    when it comes to referring to thoise columns in SQL
    [registry detailsgz].[spaced out column]
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You accidentally renamed a table in a complex database? How the *#$@ can you accidentally rename a table??

    Rename it back to what it was??! Seems like the most logical course of action.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Learn a lesson from this - All database objects should be named with a strict naming convention.

    There are several although one of the more commonly used is the 'Leszynski naming convention':-

    Microsoft Access Database Naming Conventions | Database Solutions for Microsoft Access | databasedev.co.uk

    By adhering to these guidelines, you will avoid problems like this in the future and find it easier to debug and develop your code.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Jan 2009
    Posts
    13
    This is what i wanted, thanks a lot Healdem

    Star, without offence, i started this database as an expiriment without knowing professional knowledge and ended up getting what i wanted in the first place. When i made the table for the first time, i made a space between. rename the table costs a lot of time.

    nice day mates

    Quote Originally Posted by healdem
    you can try enclosing the table name in []
    eg

    Code:
    DoCmd.RunSQL "DELETE *from [registry detailsgz];
    Code:
    select my, [spaced out column], in, my, column, list from [registry detailsgz];
    when it comes to referring to thoise columns in SQL
    [registry detailsgz].[spaced out column]

  6. #6
    Join Date
    Jan 2009
    Posts
    13
    Quote Originally Posted by garethdart
    Learn a lesson from this - All database objects should be named with a strict naming convention.

    There are several although one of the more commonly used is the 'Leszynski naming convention':-

    Microsoft Access Database Naming Conventions | Database Solutions for Microsoft Access | databasedev.co.uk

    By adhering to these guidelines, you will avoid problems like this in the future and find it easier to debug and develop your code.
    I learnt my lesson now after all, thats why we fall, to learn to pick ourselves up

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Naming conventions, that all time favourite....

    Im all in favour of qualifying the data type in variables inside a program (eg strSQL, intLoop, curPrice, but am wholly opposed to carrying that into the table and column definitions.

    why.. its redundant information, its ugly, but perhaps more importantly it also masks what the column is all about. when describing physical design to managers or users it doesn't make sense to the user. they can understand PostageRate, its harder to understand lngPostageRate. If you ever provide SQL access to users to generate reports it makes more sense to recognise the item rather than the data type, usually they an get the data defitnion formt he tael if they wish (or the documentation (if it hasn't been lost, or if it was ever written in the first place))

    yonks ago I developed/was introduced to a naming convention for column names. FWIW:-
    keep 'em simple
    I use camel case (eg ProdNo, rather thaen prodno)
    Stick to a limited range of abbreviations and be consistent (eg Address becomes Addr, Telephone No becomes TelNo, Product becomes Prod, and never ever allow confusion)
    for what are made up system values I use a suffix such as TYPE or CODE to indicate that the column is string, eg when defining Currencies I'd use Code as the PK
    I use ID or No for numerics (ID usually for system generated numbers), I'd generally use ID for the autogenerated column in a table, eg I'd use ID for Inovice or Product. if the Product number came form elsewhere Id use ProdNo or ExtProdNo
    I call tables the plural of the item it stores (eg table Products comprises lots of products)
    I don't replicate the table name in the table (Ie in table Products I wouldn't have a ProductName, I'd have Name, as in the SQL its self documenting as Products.Name, whereas Products.ProductName looks ugly
    Where the column is a Foreign Key I do usually add a prefix of the table whee its a PK (eg ID in products, becomes ProductID in OrderDetails)
    In the Access environment I do (usually) qualify the object type, but its a habit I'm trying to get rid of.

    but ultimately its whatever makes sense to you and your fellow developers.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have have my own naming conventions. L/R can jump in the lake
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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