![]() ![]() Maintenance scripts used to push new tables out to client applications.Tables are created in a temp mdb file that is created, loaded, used and deleted.) (Caution: will cause database file bloat. Create and delete temp tables for reports or other calculations.Here are some that I’ve encountered over the years: There are several scenarios where DDL might be the perfect solution. ALTER TABLE TestAllTypes DROP COLUMN MyBinaryĪLTER TABLE TestAllTypes ADD COLUMN ExtraInfo Text(255) Rather than simply dropping a column, you need to perform an ALTER TABLE command. However, if you want to change the column structure of a table, the syntax changes a bit. Note from the last script above that you can also delete, or DROP, indexes or entire tables. For the example above, the following script puts an ordinary index on the DateTime field and a unique index on the Text field. (Note: Unlike SQL Server, DDL scripts in Microsoft Access must be run one at a time.) CREATE INDEX MyDateTimeIndex ON TestAllTypes ( ASC)ĬREATE UNIQUE INDEX MyTextUniqueIndex ON TestAllTypes ( ASC)ĭROP INDEX MyTextUniqueIndex ON TestAllTypes It is not at all difficult to add an index, or a unique index, to a column. You are then presented with the QBE (query by example) grid with no tables. Select Data Definition from the Query | SQL Specific menu to continue. Begin in the normal way, by choosing New Query from the Query window, but when prompted, do not add any tables. The process to create a DDL query is a little different from what you might be used to. This script shows, in a nutshell, how to create a table with one column of each type of field, including AutoNumber, which is only intuitive if you remember that in Access 2.0, the AutoNumber was called a COUNTER field. Create a Tableįor starters, let’s consider the example provided by Microsoft in the KB Article mentioned above. ![]() The scripts used in this article have been collected into a demo database that is available for download. What follows in this article was culled from suggestions found there, as well as from an article from the Microsoft Knowledge Base: ‘How to’ write Access DDL that I found while searching the Google results. In fact, I find this resource to be so valuable, I have saved the following hyperlink to my Internet Explorer LINKS toolbar: Access GoogleNewsgroup Search (This hyperlink pre-populates frequently used fields, saving me time.) A quick search for ‘DDL’ in the Access related newsgroups yielded a plethora of results. My favorite resource is the advanced search page at Google Groups. While I am sure a comprehensive explanation exists in one of the mammoth technical volumes resting on my bookshelf, it is easier to simply search the Internet for the answer. Truth is, if I do not use a particular code technique frequently, I tend to forget the syntax. Help Yourself: Where to find answersīefore we look at some examples, it is worth revisiting the old “teach a man to fish” analogy. First, let’s see how this is done and then consider some scenarios where this technique will save you time and in some cases, a lot of work. DDL is used to create and alter database objects, such as tables. This too is part of the SQL language and can be run from an Access query, or through VBA code, but unlike DML, these commands do not return a result set. However, once you have gotten a handle on DML, you will want to explore the world of DDL, Data Definition Language. While this example does not perform any great manipulation of the data, some very clever calculations are possible. This type of query uses Data Manipulation Language (DML). A simple request for data might look something like this: SELECT FullName, Address, Phone One of the first things an Access developer learns is how to write SQL Queries. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |