Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / ASP
Article

StP Database Administrator

Rate me:
Please Sign up or sign in to vote.
4.81/5 (22 votes)
26 Aug 2003CPOL8 min read 189.5K   2.9K   67   26
Web-based administration toolkit for MS Access databases

Sample Image - dbadmin.jpg

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader Varonis
Israel Israel
I was born in small town Penza, Russia, in October 13th, 1975 yr. So my mother tongue is Russian. I finished the school there and learned in University, then I came to Israel and since then, I live there (or here *s*)
My profession is a C++ programmer under MS Windows platforms, but my hobby is Web development and ASP programming.

I started interesting in computers and programming somewere in 1990-1991 yrs., when my father brought home our first computer - Sinclair ZX Spectrum (he made it by himself). So I learned Basic and joined the Basic programmers club at my school (me and my friend were the only 2 guys from all school there, lol). After I finished the school (1992yr) I decided to continue my study at University and got specialization Operation Systems and Software Engineer. Although I still like my profession, but I always wanted something new, thus I learned HTML, Javascript and ASP which turned to be my hobby Smile | :)

Comments and Discussions

 
Generaldoubt Pin
dmrsantosh31-May-10 3:21
dmrsantosh31-May-10 3:21 
QuestionDSN's? Pin
stormydaniels15-Nov-08 11:32
stormydaniels15-Nov-08 11:32 
AnswerRe: DSN's? Pin
Philip Patrick15-Nov-08 18:05
professionalPhilip Patrick15-Nov-08 18:05 
GeneralGood job Pin
Olivier_Giulieri25-Aug-08 20:31
Olivier_Giulieri25-Aug-08 20:31 
QuestionMS Access 2007 Pin
jcfigueroa13-Nov-07 3:32
jcfigueroa13-Nov-07 3:32 
GeneralExcellent!! :-) Pin
dac0011-Apr-07 12:46
dac0011-Apr-07 12:46 
GeneralImpresive! Pin
gerardo_00118-Jul-06 19:21
gerardo_00118-Jul-06 19:21 
JokeGreat Job !!! Pin
jcfigueroa12-Jul-06 8:25
jcfigueroa12-Jul-06 8:25 
QuestionPlease give me a way for display pictures which store in DB Access 2003 by "OLE object"? Pin
tuyenhnp25-Sep-05 16:49
tuyenhnp25-Sep-05 16:49 
GeneralWell Done Pat!! Pin
e_screwz9-Jul-04 19:53
e_screwz9-Jul-04 19:53 
GeneralRe: Well Done Pat!! Pin
Xybex30-Aug-04 6:51
Xybex30-Aug-04 6:51 
GeneralVery well done! Pin
Nikolai Teofilov6-Apr-03 2:25
Nikolai Teofilov6-Apr-03 2:25 
GeneralRe: Very well done! Pin
Philip Patrick6-Apr-03 7:44
professionalPhilip Patrick6-Apr-03 7:44 
Generalthis is terrific. Pin
Andrew Bryan11-Oct-02 9:02
Andrew Bryan11-Oct-02 9:02 
GeneralVFC! Pin
Christopher Duncan8-Apr-02 13:39
Christopher Duncan8-Apr-02 13:39 
GeneralRe: VFC! Pin
Philip Patrick8-Apr-02 18:53
professionalPhilip Patrick8-Apr-02 18:53 
GeneralNew release can be found... Pin
Philip Patrick27-Mar-02 6:43
professionalPhilip Patrick27-Mar-02 6:43 
GeneralNice Job Pin
Bill Leibold23-Mar-02 5:26
Bill Leibold23-Mar-02 5:26 
GeneralThanx :) Pin
Philip Patrick23-Mar-02 7:55
professionalPhilip Patrick23-Mar-02 7:55 
GeneralRe: Thanx :) Pin
Bill Leibold23-Mar-02 10:14
Bill Leibold23-Mar-02 10:14 
QuestionQuestions? Pin
Philip Patrick23-Mar-02 1:09
professionalPhilip Patrick23-Mar-02 1:09 
AnswerRe: Questions? Pin
Member 5687765-Sep-03 23:17
Member 5687765-Sep-03 23:17 
GeneralRe: Questions? Pin
Philip Patrick6-Sep-03 6:01
professionalPhilip Patrick6-Sep-03 6:01 
Sure, Anfy. What problems do you have? You do not need to create that xml file by yourself. Instead, look in "scripts/inc_config.asp" file - there is a variable named DBA_cfgProfilePath. By defalt it is set to "dbadmin.xml", you can change it to a path to configuration file (including the filename). Use either relative (from dbadmin folder) or physical path. Of course you should have a write permissions set for the folder you want to save configuration file.

Philip Patrick
Web-site: www.stpworks.com
"Two beer or not two beer?" Shakesbeer
GeneralRe: Questions? Pin
Member 5687766-Sep-03 8:38
Member 5687766-Sep-03 8:38 
GeneralRe: Questions? Pin
Philip Patrick6-Sep-03 9:19
professionalPhilip Patrick6-Sep-03 9:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.