Example 5: Create a New Database Table using ADO, with SQL statements. The ACE Provider can be used for both the Access & .accdb DB.'To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel (your host application) by clicking Tools-References in VBE, and then choose an appropriate version of Microsoft Active X Data Objects x.x Library from the list.'For pre - MS Access 2007, files (viz. For Access 2007 (.accdb database) use the ACE Provider: "Microsoft. Execute "CREATE TABLE Sales Manager(Employee Id LONG, First Name Text(40), Surname Char(50) NOT NULL, Join Date Date, Sales Double, CONSTRAINT pk_EI PRIMARY KEY (Employee ID), CONSTRAINT un_FN UNIQUE (First Name))"conn DB.SQL Commands explained: The SQL SELECT Statement (the most commonly used SQL command) selects data from a database, specifying column(s) headings, table(s) from which columns are to be selected, and the criteria for selection.SELECT * is used to select all columns from a table.MS Access 97 up to MS Access 2003), use the Jet provider: "Microsoft. Examples include ad Schema Tables which returns the tables defined in the catalog, and ad Schema Columns which returns the columns of tables defined in the catalog. criteria) in the Schema Enum of ad Schema Tables are: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE. criteria) in the Schema Enum of ad Schema Columns are: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME. However, you can create a database table in ADO using SQL.To limit the results of the schema query, use the Criteria argument (this is the second parameter in the Open Schema Method, and is optional) which specifies an array of values (ie. The third argument of Schema ID is used only if the first argument is set to ad Schema Provider Specific. SQL CREATE TABLE statement creates a table with the specified fields.Flat file databases though easier to maintain, are far less robust and flexible.
Examples of often used DML commands are include INSERT, SELECT, UPDATE and DELETE.Options is an optional Long value which determines how the Command Text argument is evaluated: ad Cmd Unspecified - indicates that the Command Text property is unspecified; ad Cmd Text - evaluates Command Text as a textual definition of a command or stored procedure call; ad Cmd Table - evaluates Command Text as a table name whose columns are all returned by an SQL query; ad Cmd Stored Proc - evaluates Command Text as a stored procedure name; ad Cmd Unknown (default) - type of command in the Command Text argument is not known; Values of ad Cmd File or ad Cmd Table Direct are not to be used with the Execute Method. 'To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel (your host application) by clicking Tools-References in VBE, and then choose an appropriate version of Microsoft Active X Data Objects x.x Library from the list.'For pre - MS Access 2007, files (viz. For Access 2007 (.accdb database) use the ACE Provider: "Microsoft. The Execute method on the specified ADO connection object, executes the SQL statement passed in the Command Text argument:'The Like operator compares two strings, if the value in the string matches the pattern, it returns True.MS Access 97 up to MS Access 2003), use the Jet provider: "Microsoft. The percent sign (%) indicates zero or more characters after J ie.The multiple tables relate to and work with each other through certain key fields (like unique "ID Nos") viz.a separate table is created for vendor-details and another table for purchase-orders wherein vendor details are not repeated each time a purchase order is made and they are related with each other through a unique "Vendor ID" column which is present in both tables.
Whereas flat file databases contain a single table of data with fields representing all parameters viz.