StP Database Administrator

• Download source files - 158 Kb

Introduction

What is that? Well, StP DBAdmin allows you to manage MS Access databases through your web-site, using ASP and MS Jet DB Engine. In short, you don't need to download your MDB files, change them and upload again, all actions you can simply manage from this web-based DB administration toolkit.
You can take a look on live demo. Please be patient if the example database is not working correctly, some people may be testing it too hard.

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

Configuration

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.

Comments

StP Database Administrator was tested to work with MS Access 97/2000 databases and IIS5/IIS6 on MS Windows 2000/XP Professional. If you have other configuration, please test it on your computer first and if you encounter any problems, you can post it in my web-site forums or here, on CodeProject.

Version history

• 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 chosen 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 chosen 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 (thanks to Mariann D. for letting me know)
• Endless loop when database doesn't exists and trying to retrieve tables list (thanks 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