Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002

    Unanswered: Tip: Creating Access tables using SQL

    For one wacky reason or another, I'm writing a COM component for use in an ASP page that dynamically generates Microsoft Access tables based on various criteria.

    I've just spent an hour racking my brain and checking Google for the correct syntax to create an Autonumber field using a SQL CREATE TABLE statement and now I've found it, I figured it would be sensible to share it.

    Intuitively, you'd think the syntax would be something like CREATE TABLE MyTable (MyID autonumber primary key not null) but no...the keyword is "counter" not "autonumber". So:
    CREATE TABLE MyTable (
       MyID counter primary key not null
    I hope someone finds this useful and it saves them a wasted hour or two


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    this is covered in microsoft's online documentation, although i grant you that it is never easy to find what you're looking for on microsoft's site, simply because of the sheer volume of results you get in a search on words like "access" and "DDL"

    see Intermediate Microsoft Jet SQL for Access 2000

    the COUNTER data type is also mentioned in KB Article Q116145 ACC: Create and Drop Tables and Relationships Using SQL DDL -- apparently, COUNTER in DDL is valid as far back as Access Version 2.0


  3. #3
    Join Date
    Aug 2002
    Québec, Canada
    Thanks for the info

    But if you do a search in Access on the word "type", you'll find "data type comparison" (in french it is "Comparaison de types de données"). Here, ALL data types is included including the VBA keyword and DAO constant. You can see that COUNTER have a synonym, AUTOINCREMENT.

    In access, AutoIncrement can also be "random", but it is not in that page (someone know what it is?)


Posting Permissions

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