How to open MDB files

MDB Viewer Plus can be used to open Microsoft Access MDB or Accdb database files. This removed the requirement of Microsoft of a Microsoft Access installation and license. It also allows you to view and edit an Access database on a system where an installation of Access might not be allowed, such as a server.

 

To open a MDB or Accdb file use the standard open option from the file menu or toolbar. This will present you with the open dialog window as seen below.

 

At the bottom of this standard open dialog you will see a drop down box for file types. You can use this to select MDB files or Accdb files. To open a Accdb file you must first have installed the Access Database Engine 2007 which can be downloaded for free from Microsoft's website.

 

Open Options

Once you have selected your MDB file or Accdb file click open. You will then be presented with the options menu as shown below. This options menu has the following options.

• Open from or as read only.
• Open tables marked as system.
• Exclusive mode.
• Open server side or very large database.
• Password.
• Open workgroup.

Open from or as read only

This is for opening MDB files from read only devices such as CDs, DVDs or read only due to security restrictions in place on the system.

Open tables marked as system

This allows you to include standard system tables.

Exclusive mode

Opening MDB files in exclusive prevents other users from opening and allows you to make changes to the database structure.

Open server side or very large database

This option is for performance reasons. When opening large databases or databases located on a remote location that would otherwise be slow. The downside is various filtering and sorting options are not available.

Password

A password can be specified if the chosen MDB / Accdb file requires one. If it does not then simply leave this box blank.

Open workgroup

The open workgroup is for databases which have been setup to include an additional workgroup file to manage and control user access and security. Below this option has two additional boxes for user account within the workgroup file.

 

Once these options have been chosen select OK and the database will be open will allow the tables and queries displayed on the tabs just under the toolbar.

 

 

Opening by dragging and dropping

 

MDB and Accdb files can also be open by simply dragging a MDB file from explorer and dropping onto MDB Viewer Plus.

Opening using runtime parameter

If you are opening the same MDB or Accdb file on a regular biases you can create a shortcut to your saved copy of MDB Viewer Plus and then add the full path to the MDB file as a parameter.

Opening by association

MDB and Accdb files can be associated with MDB Viewer Plus so that when they are double clicked in explorer they are opened automatically.

Setting the Sort property

To set a filter set the value of the filter box to a string that contains the sorts condition.

Examples

• State ASC
  This will sort by the field named 'State' in ascending order.
• State DESC
  This will sort by the field named 'State' in ascending order.
• Surname ASC, Firstname ASC
  This will sort by both fields named Surname and Firstname in ascending order.
• [First Name] ASC
  If the field name contains a space or a symbol character, then put it in square brackets.

NOTE: ASC and DESC must be in upper case.

 

To set a filter set the value of the filter box to a string that contains the filter's test condition.

 

For example, the following statement creates a filter that tests a dataset's State field to see if it contains a

value for the state of California:

State = 'CA'

 

Use 'and' to increase the criteria but adding additional fields.

Surname='Smith' and Firstname='John'

 

Use or to return records for either criteria

Firstname ='James' or Firstname='Jim'

 

Blank field values do not appear unless they are explicitly included in the filter:

State <> 'CA' or State = BLANK

 

Note: After you specify a value for Filter, to apply the filter to the dataset, set the Filtered property to

True.

 

Filters can compare field values to literals and to constants using the following comparison and logical

operators:

• Operator Meaning
• < Less than
• > Greater than
• >= Greater than or equal to
• <= Less than or equal to
• = Equal to
• <> Not equal to

AND Tests two statements are both True

NOT Tests that the following statement is not True

OR Tests that at least one of two statements is True

+ Adds numbers, concatenates strings, ads numbers to date/time values

- Subtracts numbers, subtracts dates, or subtracts a number from a date

 

By using combinations of these operators, you can create fairly sophisticated filters.

 

For example, the following statement checks to make sure that two test conditions are met before

accepting a record for display:

(Custno > 1400) AND (Custno < 1500)

 

If the field name contains a space or a symbol character, then put it in square brackets.

[First Name]='ALEX'

Filtering by date

To filter a date field by a date value enclose the value with the # character instead of ' character.
Shipdate = #25/06/1988#

 

Below is the query window in MDB Viewer Plus. You can use this window to execute standard SQL statements which are compatible with Microsoft Access.

 

Results from a select query can be exported by clicking the export button on the toolbar once the select query returns its results.

 

 

You see circled in red a check box labelled "Non result query (update query)". Check this box when you perform a non select query such as an update query or an insert or alter table query.

