Developer's Notes

Database Administrator for MS Access

Overview

StP Database Administrator (DBAdmin below) is a tool that allows you to manage your MS Access databases through your browser, providing various set of functions you can perform with your databases, without having downloading them, changing and uploading again. Most of features needed for database management are included, you can even create a new blank databases on-line.

Features:

• View tables in your database
• Creating/modifying/deleting tables, data in tables, stored procedures, views and relations (foreign keys) between tables
• Cycling through records in table, modifying/adding data if needed.
• Editing extended properties of fields(columns) in table, like description, default value, etc.
• Generates SQL statement for each table with syntax highlighting.
• Creating/modifying/deleting indexes
• Executing views and stored procedures
• Provides human-readable description of each relation between tables
• Free-Type Query allows running your own SQL queries (single or multiple, separated with semicolon).
• When executing multiple queries at once you have an option to rollback (using transactions) if one of them fails, or continue regardless any errors.
• Compact and repair database
• Creating back up copy of your database and restoring from backup
• Exporting your database structure in form of SQL statements.
• Setting/changing/removing database password.
• Saves database paths for quick access later (doesn't save database passwords though).
• XML and Excel (text-delimited) data export.
• Browsing for your database, so you do not need to type the path by yourself
• Checking for a new version in one click.

Requirements

• ASP enabled web server (such as IIS or PWS) - required
• Sessions enabled on your web server - required
• Any Mozilla-compatible web browser: MS Internet Explorer 5.x or higher, Netscape 6.x or higher, Opera, Mozilla - required
• Write permissions on the web server - optional
• MSXML parser version 3 installed on the server - optional

Installation

Below are steps that you should perform to successfully install DBAdmin.

1. Double click on the downloaded archive file and choose a directory where all unzipped files should be placed. This directory is temporary and can be safely removed after installation.
2. Open file "scripts\inc_config.asp" in any text editor such as Notepad and find the line:
   Const DBA_cfgAdminPassword = "admin"
   This is your password as administrator. You should change it to your own, so just replace "admin" with your new password.
3. Optional - DBAdmin uses Session object to store some settings and transfer them from page to page. If you want to change those Session variables names, you can do this in same "scripts\inc_config.asp" file - see remarks inside it.
4. Optional - If you want to use XML file to store your configuration and database paths, you should update the value of DBA_cfgProfilePath in the same file, and set it to a virtual path to XML file. Note that the folder where this file will reside MUST be created and have write access.
5. Now save configuration file, create some directory on your Web server (for example "dbadmin") and upload all files, including subfolders into it.
6. Configuration is done. Open your browser and navigate to DBAdmin main page ("default.asp").

Upgrade from version 1.x

You should remove any previous version installed on your server and upload the new one as is (after changes described in Installation section).

Upgrade from version 2.x

Just upload all files to your server, but keep your configuration file (scripts/inc_config.asp), to save your current settings. You may notice that all saved paths to databases are gone(related only when upgrading from version 2.0.x). Don't worry, you will have just re-create them, since the structure of profile's XML file has been changed slightly.
Starting from version 2.1.1, DBAdmin allows you using different versions of MSXML components. By default it will auto-detect the installed component, prefering MSXML 4, but you can explictly tell it which component to use, by editing "scripts/inc_config.asp" file and adding a new constant named "DBA_cfgMSXMLProgID". Refer to "scripts/inc_config.asp" file included with this installation.

FAQ

• Cannot create a blank database
  Make sure that the folder in which you are trying to create a new database has write permissions. Usually there is always at least one folder that has it - a folder where your hosting provider lets you to place your MS Access databases
  
• I got an error "TypeLib not found" in inc_protect.asp
  A type library for ADO is not registered on your server, so you cannot use METADATA tag to specify it. Usually it happens on Windows 98 machines, but fortunately can be solved easy. Open the file "scripts\inc_config.asp" in any text editor (such as Notepad) and find the line below:
  <!-- METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
  This should be the last line in this file. Delete it and instead type this text:
  <!--#include file=adovbs.inc -->
  This fix should solve the problem of ADOType library
  
• I can't see Views created with Database Administrator in MS Access.
  MS Access doesn't support Views, only Stored Procedures (named Queries in MS Access) can be created and changed. Stp Database Administrator, which uses ADO, has full support for both Stored Procedures and Views. But if you want to be able to see your Views in MS Access later, always create them as Stored Procedures.
  
• I got error saying: "Cannot update. Database or Object is read-only".
  This error usually says that the folder where you put your database has no write permissions. If you got this error on your server, you should contact your hosting provider and ask which folder is designed for MS Access databases and place all your databases there (as well as DBAdmin configuration file). If the error occurs on your home computer, you should edit permissions of the folder and add IUSR_ComputerName (or Internet Guest Account) with write permissions.
  
• How to adjust permissions of the folder?
  Follow the link to Microsoft's Knowledge Base: http://support.microsoft.com/default.aspx?scid=kb;en-us;308418 (applies also to Windows 2000/NT).
  
• Why the application (IIS) freezes when trying to browse for database?
  This can occur if you have Norton Antivirus installed on the computer where IIS runs with Script Blocking feature enabled (even if Norton Antivirus is disabled by itself). To read more about this problem, follow this link:
  http://support.microsoft.com/default.aspx?scid=kb;en-us;295375 .
  
• After upgrading from version 2.x to version 2.1 I don't see databases list in "Existing database"
  The structure of XML configuration file has been changed slightly, you should use Browse button to add your databases again to the list.
  
• Can I open a database using DSN?
  Yes. Although it is not documented, but you are able to open a database using DSN (so-called DSN-connection). To do so, in the database path prompt (text field) type: "DSN=Your_DSN_Here", where Your_DSN_Here is your DSN. DBAdmin will know how to handle this type of connection

Versions history

Version 2.3

• New - Optional security. You can disable the internal security, which may be useful if you have installed DBAdmin in a secure folder.
• New - Session timeout can be configured from Settings page now. Maximum is 24 hours, which is a limitation of IIS.
• New - Allows renaming tables (note, that table creation/modified dates will be changed)
• New - Allows viewing of System tables. Read FAQ on the site if you have troubles with this feature (suggested by Ron)
• New - Added support for Linked tables (suggested by Ron).
• New - Boolean values in record edit form are now represented with checkboxes
• New - Added lookup feature for columns that related to other tables
• New - Built-in HTML editor for Text and Memo fields.
• New - Log-off button added
• Update - Find/Filter in table data view has finally replaced with true filter
• Update - Paging is now made easier (replaced to combo box)
• Bug fix - Unable (or error) to sort by columns that has a multi-word or registered words (submitted by Kevin Bates)
• Bug fix - Sometimes Default value got corrupted on the table's structure form
• Bug fix - When tryingt to delete a View or Stored Procedure, its name is not displayed on message box prompt
• Bug fix - Javascript error when you try to delete a table (submitted by Dominique Boyer)

Version 2.1.1

• Bug fix - DBAdmin works properly when ADOX is not available. Note, that not all the features are supported with pure SQL
• Bug fix - MSXML version now choosen automatically, version 4 is preferred (checked first)
• Bug fix - Error on hosts where MSXML object is unavailable
• Bug fix - Some strings were left without translation available.

Version 2.1

• New - Default page size (records per page) can be set now in Settings
• New - Opens databases from DSN
• New - Added feature for importing structure and data from another Access database
• New - Jump filter added to the data view and Free-Type Query. You can now specify one-column filter and jump directly to a record matched your criteria
• New - Interface's language can be choosen from Settings page
• New - Allows changing Locale (Language) of the database.
• Updated - Some more properties of database added
• Updated - Improved support for multiple languages.
• Bug fix - Editing records with multiple primary keys error
• Bug fix - On some hosts there was impossible to set default value of the column
• Bug fix - Compacting and repairing database could reset its locale language.
• Bug fix - If not able to delete a table, was no error reported.
• Some minor improvements in the code and usability

Version 2.0.2

• Bug fix - Javascript error on Settings page.
• Bug fix - Application hangs when trying to view data of a table with no columns
• Bug fix - Not able to open a database if folder has no write permissions.
• Bug fix - Unable to edit text fields with double quote (") in the data.
• Bug fix - Database can be removed from the saved list only when another one is opened
• Other minor bugs fixes

Version 2.0

• Major - The tool was redesigned both in code and look
• New - Editing Views and Stored Procedures
• New - Free-Type Query can run several SQL statements at once (separated with ";"), also as one transaction and/or ignoring errors
• New - Setting/changing/removing database password
• New - Editing column names
• New column properties for text and memo: Allow zero length, Compress Unicode (new columns only)
• New - several settings can be changed through a web-based interface, more will come. Uses XML file for storing
• New - shows database file size and how much bytes can be reclaimed by compacting the it
• New - database export: select tables, procedures and/or views and DBAdmin will generate an SQL script for them.
• Bug fix -Compacting password-protected databases

Version 1.7

• Delete table bug fixed
• Inserting NULL values bug fixed (submitted by Ivo Vis)
• HTTP headers on Windows NT bug fixed (submitted by Ivo Vis)
• Multi-language support added
• Added No-Cache HTTP header
• Password-protected databases support
• Cycling Prev/Next while updating or adding records in table (suggested by Christopher Duncan)
• Page size in table's data view and Free-Type Query can be changed dynamically
• Checking for latest version

Version 1.6

• Creating new empty databases
• Backup and restore database files
• Compact and repair MS Access 2000/97 database
• NOT NULL field attribute dropped to NULL bug fix
• Other minor bugs and improvements

Version 1.5.1

• Date/Time field couldn't be created bug fixed (thanx to Mariann D. for letting me know)
• Endless loop when database doesn't exists and trying to retrieve tables list (thanx to Raul)
• Other minor bug fixes and improvements

Version 1.5

• Multiple Primary Keys feature added
• Editing data Primary Keys columns (in case it is not an AutoNumber field)
• Tables' relationships viewing, creating
• Browse for database dialog improved. Now supports browsing on other hard drives and network mapped drives (not tested yet, if you will find a problem with it, let me know)
• Builds an SQL statement for creating same table with same indexes (foreign keys are not included)
• SQL reserved words highlighting in Views, Procedures and table statements
• XML and MS Excel (.csv) export
• Bugs fixes

Version 1.0 Beta

• Developed and released

Downloads

• DBAdmin Version 2.3. Latest version of Database Administrator
• Language packs. All available language packs: Chinese Simplified, Czech, German, Dutch, Spanish, French, Italian, Norwegian, Portuguese, Russian, Turkish

Screenshots

Below you can see some screenshots of DBAdmin. Click on the picture to view full size

Database selection screen

...

Tables list

...

Table designer

...

Stored procedures list

...

Views list and editing

...

Table relations

...

posted @ Wednesday, April 30, 2008 4:17 PM