reinnovate database

The Actual Database – Part 1

BAU DB is so convenient that you can click out a worksheet and enter data immediately without building anything. You can do so because BAU DB has taken care of the tasks of creating back-end database with default tables and fields, and associating them to each of the input cells on the worksheet that you have created just by a single click. Nevertheless, BAU DB is also an open database tool that let you create and manage your own database tables and fields, and you can create your own worksheet from scratch.

The Database Files

Let’s have a look first of the actual database files working behind the worksheets. BAU DB allows you to create as many workbooks, or applications, as you like. For each workbook, there are 2 working files. One is “dbp” – the setting file, like Book1.dbp which holds the settings and formulas of the workbook, and the other one is mdb – the data file, like Book1.mdb which holds the actual data of your work.
database files of a worksheet
If there is no file extension (*.xxx) shown in your Windows Explorer, you can check a option to turn it on. These links show you how to do so :
Windows XP, Windows 7, Windows 8, Windows 10

What’s Inside a Database File

The basic elements inside a database file is a collection of two dimensional tables, just likes a number of sheets holding tabular data in a spreadsheet file. Inside a database table, a row of record is the same as a row in the spreadsheet, and a data field is the same as a column in the spreadsheet.
Let’s have a look of the elements of a database using MS Access :
A database is a collection of data tables.

The Ways of Viewing the Database Tables

It will be more comfortable if you can “touch” the actual database tables. Actually, it is more convenient to open the database tables and view the actual data stored since there could be working data tables and fields that are hidden from the worksheet. Below is the ways to open the database tables.
Using “DB Tables”
Click “DB Tables” in Main Menu to pop out the Data Tables Form. Select a table to view it’s records.
click out the data table form from Main Menu
View the database tables
Open Database File in MDB Viewer Plus
You can open the actual database file (*.mdb file) of the workbook in MDB Viewer Plus, which is a free tool, and then move your spreadsheet data into the tables of the database file using the importing feature.
Download MDB Viewer Plus (freeware) in alexnolan.net.
As an example, if you are working on the workbook Book1.dbp, the file that is actually holding the working data is Book1.mdb (in the same location of Book1.dbp), while Book1.dbp is for the settings and formulas that are used in the workbook. You want to import the spreadsheet data and the first step is to open the file Book1.mdb in MDB Viewer Plus:
open database file in mdb viewer plus
Book1.mdb opened in MDB Viewer Plus:
database file opened in MDB Viewer Plus
Open Database File in MS Access
You can open the database file, for example Book1.mdb, in MS Access if you have one:
open worksheet database file in access

How to Know the Associated Database Table and Field of a Cell

Formulas manipulate data using the actual database tables and fields. Therefore it is necessary to know the associated table and field of a cell in the worksheet when you are writing formula related to the cell. Let’s look at the example of a default worksheet to understand how to know the associated table and field of a cell.
Each default worksheet is ready with its default tables and fields created automatically, so that you have no need to bother with the building work of database tables and fields and go straight to your work. The followings are the ways that you can check the associated table and field of a cell.
Mouse Over the Cell Corner
Move mouse pointer over the “..” button on the top right corner of a cell in the worksheet, and you will see the tooltips showing the data table and feild name inside the bracket. In the below example, the table name is “d” and the field name is “DocNo”.
check database table and field from tooltips of a cell
Open Cell Setting Screen
Click into the “..” button to pop out the Cell Setting Screen and you will find the table and field name in it.
pop out cell setting screen
database table and field name displayed in cell setting screen
Check in Worksheet Setting
Worksheet Setting includes all the settings of a worksheet. You can go to Worksheet Setting to check and update the database tables and fields of the cells in a worksheet.
Click “Worksheet Setting” in Main Menu and go to worksheet setting. You will find that Worksheet Setting is just another worksheet of BAU DB. Right, we are using BAU DB to build BAU DB’s applications ad it’s fun to do so :). The data in the section “Input Pane’s Cell Properties” define associated database tables, fields and cells of a record entry in a worksheet. One row here represents one cell in the worksheet.
click here in Main Menu to Worksheet Setting
table and field names of the cells in a worksheet