You can import data from a variety of different formats to the current table using the import wizard. Simply follow the wizard through to the end. Data can be imported from the following formats such as CSV,TXT,Doc,XLS,WK1,WQ1,SAV,DB,DBF,MDB,ADO,ADT,DAT,HTM,XML,WAB and VCS.

 

 

You can export the currently displayed table to a number of different formats. To export click the Export button on the toolbar and enter a file name for your exported data. Select the type of data from the drop down box on the bottom of the dialog and click save. You can see it in the screen shot below circled in red.

 

 

A progress bar will be displayed while your current table is being exported.

To create a new MDB file is very simple. Click the new button from the toolbar or select "New Database" from the file menu. Enter the MDB file name or the database to be created and select save.

From version 2.16+ you can create a Accdb database file by selecting "Access 2007+ Database (*.accdb)" from the save as type drop down box shown below circled in red. Note you need to have the Microsoft Access 2007 database engine installed first

A new blank MDB / Accdb  file will be saved in the location you selected in the save dialog.

 

You can now add tables to the MDB file. See "Adding a new table" section for details.

 

• To add a new table enter the table name and below.
• Input fields with data type and field size.
• Click the create table button and the table will be added to the MDB file and you will see a new tab on the main screen.

The screenshot above shows and example of creating a simple members table.

When inputting fields enter the fieldname first then the data type followed by field size if necessary and primary key if necessary. When you have selected a data type from the drop down box that appears when clicking in the column the options will change when you select the field size column.

One or more fields can be made to be part of the primary key by selecting True in the Primary key column. Blank is the same as false.

Additional fields are added with the add field dialog as show in the next section.

 

 

 

To add a new field, select the table from the drop down box. Enter the new field's name and select the field type from the next drop down box. Enter the size of the field if applicable. Press the add button to create the new field. Fields are added one at a time so repeat the process for the next field.

 

 

To delete a field select the table from the drop down box. This will populate the field name drop down box below. Select the field you wish to delete and press the delete button. You will then be asked to confirm the delete with a yes or no dialog.

 

Add a child table linked to the current table allows you to see an additional grid showing only the related records for the current selected record of the parent table.

 

To setup select the child table from the drop down box and below select the two fields that link the two tables together.

 

Below is screen shot of the many-to-many dialog in MDB Viewer Plus. The screenshot is showing a classic example of a many-to-many relational database structure; Students and Courses.

 

The students to courses example students can be on more that one course and each course can have more than one student. A many-to-many relational structure requires three tables. The two list tables, in this example students and courses and a single link table to link the two forma tables together.

 

The new many-to-many dialog in MDB Viewer Plus provides a nice easy way to assign many-to-many records.

 

To configure the dialog perform the following:

• Select the first many table from the drop down box.
• When this done the drop down box next to it will be populated with the field list from the select table.
• Select the key field from this drop down box.
• Repeat this for the second many table.
• Select the link table from drop down box.
• Select the field from the link table that will link to the key field of the many table 1.
• Select the field from the link table that will link to the key field of the many table 2.

 

Assign records

To assign records simple highlight a record in many table 1 and a record in many table 2 and click the link button.

 

Removing a link

To remove a link simple select the row in the link table and click remove.

 

The screenshot below shows the table structure and indexes.

 

 

The toolbar gives access to the following functions.

• Add field
• Delete field
• Add Index
• Print table structure
• Copy table structure to the Windows clipboard
• Save structure to a text file

 

 

This is a screenshot of the Create Index dialog.

To create a new index for a table in your MDB or Accdb file simply select the table from the drop down box and then tick the fields that the index will span. Most of the time it will only be one field but if you require an unique index which uses multiple fields you can do so.

 

Enter the name for the new index in the box labelled "Index Name". Ideally this needs to be the same or similar to the field name. If it is a unique index tick the box below. If the field is required for the index tick "Disallow Null".

You can also create an index as a primary key by check the "Create Primary Key" box at the top of the dialog. This is for tables that don't already have a primary key field

 

 

 

 

 

Renaming a column as very straight forward as you can see from the screenshot below. Simply select the table, then the column name and enter the new column name.

 

Another way to achieve the same result as the rename column function is the follow the following steps.

1. Create a new field of the same type with the new field name.
2. Using the copy column function to copy data from the existing field to the new field.
3. Delete the old field.

 

 

This feature allows you to column all the data in a select column in a table to another column in that table.

Simply select the table first. Then select the source column and then the target column name. Click the copy button and the data will be copied.  This function can be used as a replacement for the rename function.

How to delete a table

1. From the table menu at the top of the main screen select "Delete table"
2. In the window shown below select the table name in the drop down box.
3. Click the delete button
4. You will then be asked to confirm the deletion of the table in question. Select yes.
5. The table and it's data will then be deleted.