TableSheet Utility

The TableSheet is my nickname for the worksheet that is automatically created by dragging an MDB file into Excel and picking one of its tables to import.

Excel automatically makes Row 1 the row with all the column names, the data starts in Row 2. The one difference with my "TableSheet" notion is that these utilities require the Worksheet name to be the same as the table name, whereas an imported MDB file table gets put on a sheet with the MDB file name as its name. My import button corrects this.

The utility is included partly to give an easily-found home to the code written originally to upload the data from the "Rausch" video unit, which is provided in MDB files to our corporate Oracle and PostgreSQL databases. The major utilities are made accessible at a button.

TableSheet Form Overview

There are only two functions on the form. The first only does what you can do by simply dragging in an MDB file: given the name of a table in the file in the "Table Name Here" text box, clicking on the "Get MDB File Table" button opens a standard Windows file-search dialogue, allows you to pick an MDB file, and creates a new "TableSheet" with the same name as the table, in your current workbook.

The second, "Upload Active TableSheet" assumes that there is already a table by the same name as the worksheet in whatever database you were last logged into with the TableWare form: the AM data warehouse, PostgreSQL, or any Oracle database you can log in to with the Login form button.

Further, it assumes that the column names in Row 1 correspond (exactly) to the column names up in the receiving database. The "Upload Active TableSheet" button uses the column names and the data below to construct a large INSERT statement for every row, naming the columns, quoting the text data, and converting any date columns to TO_DATE functions that work in both Oracle and PostgreSQL. Then it runs those INSERT statements; it's error handling is between primitive and non-existent, so it's up to the user to have their database set up correctly to receive it.

The TableSheet utilities may prove useful for other data provided by contract or entered on spreadsheets by our own field staff. You just need to create the appropriate table to receive the data, get the column names right on your worksheet, name the sheet after the table, and upload automatically rather than laboriously creating INSERT statements with large Excel string formulas.