This sample (with full documentation) illustrates how to do a variety of DDL (Data Definition Language) operations using both SQL and DAO.
DDL operations are those that modify the database structure, ie. tables, fields, indexes, and relationships.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic570_post587.html
Data Definition Language (DDL)
Data Definition Language (DDL) is a programmatic way to create and modify the database structure, that is, objects like tables, indexes, and relationships.
Before Access came along, DDL statements were the only way to modify the database structure in most relational database management systems. Access introduced the graphical user interface (GUI) to do most DDL functions. Because the Access GUI is so easy to use, most Access users never have reason to use DDL statements. However, there are circumstances under which it is advantageous to use DDL statements.
I use them a lot for automating data import processes. I can create a temporary table, import data to the temp table, change data types, remove indexes from the permanent table, append data from the temp to the permanent table, then rebuild the indexes -- all automatically, all in SQL code.
But that's not the only use. You can also use DDL to set the seed and interval for an autonumber field, to remotely change the structure of a back-end database (useful for multi-user databases), or make any other change to a production database. In a web environment, it can be used to add databases to an active website.
In this three-part series, I'll be addressing both SQL and DAO.
DAO (Data Access Objects) works differently than SQL. DAO is an object model specific to Access (or rather for the MDB or ACCDB file types). One reason for using DAO over SQL is that you can set Access specific properties (like Default Value and Validation Rules) with it. Also, certain datatypes (like Hyperlink) can only be created with DAO.
In general, with DAO, you:
You can't create a Table without creating at least one Field, so you have to create and append the fields for the table before you append the table. If you set Properties for the field, you must do so before you append the field to the fields collection. So creating a table goes something like this:
So, specific examples might go something like these:
Sub exaCreateTableDAO()
'DAO DDL example demonstrates creating a table, fields, properties
' Declare object variables
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
' Create the table (BooksDAO)
Set tbl = db.CreateTableDef("BooksDAO")
' Create a field (ISBN)
Set fld = tblNew.CreateField("ISBN", dbText, 13)
' Set field properties
fld.Required = True
' Append field (ISBN) to Fields collection
tbl.Fields.Append fld
' Create a field (Title)
Set fld = tblNew.CreateField("ISBN", dbText, 100)
' Set field properties
fld.Required = True
fld.AllowZeroLength = False
fld.DefaultValue = "Unknown"
' Append field (Title) to Fields collection
tbl.Fields.Append fld
' Append table (BooksDAO) to TableDef collection
db.TableDefs.Append tbl
End Sub
Modifying a table (that is, adding a new field or adding new properties to an existing field) is similar, except instead of instantiating a new table, you instantiate an existing table. When modifying an existing table or field, you do not need to append it to its collection.
Sub exaModifyTable()
'DAO DDL example'demonstrates modifying a table by adding a field
' and modifying an existing field's property
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
' Instantiate existing table (BooksDAO)
Set tdf = db.TableDefs("BooksDAO")
' Create a field (Price)
Set fld = tdf.CreateField("Price", dbCurrency)
' Append field to Fields collection
tdf.Fields.Append fld
' Modify existing field (Title) with properties (validation rule
' and validation text)
Set fld = tdf.Fields("Title")
fld.ValidationRule = "Like 'A*' or Like 'Unknown'"
fld.ValidationText = "Known value must begin with A"
End Sub
Sub exaModifyFieldNames()
'DAO DDL example demonstrates modifying field names
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs("MyTable")
For Each fld In tdf.Fields
fld.Name = fld.Name & "new"
Next
End Sub
Sub exaCreateDB()
'DAO DDL example demonstrates creating a database programmatically
Dim dbNew As DAO.Database
Set dbNew = CreateDatabase _
("C:\classes\cis253\winter98\MoreBks", dbLangGeneral)
End Sub
Sub DeleteTable()
'DAO DDL example demonstrates deleting a table
Dim db As DAO.Database
Set db = CurrentDb
db.TableDefs.Delete "tstBooks"
End Sub
Sub exaCreateIndex()
'DAO DDL example demonstrates creating an index
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs!Books
' Create index
Set idx = tdf.CreateIndex("PriceTitle")
' Append fields to index
Set fld = idx.CreateField("Price")
idx.Fields.Append fld
Set fld = idx.CreateField("Title")
idx.Fields.Append fld
' Append index to table
tdf.Indexes.Append idx
End Sub
Sub CreatePrimaryKey()
'DAO DDL example demonstrates creating an index
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs!Books
' Create index
Set idx = tdf.CreateIndex("PriceTitle")
' Append fields to index
Set fld = idx.CreateField("Price")
idx.Fields.Append fld
Set fld = idx.CreateField("Title")
idx.Fields.Append fld
' Make Index primary
idx.Primary = True
' Append index to table
tdf.Indexes.Append idx
End Sub
Sub DeleteIndex()
'DAO DDL example demonstrates creating a composite primary key
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
Set tdf = db.TableDefs!Books
tdf.Indexes.Delete "PriceTitle"
End Sub
Sub exaRelations()
'DAO DDL example creating a Relationship
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
Set db = CurrentDb
' Create relation
Set rel = db.CreateRelation("PublisherRegions", _
"PUBLISHERDAO", "SALESREGIONS")
' Set referential integrity w/ cascade updates
rel.Attributes = dbRelationUpdateCascade
' Specify key field in KeyTable (Publishers)
Set fld = rel.CreateField("PubID")
' Specify foreign key in ForeignTable (SalesRegions)
fld.ForeignName = "PubIDFK"
' Append field to Relation
rel.Fields.Append fld
' Append relation to Relations collection
db.Relations.Append rel
End Sub
Sub exaDeleteRelation()
'DAO DDL example Deleting a Relationship
Dim db As Database
Set db = CurrentDb
db.Relations.Delete "PublisherRegions"
End Sub
Sub exaCreateTableWithAutoNumberAndHyperlink()
'DAO DDL example demonstrates creating a table, fields, properties
Dim db As DAO.Database
Dim tblNew As DAO.TableDef
Dim fld As DAO.Field
' Create the table and a field
Set db = CurrentDb()
Set tblNew = db.CreateTableDef("NewTable")
Set fld = tblNew.CreateField("AutoField", dbLong)
' Set field properties
fld.Required = True
fld.Attributes = dbAutoIncrField
' Append field to Fields collection
tblNew.Fields.Append fld
' Create Primary Key
Set idx = tblNew.CreateIndex("PrimaryKey")
' Append fields to index
Set fld = idx.CreateField("AutoField")
idx.Fields.Append fld
' Make Index primary
idx.Primary = True
' Append index to table
tblNew.Indexes.Append idx
' Create hyperlink field
Set fld = tblNew.CreateField("HyperField", dbMemo)
' Set field properties
fld.Attributes = dbHyperlinkField
' Append field to Fields collection
tblNew.Fields.Append fld
' Append table to TableDef collection
db.TableDefs.Append tblNew
End Sub
Data Definition Language (DDL)
Data Definition Language (DDL) is a programmatic way to create and modify the database structure, that is, objects like tables, indexes, and relationships.
Before Access came along, DDL statements were the only way to modify the database structure in most relational database management systems. Access introduced the graphical user interface (GUI) to do most DDL functions. Because the Access GUI is so easy to use, most Access users never have reason to use DDL statements. However, there are circumstances under which it is advantageous to use DDL statements.
I use them a lot for automating data import processes. I can create a temporary table, import data to the temp table, change data types, remove indexes from the permanent table, append data from the temp to the permanent table, then rebuild the indexes -- all automatically, all in SQL code.
But that's not the only use. You can also use DDL to set the seed and interval for an autonumber field, to remotely change the structure of a back-end database (useful for multi-user databases), or make any other change to a production database. In a web environment, it can be used to add databases to an active website.
In this three-part series, I'll be addressing both SQL and DAO.
The SQL DDL statements can be executed in a query window, but the most useful way to do it is in VBA code. To do that, I need to build a string holding the SQL statement and run it using the Execute method of the database object. There are two ways to do this. The first (and simplest) is to run it directly against the built-in CurrentDb object:
Dim strSQL As String
strSQL = "<The SQL Statement here>"
CurrentDb.Execute strSQL
The other is a little more complicated, but in my opinion better. Create a database object and set it to the CurrentDb:
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "<The SQL Statement here>"
db.Execute strSQL
This method is particularly useful when executing multiple SQL statements as I'll show later.
Drop Table
CurrentDb.Execute "DROP TABLE BOOKSQL;"
Create Table
'create a table with 4 fields and Primary key
Dim strSQL As String
strSQL = "CREATE TABLE BOOKSQL "
strSQL = strSQL & "(ISBN TEXT(13) CONSTRAINT PKey PRIMARY KEY, "
strSQL = strSQL & "Title TEXT(100), "
strSQL = strSQL & "Price CURRENCY, "
strSQL = strSQL & "PubID TEXT(10)); "
CurrentDb.Execute.Execute strSQL
Datatype key words:
Alter Table
CurrentDb.Execute "ALTER TABLE PublisherSQL ADD PubAddress TEXT (50);"
CurrentDb.Execute "ALTER TABLE BookSQL ADD Cost CURRENCY;"
CurrentDb.Execute "ALTER TABLE BookSQL ADD PubDate DATETIME;"
Drop Index
CurrentDb.Execute "DROP INDEX idxTitle ON BookSQL;"
Create Index
CurrentDb.Execute "CREATE UNIQUE INDEX idxTitle ON BookSQL (Title);"
Create Primary Key
CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON Books (BookID) WITH PRIMARY;"
Drop Relationship
CurrentDb.Execute "ALTER TABLE BookSQL DROP CONSTRAINT PubBook;"
Create Relationship
Dim strSQL As String
strSQL = "ALTER TABLE [BookSQL] "
strSQL = strSQL & " ADD CONSTRAINT [PubBook] FOREIGN KEY (PubID) "
strSQL = strSQL & " REFERENCES PublisherSQL (PubID);"
CurrentDb.Execute strSQL
Examples:
Sub DropTables()
'deletes a Relationship and two tables
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "ALTER TABLE BookSQL DROP CONSTRAINT PubBook;"
db.Execute "DROP TABLE PublisherSQL;"
db.Execute "DROP TABLE BookSQL;"
Set db = Nothing
End Sub
'---------------------------------
Sub BuildTables()
' creates two tables and a Relationship between them
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
'create PublisherSQL table
strSQL = "CREATE TABLE PublisherSQL "
strSQL = strSQL & "(PubID TEXT(10) CONSTRAINT "
strSQL = strSQL & "PrimaryKey PRIMARY KEY, "
strSQL = strSQL & "PubName TEXT(100), "
strSQL = strSQL & "PubPhone TEXT(20));"
db.Execute strSQL
'create BookSQL table
strSQL = "CREATE TABLE BookSQL "
strSQL = strSQL & "(ISBN TEXT(13) CONSTRAINT PKey PRIMARY KEY, "
strSQL = strSQL & "Title TEXT(100), "
strSQL = strSQL & "Price MONEY, "
strSQL = strSQL & "PubID TEXT(10)); "
db.Execute strSQL
'create One-To-Many relationship between BookSQL and table
strSQL = "ALTER TABLE [BookSQL] "
strSQL = strSQL & " ADD CONSTRAINT [PubBook] FOREIGN KEY (PubID) "
strSQL = strSQL & " REFERENCES PublisherSQL (PubID);"
db.Execute strSQL
Set db = Nothing
End Sub
'---------------------------------
Sub DropIndex_Import_CreateIndex()
' this sample demonstrates removing an index, importing data, and
' re-creating the index.
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "DROP INDEX idxTitle ON BookSQL;"
DoCmd.TransferText acImportDelim, "", "Books", _
CurrentProject.Path & "\Books.txt", True, ""
db.Execute "create unique index idxTitle on BookSQL(Title);"
Set db = Nothing
End Sub
'---------------------------------
Sub ModifyField()
'you can't directly modify a field in a table you have to:
'1) create a new field with the new properties
'2) copy the data from the old field to the new field
'3) delete the old field
'Note: if the field to be deleted is part of an index, that
'index must be dropped and then re-established on the new field
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "alter table [Books Copy] add Title2 text(100);"
db.Execute "UPDATE [Books Copy] SET Title2 = Title;"
db.Execute "alter table [Books Copy] drop column Title;"
Set db = Nothing
End Sub
Next time, I'll take a closer look at specific Data Definition Language using DAO.
Data Definition Language (DDL)
Data Definition Language (DDL) is a programmatic way to create and modify the database structure, that is, objects like tables, indexes, and relationships.
Before Access came along, DDL statements were the only way to modify the database structure in most relational database management systems. Access introduced the graphical user interface (GUI) to do most DDL functions. Because the Access GUI is so easy to use, most Access users never have reason to use DDL statements. However, there are circumstances under which it is advantageous to use DDL statements.
I use them a lot for automating data import processes. I can create a temporary table, import data to the temp table, change data types, remove indexes from the permanent table, append data from the temp to the permanent table, then rebuild the indexes -- all automatically, all in SQL code.
But that's not the only use. You can also use DDL to set the seed and interval for an autonumber field, to remotely change the structure of a back-end database (useful for multi-user databases), or make any other change to a production database. In a web environment, it can be used to add databases to an active website.
In this three-part series, I'll be addressing both SQL and DAO.
DAO vs. SQL
In Access, the two major methods are DAO methods and SQL Statements. DAO (Data Access Objects) is the object model that Access uses to programmatically manipulate the database and its data. (There are other object models you can use like ADO and ADO.net, but DAO is recommended for use with Access.) However, in many cases, you can also manipulate your database with in SQL statements. In general, SQL is more efficient than other methods, so if you can, it's recommended.
SQL DDL statements have a number of advantages over other methods of modifying the database structure. For one thing, is independent of the object model you're using (ADO, DAO, ADO.Net) and can be executed from different platforms like VBA, C++, C#, and so forth. And while there are minor differences in implementation, DDL is fairly standard to most database platforms like Access, SQL Server, Oracle, and Sybase. It is also easier to read and understand.
For instance, in SQL, I can create a simple table like so:
Sub CreateTableSQL()
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "CREATE TABLE NewTable2 " & _
"(NewField1 TEXT(100), " & _
"NewField2 SINGLE);"
db.Execute strSQL
End Sub
By comparison, in DAO, I need to do the following:
Sub CreateTableDAO()
Dim db As DAO.Database
Dim tblNew As DAO.TableDef
Dim fld As DAO.Field
Set tblNew = db.CreateTableDef("NewTable")
Set fld = tblNew.CreateField("NewField1", dbText, 100)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("NewField2", dbSingle)
tblNew.Fields.Append fld
db.TableDefs.Append tblNew
End Sub
SQL DDL statements have a number of disadvantages as well. Most importantly, you cannot set a number of Access specific table properties, like Validation Rules, Validation Text, Default values, and so forth. To do that, you have to use an object model like DAO.
Next time, I'll take a closer look at specific SQL Data Definition Language statements.
By AD Tejpal
This sample db demonstrates listing of folders and files contained within the selected top directory, as per desired file specifications. Based upon user's choice, subfolders can be included or ignored. If desired, more than one types of files can be included in a single file spec in the form of a comma separated string - e.g. : "*.htm,*.pdf,*.txt" etc.
Process Mode For Listing Folders and files:
Two alternative methods for listing of folders and files, under a given top folder, are covered as follows:
(a) Non-Recursive mode - using Dir() function. Apart from being faster than (b), it has the advantage that there is no extra strain on memory resources (otherwise associated with recursive approach), thus avoiding the risk of potential hang up in case of very large and deep directory tree.
(b) Recursive mode - using FileSystemObject
Display Of Listed Folders And Files:
Each run for listing of folders and files is logged in table T_ProcessLog. On the viewing form, for the selected ProcessID, path and other details of topmost folder are displayed at top of the form. Similar details for the current subfolder are displayed just below the information for top-most folder.
Subfolders and their files are displayed in adjacent subforms. For the current file, its details (like file type, size, attributes, DtCreated / DtLastModified / DtLastAccessed) are also displayed, apart from a hyperlink to the file itself. The hyperlink label becomes active only for permitted file types. The user can edit the contents of table T_AllowHyperLink for setting such permissions. Three alternative styles of display are provided as follows:
Style A - View Folders and Files In Hierarchical Chain:
The user can drill down the directory tree by expanding any of the subfolders which then assumes the role of current main folder, resulting in display of subfolders and files held by the erstwhile subfolder. This can be done indefinitely, till the last subfolder at deepest nesting level is reached. Similarly, by pressing a command button, the user can move up the directory tree. The process can be repeated till the current main folder becomes identical to the top folder (i.e. the original top most folder for which the listing was generated).
Style B - View All Folders At A Glance - And Their Files:
For convenient viewing, the folders are sorted as per nesting level and path. The top-most folder (nesting level: zero) is highlighted in distinct color. For other folders, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other.
Style C - View All Files At A Glance:
For convenient viewing, the files are sorted as per nesting level and path. Files in top-most folder (nesting level: zero) are highlighted in distinct color. For other files, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other.
General module named basCommDlg, an adaptation from Access Developer's Handbook, has been kindly provided by Bill Mosca.
Note:
(a) While using file system object, all types of folders and files get covered (including System, Volume, Hidden, ReadOnly etc). On the other hand, while using Dir() function, such types don't get covered unless relevant arguments are explicitly supplied.
(b) Using Dir command via DOS command prompt, listing of folders and files can be saved to a text file, which can then be imported into access table. Such a listing is quite fast and one might be tempted to try this route. However, there is a pitfall associated with this approach. If any special characters are present in the folder or file name (say in internet files), the same might not come through faithfully. For example ® is found to come across as r - resulting in corrupted path.
Version: Access 2000 file format.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic568_post584.html
More samples by AD Tejpal: http://www.rogersaccesslibrary.com/forum/tejpal-ad_forum45.html
