The Tables of a Database
A Table as a List
Imagine that you want to create a computer database for a company. After planning, you may want to start creating the list of employees. You could start with the list of names that employees call each other at work. Here is an example:
Julie, Suzie, John, Lucie
This information, however valuable would not provide much detail about each employee, such as the last name, which is used for employment verification, the salary used for payroll. To make the list more useful, you may need to provide more details about each employee, etc. Here is an example of how you may decide to change the list:
This is the essence of a database. A computer database is an application made of one or more lists that store information that can eventually be explored at will. The list should be easy to use and as intuitive as possible, providing as much information as possible but without any unneeded detail.
A list of items in a database is primarily referred to as a table. This means that the information that a database contains is stored in one or more tables. To make that information easy to use or locate, it is organized in sections called columns. If you observe the above table, you may find it difficult to know what type of information each column represents. To improve it, each column has a header that can display a label that indicates what the column represents. Based on this, the above table should display as follows:
This implies that, to get a database, you should start by creating one table. Later on, you can add as many tables as you judge necessary.
As a database application, Microsoft Access provides all the tools you need to create the necessary tables of your database. In future lessons, we will learn that Microsoft Visual Basic, through various libraries, provides other means of creating a table.
To create a table, you can use the main menu, the Database window, or the New Object button on the toolbar. To create a table using the main menu, you can click Insert -> Table.
If you want to use the Database window, of course you should display it first. If another object is on the screen and the Database Window is in the background, you can press F11 to display it. To programmatically display the Database window, you can call the SelectObject() method of the DoCmd object. Its syntax is:
DoCmd.SelectObject(ObjectType, ObjectName, InDatabaseWindow)
The first argument of this method can be A_TABLE, the second argument can be the name of an object to select. Because this argument is optional, in this case you can omit it. The third argument is a Boolean value that allows you to specify if you want to display the Database window. Based on the scenario we are describing, this argument should be passed as True. Here is an example:
Private Sub cmdDatabaseWindow_Click() DoCmd.SelectObject A_TABLE, , True End Sub
When this code executes, it will display the Database window.
When in the Database window, if a section other than Tables is displaying, to select the Tables section, click Tables. The Database window is equipped with a toolbar under its title bar. From this toolbar, you can click the New button.
As another option to start creating a new table, on the Database toolbar, you can click the arrow of the New Object button and click Table. Any of these three actions would display the New Table dialog box. To programmatically display the New Table dialog box, you can execute the following code:
Private Sub cmdDatabaseWindow_Click() DoCmd.DoMenuItem 1, A_FILE, 3, 0, A_MENU_VER20 End Sub
From the New Table dialog box, you can click Datasheet View and click OK. As an alternative, from the Tables section of the Database window, you can double-click Create Table By Entering Data. This also would display a spreadsheet-like window. The Datasheet View appears like a spreadsheet:
You can use it to start entering values or you can change some of its aspects.
From the New Table dialog box, you can also click Design View and click OK. As an alternative, from the Tables section of the Database window, you can double-click Create Table In Design View. This also would display the Design View of a new table:
This view is the most significant, the most detailed, and the best window to create a table as it provides many options.
On the New Table dialog box, you can click Table Wizard and click OK. As an alternative, from the Tables section of the Database window, you can double-click Create Table By Using Wizard. Any of these two actions would start the Table Wizard:
The Table Wizard provides the fastest means of creating a table.
To start a table, you must create an object called a column. In the next lesson, we will learn the various techniques of creating a column.
While or after creating a table, to make it part of your database, you must save it. In fact, if you try closing a table after starting to create it, you would be asked whether you want to save it or not. Also, some other operations we will see in the next lessons require that you save the table even if you have not completed it.
To save a table while still working on it, you can click the Save button on the Table Design or the Table Datasheet toolbar. This would display the Save As dialog box.
Microsoft Access is very flexible with names of tables. The name of a table:
This flexibility can become overwhelming and confusing. To avoid these, there are suggestions and conventions we will apply when naming our tables:
In the first lesson, we mentioned that Microsoft Access is equipped with its own library used to create and manage databases. You can verify the existence of this library if you open the References dialog box from the Tools menu of Microsoft Visual Basic:
To programmatically create a table using the Microsoft Access Object Library, you can first declare a variable of type Object and then initialize it with the CreateTableDef() method of the current database object. This method takes as argument the name of the new table. After creating the table, you must add it to the current database. To support this, the CurrentDb object is equipped with the TableDefs property. TableDefs is in fact a collection. The TableDefs collection is equipped with the Append() method that is used to add a new table to the current database. This would be done as follows:
Private Sub cmdCreateTable_Click() Dim curDatabase As Object Dim tblStudents As Object ' Get a reference to the current database Set curDatabase = CurrentDb ' Create a new table named Students Set tblStudents = curDatabase.CreateTableDef("Students") . . . ' Add the Students table to the current database curDatabase.TableDefs.Append tblStudents End Sub
In DAO, a table is an object of type TableDef. The tables of a DAO database are stored in a collection called TableDefs. To give access to this collection, the DAO object is equipped with a property named TableDefs that is of type TableDefs.
To provide access to a TableDef object, the DAO object is equipped with a property called TableDef. Before creating a table, you should first declare a variable of type DAO.TableDef. To initiate the table, use the Set operator to assign the CreateTableDef() method of a database you have previously opened. here is an example:
Private Sub cmdCreateTable_Click() Dim dbDeja As DAO.Database Dim tblEmployees As DAO.TableDef ' Open the database Set dbDeja = DBEngine.OpenDatabase("C:\Yevol\Exercise.mdb") ' Create a new TableDef object. Set tblEmployees = dbDeja.CreateTableDef("Employees") . . . End Sub
After initiating a table, you can add it to the database by passing it to the Append() method of the TableDefs property of the database that will receive the table. This would be done as follows:
Private Sub cmdCreateTable_Click() Dim dbDeja As DAO.Database Dim tblEmployees As DAO.TableDef ' Open the database Set dbDeja = DBEngine.OpenDatabase("C:\Yevol\Exercise.mdb") ' Create a new TableDef object. Set tblEmployees = dbDeja.CreateTableDef("Employees") . . . ' Add the new table to the database. dbDeja.TableDefs.Append tblEmployees dbDeja.Close End Sub
In the SQL, to create a table, you can type the expression CREATE TABLE followed by the name of the table. The syntax starts with:
CREATE TABLE Name;
The CREATE and TABLE keywords must be used to let the SQL interpreter know that you want to create a table. The Name factor specifies the name of the new table. The Name can use the rules and suggestions we have been applying to the variables of the database objects. As mentioned already, a table needs at least one column. The formula to create a column is:
CREATE TABLE Employees(ColumnName DataType)
A column is specified with a name and a data type. The name can follow the rules and suggestions we reviewed for the tables.
Private Sub cmdTable_Click() DoCmd.RunSQL "CREATE TABLE Employees(EmployeeName Text);" End Sub
The ADO library relies on the SQL to perform most of its database operations. Based on this, to create a database in ADO, formulate a SQL statement that creates a database and let ADO execute it. To execute a SQL statement in ADO, the Connection object is equipped with the Execute() method. Its syntax is:
Connection.Execute ExecString, RecordsAffected, Options
In this syntax, the SQL statement that creates the database can be passed as the first argument. The other two arguments are optional.
Before performing most operations on a table, you may need to select it. This is a routine operation that is usually done transparently but in some cases, it is a prerequisite. If you select a table, some operations you perform may affect it, depending on how such operations are carried out. A table indicates that it is selected when it is highlighted:
In this example, a table named CD@Home is selected.
To select a table, in the Tables section of the Database window, you can simply click it, once. If another table or another item is already selected in the Tables section of the Database window, you can press the up or the down arrow key continuously until the table is selected.
To programmatically select a table, you can use the DoCmd object that is equipped with the SelectObject() method. The syntax to use would be:
DoCmd.SelectObject acTable, [objectname][, indatabasewindow]
The first argument must be acTable in this case because you want to select a table. The second argument is the name of the table you want to select. If you want to select the table and only highlight it in the Database window, then pass the third argument as True.
Here is an example:
If the table is already opened (in the next section we will see how to open a table) and it is displaying, it is most likely in the background. If you omit the third argument or pass it as False, the table would be displayed in the foreground. If the table is not opened and you omit the third argument or pass it as False, you would receive an error. You can use a conditional statement and error handling to make sure the user doesn't see this nasty dialog box.
By default, if you open a database in Microsoft Access, all of its tables are closed. Before using a table, you may need to open it first and this depends on what you want to do with the table. A table can be opened in one of three different views:
DoCmd.OpenTable tablename[, view][, datamode]
The first argument of this method is the name of the table that you want to open. The second argument is a constant value as follows:
This second argument is optional. If you omit it, the acViewNormal option applies.
The third argument, also optional, has to do with data entry, which we haven't reviewed yet. This means that you can omit it.
Here is an example:
When this code executes, a table named Members would be opened in Design View.
After using a table, you can close it. If there is a structural change that needs to be saved, Microsoft Access would prompt you.
To close a table, you can click its system Close button or . You can also double-click its System button on the left side of its title bar. You can also press Ctrl + F4.
To programmatically close a table, you can call the Close() method of the DoCmd object. Its syntax is:
DoCmd.Close acTable, [objectname], [save]
The first argument must be specified as acTable because you are trying to close a table. The second argument can be the name of the table you want to close. If you suspect that there might be a need to save the structure of the table, you can pass the third argument with one of the following values:
The tables of a Microsoft Access database are stored in a collection called AllTables. Each table of this collection can be identified by its name or its index. When working on a database, the total number of its tables is stored as the Count property of the AllTables collection.
To identify a table in the AllTables collection, you can access it using its name if you know it. To help with this, each table of the collection has a property called Name that represents the name of the table. Here is an example that shows the name of each table of the current database:
Private Sub cmdTables_Click() Dim obj As AccessObject, dbs As Object Set dbs = Application.CurrentData ' Check each object of the AllTables collection For Each obj In dbs.AllTables ' When you find a table, display its name MsgBox obj.Name Next obj End Sub
When using the Name property of a table, you are supposed to know. In some cases, you may not know the name of a table. An alternative is to access a table by its rank in the collection. To support this, the AllTables collection is equipped with an Item() property, which is an array. In the parentheses, you can enter the index of the desired table. The first table has an index of 0, the second has an index of 1, and so on. Here is an example that would display then name of the third table of the current database:
In most cases, before performing an operation on a table, you will need to indicate what table you are referring to. This is usually easy to the user who can visually see the table. As for you as the database developer, you can first programmatically get a pointer to the table you intend to work on.
If a table exists already, to get a reference to it, first declare an Object variable. Then, assign the TableDefs property of the current database to the variable. TableDefs is a collection of the tables of the current database. To specify what table you are referring to, you can pass its name, as a string to the TableDefs indexed property. Here is an example:
Private Sub cmdGetReference_Click() Dim curDatabase As Object Dim tblStudents As Object ' Get a reference to the current database Set curDatabase = CurrentDb ' Get a reference to a table named Students Set tblStudents = curDatabase.TableDefs("Students") End Sub
Instead of using its name, you can also pass the numeric index of the table to the TableDefs property.
To get a reference to a table in DAO, you essentially use the same approach as above. Here is an example that gets a reference to the first table of the current database:
Private Sub cmdContrators_Click() Dim curDatabase As DAO.Database Dim tblContractors As DAO.TableDef ' Get a reference to the current database Set curDatabase = CurrentDb ' Get a reference to a table named Contractors Set tblContractors = curDatabase.TableDefs(0) End Sub
Renaming a table consists of changing its name from the original or previous name. As introduced above, you cannot rename a table if it is opened: you would receive an error. Also, you should check whether the table is involved in a relationship or an expression.
To rename a table in the Database window, in the Tables section, you can right-click the table and click Rename. The name of the table would become highlighted, indicating that it is in edit mode. You can then type the new name and press Enter.
To programmatically rename a table, you can use the DoCmd object that provides the Rename() method. The syntax to use would be:
DoCmd.Rename(NewName, acTable, OldName)
Here is an example:
When this code executes, an existing table named StaffMembers would be renamed as Employees.
When you rename a table, you get the same table with a new name. This preserves the structure and contents of the table. An alternative to this approach consists of making a copy of an existing table. With this technique, you would get two tables that share the same structure and have the same contents, with different names. This can be useful if you want to experiment with the structure or contents of an existing table without risking to compromise it. Microsoft Access supports this in two ways.
To make a copy of an existing table using the Microsoft Windows Save As routine, in the Tables section of the Database window, you can right-click the table and click Save As... This would open the Save As dialog box:
If you are just trying to experiment with the table, you can accept the name. Otherwise, to give a different name to the new table, type the desired string in the Save Table To text box and click OK. If the name exists already, you would receive an error:
Besides the Save As technique, you can also use the Copy And Paste routine of Microsoft Windows. To do this, in the Tables section of the Database window, right-click an existing table, click Copy, then right-click an empty area of the Tables section of the Database window and click Paste. This would open the Paste Table As dialog box:
If you want to change your mind, you can click Cancel. Otherwise, you can type the desired string in the Table Name text box and click OK.
DoCmd.CopyObject [destinationdatabase][, newname], acTable, sourceobjectname]
The first argument to this method is the name or path of the database where the copied object would be transferred to. If you are making a copy of the table in the same database that is opened, you can omit this argument.
The second argument is the name that you want the new table to have. It is the same string you would provide to the above Save As or the Paste Table As dialog boxes.
The third argument must be acTable because in this case you are copying a table.
The last argument is the current name of the existing table.
Here is an example:
From this example, a table named StaffMembers will be copied to generate a new table named Teachers, to the current database.
If you happen to have a table you don't need anymore in your database, you can remove it. Once again, don't remove a table if you have any doubt. It is better to have a useless table whose role is not clear than to delete a table in doubt. The reason is that, if you delete a table by mistake and then later on find out that you need it, you would have to recreate it completely. If you remove a table that is involved in an expression or a relationship, the expression or the relationship would be broken and this would result in unpredictable results. Fortunately, if you start deleting a table without using code, you would be warned. If the table is involved in a relationship, Microsoft Access would warn and may even prevent you from deleting it.
To visually remove a table, in the Tables section of the Database window, you can right-click the table and click Delete. As stated already, before the operation is carried out, you would be warned:
After reading the message, if you want to change your mind, you can click No. If you still want to delete the table, you can click Yes.
DoCmd.DeleteObject acTable, [objectname]
The acTable argument indicates that you want to delete a table. If you select a table in the Database window when this method is called, you can omit the second argument and the selected table would be deleted. Otherwise, to specify the table you want to delete, pass its name as the second argument of the method.
Here is an example:
When this code executes, Microsoft Access would look for a table named Members. If it finds it, it would remove it from the database.
To delete a table using either the Microsoft Access Object Library or DAO, pass the name of the undesired table to the Detele() method of the TableDefs property of the database. Here is an example from the Microsoft Access Object Library:
Private Sub cmdDeleteTable_Click() Dim curDatabase As Object Set curDatabase = CurrentDb curDatabase.TableDefs.Delete "Books" End Sub
If you are using SQL, to delete a table, create a DROP TABLE expression followed by the name of the table. The formula to use is:
DROP TABLE TableName;
Replace the TableName factor of our formula with the name of the table you want to delete. Here is an example:
Private Sub cmdDeleteTable_Click() Dim conDepartments As ADODB.Connection Dim strSQL As String Set conDepartments = New ADODB.Connection conDepartments.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\ADO Lessons\MVA1\MVA.mdb'" strSQL = "DROP TABLE Departments;" conDepartments.Execute strSQL MsgBox "The Departments table of the MVA.mdb database has been deleted" Set conDepartments = Nothing End Sub
|Previous||Copyright © Yevol, 2007||Next|