Ms Access: Setting Table Column Caption or Description in Ddl

MS Access: setting table column Caption or Description in DDL?

Use DAO to set Caption and Description properties as Andrea suggested. Just be aware that both Caption and Description are user-defined, not default properties ... meaning that they don't exist before you assign them a value.

For example the following statement triggers error 3270, 'Property not found', because I have not assigned a Description for the id field:

Debug.Print CurrentDb.TableDefs("tblFoo").Fields("id").Properties("Description")

In order to assign a Description value for the field, I would first have to CreateProperty for "Description" then Append the new property to the field's Properties collection. Rather than write code to show you, I will suggest you use Allen Browne's SetPropertyDAO function. That function will handle the details for you. Be sure to grab the HasProperty function, too, because it is called by SetPropertyDAO. (The code for HasProperty is on the same web page, immediately below the SetPropertyDAO function code.)

Creating a table using SQL In Access

Setting Yes/No

Setting yes/no is done by adding a NOT NULL constraint to the field definition:

CREATE TABLE IceCream (
    Flavor TEXT(25) NOT NULL
);

---

Setting Caption

There doesn't seem to be a way to set this using SQL. See MS Access: setting table column Caption or Description in DDL?.

---

Primary Key

The primary key can be set using the PRIMARY KEY clause on a single field, or as a separate CONSTRAINT definition. If you have a multi-field primary key, you must use the second form.

CREATE TABLE IceCream (
    Flavor TEXT(25) PRIMARY KEY
);

CREATE TABLE Sundae (
    Name   TEXT(25),
    Flavor TEXT(25),
    CONSTRAINT PK_Sundae
        PRIMARY KEY (Name)
);

---

Foreign Key

The foreign key can be set with a CONSTRAINT definition:

CREATE TABLE IceCream (
    Flavor TEXT(25) PRIMARY KEY
);

CREATE TABLE Sundae (
    Name   TEXT(25) PRIMARY KEY,
    Flavor TEXT(25),
    CONSTRAINT FK_Sundae_IceCream
        FOREIGN KEY (Flavor)
        REFERENCES IceCream (Flavor)
);

---

Disclaimer: These are not examples of proper database design (normalization and such). They're just examples I made up on the spot.

---

References

• CREATE TABLE Statement (Microsoft Access SQL)
• CONSTRAINT Clause (Microsoft Access SQL)

Add caption to access fields with c#

Caption has no relevance outside of MS Access, however, here are some notes using interop:

 using Microsoft.Office.Interop.Access.Dao;

        DBEngine dbEng = new DBEngine();
        Workspace ws = dbEng.CreateWorkspace("", "admin", "",
            WorkspaceTypeEnum.dbUseJet);
        Database db = ws.OpenDatabase(@"z:\docs\test.accdb", false, false, "");
        TableDef tdf = db.TableDefs["Table1"];
        Field fld = tdf.Fields["Field1"];

        Property prp = fld.CreateProperty("Caption", 10, "My caption");

        fld.Properties.Append(prp);

how to add new column in ms access table with this criteria

This SQL will make a new table called tblFooC

SELECT tblFooB.TextB, nz([NumA],0) AS Expr1 INTO tblFooC
FROM tblFooB LEFT JOIN tblFooA ON tblFooB.TextB = tblFooA.TextA;

You could then delete table tblFooB and rename tblFooC to tblFooB

Creating table with column name (having spaces in between)

You need to add [] brackets to column name.

CREATE TABLE IDE_Dump
(
   Name VARCHAR(255),
   [Head Name] VARCHAR(255),
   [Parent Account] VARCHAR(255) 
);

Or you can use double quotes "" as jarlh commented:

CREATE TABLE IDE_Dump
(
   Name VARCHAR(255),
   "Head Name" VARCHAR(255),
   "Parent Account" VARCHAR(255) 
);

---

Related Topics