Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: Create Table in VB

    Hi guys,

    I'm currently working on a function within Access to build temporary tables and queries.

    I've got as far as creating the table with all the correct field names and field types but what I would really like to do is to put a default values or validation rule into specific fields. For example; on the date field I want the default value to be now(), where as on the rating field I want the validation rule to show <6

    Is it possible to create a table in VB to this level of detail? And if so can someone advise how?

    My current code;

    Code:
        Dim dbs As Database
        Set dbs = CurrentDb
    
        dbs.Execute "CREATE TABLE " & Me.Val_CreateTableName & "(" _
            & "AutoID INTEGER constraint MyFieldConstraint unique Primary key, " _
            & "Response1 CHAR, " _
            & "Response2 CHAR, " _
            & "Date DATETIME " _
            & ");"
    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You cannot define a default value when you use DAO to execute SQL DDL statements, and CurrentDb is a reference to a DAO database. Use the ADODB library instead TO EXECUTE THE SQL DDL statement:
    Code:
    CurrentProject.Connection.Execute strSql
    Have a nice day!

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Thanks Sinndho. Admittedly my method of working is to scour the net and take apart bits of code until I can get it to do what I want hence the multiple methods I may use.

    Is it possible to define default values using this method?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The basic instruction to create a table and define its columns in SQL (Data Definition Language, or DDL) is:
    Code:
    CREATE TABLE TableName (ColumnName1 DataType (Size), ColumnName2 ...)
    (Size) is optional and should be used for certain data types only, such as: Text (50).


    When you define the columns, you can also specify some of their properties or constraints (NOT NULL, UNIQUE, etc.), such as:
    Code:
    CREATE TABLE TableName (ColumnName1 DataType (Size) NOT NULL, ColumnName2 ...)
    Or:
    Code:
    CREATE TABLE TableName (ColumnName1 DataType (Size) CONSTRAINT ConstraintName1 UNIQUE,
     ColumnName2 ...)

    PRIMARY KEY is a special type of constraint. It creates an index on the column and implies both the UNIQUE constraint and the NOT NULL property:
    Code:
    CREATE TABLE TableName (ColumnName1 DataType (Size) CONSTRAINT PrimaryKeyName PRIMARY KEY,
     ColumnName2 ...)
    Then, you don't need to specify NOT NULL and UNIQUE when you use PRIMARY KEY to define the PRIMARY KEY constraint.


    In SQL DDL, you can also define a DEFAULT constraint, that sets a default value for the column:
    Code:
    CREATE TABLE TableName (ColumnName1 DataType (Size)  CONSTRAINT ConstraintName1 DEFAULT (DefaultValue),
     ColumnName2 ...)
    Unfortunately this DEFAULT constraint does not work with DAO (Data Access Objects), the default database library used by Access, and this will fail:
    Code:
    Dim strSQL As String
    strSQL = "CREATE TABLE TableName (ColumnName1 LONG CONSTRAINT ConstraintName1 DEFAULT (0))"
    CurrentDb.Execute strSQL, dbFailOnError    ' CurrentDb is a reference to a DAO object.
    Notice that if you do not use dbFailOnError as the second parameter, no error will be raised, though the statement will not be executed.


    As an alternative, you can use the ADO (ActiveX Data Objects) that correctly recognizes and execute a SQL DDL instruction with CONSTRAINT DEFAULT clauses, so this will succeed:
    Code:
    Dim strSQL As String
    strSQL = "CREATE TABLE TableName (ColumnName1 LONG DEFAULT 0)"
    CurrentProject.Connection.Execute strSQL   ' Reference to an ADO object.
    Notice that for some odd reason and contrarily to the standard SQL syntax, the ADO Library considers that this is a property of the column, not a constraint, which explain why the CONSTRAINT ConstraintName part is missing.

    Notice also that these properties and constraints, as well as several others, can be defined later, when the table already exists, using the ALTER TABLE SQL DDL instruction.

    See also:
    Access Data Definition Language (DDL): Creating and Altering Tables On The Fly &mdash; DatabaseJournal.com
    Fundamental Microsoft Jet SQL for Access 2000
    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
  •