Microsoft Access tips DDL Programming Code Examples Examples. Provided by Allen Browne. Created March 2. Last Updated August 2. The SQL standard has sublanguages such as Data Manipulation Language DML SELECT statements and action queries DELETE, UPDATE, INSERT INTO,. Data Definition Language DDL Statements that alter the schema changing the tables, fields, indexes, relations, queries, etc. Object. Type Table. Query. Linked Table. Form 3. 27. 68. Report 3. Module 3. 27. 61. Using DML queries, you can read some aspects of the database schema. You can list the objects in an Access database like this   SELECT MSys. Create Desktop Database Invoice in MS Access 2010. How to Use Microsoft Access for Invoicing Simple Steps MS Access Form Templates. Forms in MS Access act as user. Examples Of Microsoft Access Database' title='Examples Of Microsoft Access Database' />Objects. Type, MSys. Objects. Name. FROM MSys. Objects. WHERE MSys. The Microsoft Access Contact Database is a full featured Microsoft Access Database Template that allows for viewing and creating Contact data in a Microsoft Outlook. Microsoft Access free download Download Access database examples demonstrating usefull programming techniques. Downloaded databases include How Tos for Visual Basic. AccessForums. net is a forum dedicated to Microsoft Access, if you want to ask any Access related questions or help other members out, please join our community, the. Database Solutions Downloads for Microsoft Access Free downloadable Microsoft Access 97, 2000, XP and 2003 Database Files and Sample database examples for you to. Objects. Name Not Like ORDER BY MSys. Objects. Type, MSys. Objects. Name where Type will be one of the values in the table at right. Unfortunately, DML provides no easy way to read the field names in a table. DDL provides another whole range of query statements such as CREATE TABLE to generate a new table, specifying field names, types, and constraints. Examples Of Microsoft Access Database' title='Examples Of Microsoft Access Database' />ALTER TABLE to add a column to a table, delete a column, or change a columns data type or size. DROP TABLE to delete a table. Similarly, you can CREATEALTERDROP other things such as indexes, constraints, views and procedures queries, users and groups security. While DDL is important in some larger databases, it is of limited use in Access. You can create a Text field, but you cannot set the Allow Zero Length property to No, so any query criteria must test for both Nulls and zero length strings. You can create a YesNo field, but you get a text box, not a check box, since you cannot set the Display Control property with DDL. You can create a DateTime field, but you cannot set the Format property. DDL cannot create Hyperlink fields, Attachment fields, or the complex data types at all. Ultimately, you execute your DDL query under DAO or ADO. For DAO, use    db. Engine00. Execute str. Sql, db. Fail. On. Error. For ADO, use    Current. Project. Connection. Execute str. Sql. Some features of JET 4 Access 2. ADO only. These queries fail if you paste the SQL statement into the query designer in Access, since the Access interface uses DAO. One case where DDL is really useful it to change a fields data type or size. You cannot do this in DAO or ADOX, so DDL is your the only practical approach other than copying everything to another field and deleting the old one. Other than that, Access developers use DDL infrequently. See the field type reference for a comparison of the field type names in DDL compared to the Access interface and libraries. Heres some examples to get you started if you need DDL. Option Compare Database. Option Explicit. Sub Create. Table. DDL. Dim cmd As New ADODB. Command. Dim str. Sql As String. cmd. Active. Connection Current. Project. Connection. Sql CREATE TABLE tbl. Ddl. Contractor. Contractor. ID COUNTER CONSTRAINT Primary. Key PRIMARY KEY,. Surname TEXT3. 0 WITH COMP NOT NULL,. First. Name TEXT2. WITH COMP,. Inactive YESNO,. Hourly. Fee CURRENCY DEFAULT 0,. Penalty. Rate DOUBLE,. Birth. Date DATE,. Entered. On DATE DEFAULT Now,. Notes MEMO,. CONSTRAINT Full. Name UNIQUE Surname, First. Name. cmd. Command. Text str. Sql. cmd. Execute. Debug. Print tbl. Ddl. Contractor created. Sql CREATE TABLE tbl. Ddl. Booking. Booking. ID COUNTER CONSTRAINT Primary. Key PRIMARY KEY,. Booking. Date DATE CONSTRAINT Booking. Date UNIQUE,. Contractor. ID LONG REFERENCES tbl. Ddl. Contractor Contractor. Starcraft Map Cracker. ID. ON DELETE SET NULL,. Booking. Fee CURRENCY,. Booking. Note TEXT 2. WITH COMP NOT NULL. Command. Text str. Sql. cmd. Execute. Debug. Print tbl. Ddl. Booking created. Sub Create. Field. DDL. Dim str. Sql As String. Dim db As DAO. Database. Set db Current. Db. Sql ALTER TABLE My. Table ADD COLUMN My. New. Text. Field TEXT 5. Execute str. Sql, db. Fail. On. Error. Set db Nothing. Debug. Print My. New. Text. Field added to My. Table. Function Create. Field. DDL2. Dim str. Sql As String. Dim db As DAO. Database. Set db Current. Db. str. Sql ALTER TABLE Table IN C Datajunk. ADD COLUMN My. New. Field TEXT 5. Execute str. Sql, db. Fail. On. Error. Set db Nothing. Debug. Print My. New. Field added. Function Create. View. DDL. Dim str. Sql As String. Sql CREATE VIEW qry. SELECT tbl. Invoice. Invoice. Current. Project. Connection. Execute str. Sql. Sub Drop. Field. DDL. Dim str. Sql As String. Sql ALTER TABLE My. Table DROP COLUMN Delete. Me. DBEngine00. Execute str. Sql, db. Fail. On. Error. Sub Modify. Field. DDL. Dim str. Sql As String. str. Sql ALTER TABLE My. Table ALTER COLUMN My. Text. 2Change TEXT1. DBEngine00. Execute str. Sql, db. Fail. On. Error. Function Adjust. Auto. Num. Dim str. Sql As String. str. Sql ALTER TABLE My. Table ALTER COLUMN ID COUNTER 1. Current. Project. Connection. Execute str. Sql. Function Default. ZLS. Dim str. Sql As String. Sql ALTER TABLE My. Table ADD COLUMN My. ZLSfield TEXT 1. DEFAULT. Current. Project. Connection. Execute str.