Advanced features

 

Creating a new table

Select the connection where you want to add the table to. Select New from the Table menu. The Table Structure window appears. Define the fields of the new table:

  1. Type in a Name for the field
  2. Choose a Type for the field
    The drop down list contains up to three parts:
    1. Common types like Text, Boolean, Double, Date or AutoNumber
    2. Well-known types of the connection's provider starting with an underscore like _smallint
    3. ADO types starting with ad like adVariant
  3. Specify Size and Scale. Some types need a size (like Text), some don't (like Double) whereas others may have both a size and a scale (like _number in Oracle).
  4. Select Nullable if your field is allowed to hold Null values.
  5. Select Unique if your field should be restricted to contain unique values (i.e., there are no two rows with the same value)

Use the Append or Insert button to add a new line, use the Delete button to delete a line or use the up/down button to move a line. Finally press Create Table to make the new table.

Tip: You may also copy a structure from an existing table (even from another connection) as a template for the new table. Use Design from the Table menu on the template table, apply your modifications to the list and finally press Create Table. Not enough? You may also export data from any table into a new table using the Export feature.

Modifying Data Definition

You may modify the structure of your table using Design from the Table menu. This opens a Design window showing the table's fields along with their properties. You may edit the information an then press Alter Table.

Depending on the provider this function is often restricted. The "official" ADO/ADOX ways to modify data definition are unfortunately only poorly supported by the various OLE DB providers. We're trying to do our best to use SQL in order to manage the modifications, but the SQL syntax on most databases often doesn't match standards like SQL92. If a special function doesn't work for your database, maybe you'll be able to enter an appropriate command manually using Open Command from the Table menu. If you've discovered such a workaround, please write us at service@blueshell.com and we will integrate it in future releases.

You may rename or delete tables using Rename Table or Delete Table from the Table menu. See also Renaming a node for a smart way to rename a table.

Exporting a table

Using blueshell Data Guy you may export from any connection to any other connection. Note that bDg's connection concept includes the scribbling-block.

  1. Define your source recordset
    Open a table and optionally define a filter. Do you want a more complex query? Use the Open Command... from the Table menu.
  2. Start the export
    Select Export from the Table menu.
  3. Choose the destination
    Simply click a table (if you want to append) or a connection (if you want to create a new table) of the tree view. Note that you may still use File → New or File → Open to create or open the destination connection while in export mode.
  4. Edit the details of the export process
    Fine-tune the structure of the new table and specify which source fields will go to which destination fields.
  5. Do the transfer
    Press the Start button. Watch the progress bar. You may interrupt the process by pressing the Pause button. You may abort the process by pressing the Stop button. You may specify error actions when an error occurs. You may even watch the source and destination (or any other) table during the transfer.
  6. Decide whether to commit or to rollback
    Finally you may commit the whole transaction or you may rollback it. Note that some providers (including the scribbling-block) don't support transactions.

Forwarding to Access

If you're an Access developer who works with more than one version of Microsoft Access you have to be aware of an Access peculiarity: Each version of Access has its own .mdb format, if you want to modify things like forms or queries you've to use the right version of Access. blueshell Data Guy helps you to detect the Access version of a .mdb file and allows you to forward it to the most appropriate Access application. blueshell Data Guy detects automatically which versions of Access are installed on your system: If you're forwarding an Access 98 file but you don't have Access 98 installed this file will be forwarded to the next installed version (e.g. Access 2002).

You may use the command Forward to Access from the File menu, the Explorer interface or the Command line interface to forward a .mdb file to Access.

Making a Backup and compacting a Jet database file

Jet (including Access) database files (*.mdb) should be compacted from time to time. Using the Backup/Compact command of blueshell Data Guy you can easily make a backup and compact a Jet database in one step. Just open your Jet database file and use the Backup/Compact command from the Files menu.

The original database file is backed up read-only with a numeric extension in the same folder. If your file is Sample.mdb, the first backup will be Sample;1.mdb, the second backup will be Sample;2.mdb etc.