Creating databases

Introducing a New Database

Creation of a new database can be done only through the ODBC Control Panel.

Filling the New Database

Create the structure of dbcreate.mdb

Needs["DatabaseAccess`"]

dbcreate  =  OpenDataSource["dbcreate"]

Create the table

DataSourceEvaluate[dbcreate,  
      SQLCreateTable[
        SQLTable["employees"],  {SQLColumn[ColumnName -> "emp_id",  
            DataTypeName -> "char",  DataLength -> 4,  
            Nullable -> False],  
          SQLColumn[ColumnName -> "emp_lname",  
            DataTypeName -> "text",  DataLength -> 40,  
            Nullable -> False],  
          SQLColumn[ColumnName -> "emp_fname", 
            DataTypeName ->  "text",  DataLength -> 20,  
            Nullable -> False]}]]

Enter three rows of data in the “employees” table using the SQLInsert function three times, with different data values each time.

\(DataSourceEvaluate[dbcreate, \ 
      SQLInsert[
        SQLTable["employees"], \ {{SQLColumn["emp_id"], "0001"}, \ {SQLColumn["emp_lname"], "Wayne"}, \ {SQLColumn["emp_fname"], "Sandy"}}]]
\)
\(DataSourceEvaluate[dbcreate, \ 
      SQLInsert[
        SQLTable["employees"], \ {{SQLColumn["emp_id"], "0002"}, {SQLColumn["emp_lname"], "Coburn"}, \ {SQLColumn["emp_fname"], "Washington"}}]]
\)
\(DataSourceEvaluate[dbcreate, \ 
      SQLInsert[
        SQLTable["employees"], \ {{SQLColumn["emp_id"], "0003"}, \ {SQLColumn["emp_lname"], "James"}, \ {SQLColumn["emp_fname"], "John"}}]]\)

View the “employees” table with column headings.  The column names each have a space in the name as defined.

DataSourceEvaluate[dbcreate,  SQLSelect[SQLTable["employees"]],  
        ShowColumnHeadings  ->  True] // TableForm

CloseDataSource[dbcreate]