Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    create table wityh code (Access 2000)

    I use the following code the create a table called MyTable:


    dbs.Execute " CREATE TABLE MyTable (orderid INTEGER CONSTRAINT MyFieldConstraint PRIMARY KEY ," & _
    " FirstName TEXT, LastName TEXT)"
    dbs.Close


    However i obtain an orderid with a field Number, and i want the field to be Autonumber.How can i achieve that?
    Also, what is the constant for the date, since i have a field called OrderDate?. Is there an easier way to build a table throuch code?

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

    Re: create table wityh code (Access 2000)

    You can use AUTOINCREMENT for an AutoNumber field and DATETIME for a date/time field:

    dbs.Execute "CREATE TABLE MyTable " & _
    "(OrderID AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY, " & _
    "FirstName TEXT (20), LastName TEXT (50), OrderDate DATETIME);"

    You can also use DAO. It's a lot more work:

    ' Declarations
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim idx As DAO.Index
    ' Current database
    Set dbs = CurrentDb
    ' Create table
    Set tdf = dbs.CreateTableDef("MyTable")
    ' Create field
    Set fld = tdf.CreateField("OrderID", dbLong)
    ' Make it AutoNumber
    fld.Attributes = dbAutoIncrField
    ' Append to field list
    tdf.Fields.Append fld
    ' Create field
    Set fld = tdf.CreateField("FirstName", dbText, 20)
    ' Append to field list
    tdf.Fields.Append fld
    ' Create field
    Set fld = tdf.CreateField("LastName", dbText, 50)
    ' Append to field list
    tdf.Fields.Append fld
    tdf.Fields.Append fld
    ' Create field
    Set fld = tdf.CreateField("OrderDate", dbDate)
    ' Append to field list
    tdf.Fields.Append fld
    ' Refresh field list
    tdf.Fields.Refresh
    ' Create index
    Set idx = tdf.CreateIndex("PrimaryKey")
    ' Field for index
    Set fld = idx.CreateField("OrderID", dbLong)
    ' Append field to list
    idx.Fields.Append fld
    ' Append index
    tdf.Indexes.Append idx
    ' Refresh list of indexes
    tdf.Indexes.Refresh
    ' Append table to database
    dbs.TableDefs.Append tdf
    ' Refresh list of tables
    dbs.TableDefs.Refresh
    ' Release memory
    Set idx = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create table wityh code (Access 2000)

    dbs.Execute "CREATE TABLE MyTable " & _
    "(OrderID AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY, " & _
    "FirstName TEXT (20), LastName TEXT (50), OrderDate DATETIME);"
    Please help again !

    Thank you very much for your reply.It works excellent.Now i somehow cannot create the fields for Number.
    I have to create a field called freight with field size double and PaymentMethod with a field size Long Integer.
    Also, is it possible ti have the field "Freight" indexed?(not to allow duplicates)
    I somehow cannot continue with these fields in your abocve code.Otherwise, without my addition, your code works without problems.

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

    Re: create table wityh code (Access 2000)

    Try

    dbs.Execute "CREATE TABLE MyTable " & _
    "(OrderID AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY, " & _
    "FirstName TEXT (20), LastName TEXT (50), OrderDate DATETIME, " & _
    "Freight DOUBLE CONSTRAINT SecondaryKey UNIQUE, PaymentMethod LONG);"

    I still use Access 97. In the Help Index, if I type "Reserved words" (without the quotes), or "SQL, Reserved words", I get an overview of reserved words as hyperlinks. If I then click for instance DATETIME or LONG, I get a list of SQL data types. But I gather that help in Access 2000 is less useful.

Posting Permissions

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