Click here to Skip to main content
15,867,308 members
Articles / Web Development / HTML
Article

DB Tool : an ADO DB Viewer

Rate me:
Please Sign up or sign in to vote.
4.76/5 (23 votes)
9 Nov 20034 min read 124.3K   3K   26   13
DBTool is a little HTA (HTML application) to peek into databases.

Image 1

Introduction

A lot of small DB applications use Microsoft's Jet engine. Usually the way to take a look into such a DB is to open the MDB file in MS Access. However I often run into PCs where a DB application is installed and running, but MS Access isn't. So I wrote this little application to peek into MDB files even without MS Access.

To keep the application most flexible for changes and adjustments, it was written as a HTA (HTML Application). HTAs combine the Windows Scripting Host for code and the Internet Explorer for the user interface, to build simple applications. No fancy development environment is required, just a text editor of your choice (... and there is always Notepad).

Background

The Microsoft Data Access Components (MDAC) provide a runtime system for database access. A part of MDAC is ADO (ActiveX Data Objects). It uses SQL as the language for database operations.

I believe MDAC is preinstalled since Win2000. It can also be downloaded from Microsoft's website. Search for MDAC_TYP.EXE. There are also a couple of interesting articles like 'MDAC Installation' and 'Redistributing MDAC'.

What's inside the code

This little application may be also useful as a sample of various techniques, like:

  • How to create a HTA application
  • Scripting in VBS
  • Using ADO and ADOX
  • DHTML

All of the code is in a single file: DbTool.hta. The '.hta' extension and the HTA tag in the HTML header makes it an HTML application. Some advantages of HTA over HTML documents are:

  • The IE security mechanisms are less strict in HTA, i.e. many things are allowed in HTA that are prohibited in HTML files.
  • The window layout of an HTA application can be controlled better, like border style, maximize/minimize buttons etc.
  • Development/software-changes can be done on any Windows PC, no special environment is required. All the tools come with the OS and IE (since Win2000 and IE5).

The code is written in VBScript, however it could also be done in JScript.

In the HTML body, the user interface can be found as plain HTML code. It is very simple. There is:

  • an input field for the file selection (txtDbFile)
  • a text input field for a SQL query string (txtSqlQuery)
  • a button to execute the SQL query
  • a button to show all tables in the DB
  • a division to display the DB tables (div1)

Both buttons start functions in the script section:

  • btnDbTables_Click
    • div1 is used for display.
    • The selected DB is opened using the ADODB.Connection object.
    • The ADOX.Catalog object is used to access the structure of the DB, i.e. the tables and fields.
    • A list of all tables and the contained fields with their data types is generated.
    • The names of the tables are made sensitive with the onclick event.
    • Tooltips are created for the data type names.
    • Closes the display.
  • btnSqlQuery_Click
    • A new window is opened for display.
    • The selected DB is opened using the ADODB.Connection object.
    • A recordset (object ADODB.Recordset) is opened using the specified SQL query string.
    • If there are any records, they are displayed with all their fields.
    • Closes the display.

Using the application

To work with the program:

  • Start the application
  • Select a database file
  • Click on the 'Display DB Tables' button. Then the list of tables in the DB is shown at the bottom.
  • Click on a table name in this list. This automatically generates a SQL query string: Select * from <tablename> and clicks the 'Execute SQL Query' button for you. A new window is opened showing the result of the SQL query.
  • Create your own SQL queries and press the 'Execute SQL Query' button.

Points of interest

Many people know how to work with ADODB Connections and Recordsets. You can find plenty of information in the Internet about this topic. However I couldn't find that much about the ADOX extensions. It can show, create, delete and modify the internal structure of a DB. There is a lot of potential for extensions of the application, like create new tables, columns, keys etc. There is also information about relations between data tables. I couldn't come up yet with a good solution to show all this information.

This application is not only for users of the Jet engine. ADO supports a variety of databases (ODBC etc.). It should be pretty easy to make the changes for that: Just change the provider and connection string and you're done. However I don't know how far the support goes as far as ADOX is concerned?!?

The SQL query string also can be something else than a SELECT command, like an UPDATE command to make a change in the DB.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior)
Germany Germany
Occupation: SW engineer since 1981.

Business: Control and automation systems.

History
The 80s: Assembler, PL/M, RMX OS.
The 90s: C, C++, pSOS, VxWorks, Structured Analysis, Windows, networking, project management
98-03: WinNT/2000, COM with C++/ATL, UIs with VB6, design with UML, IP protocols
The new millenium: WinXP, .NET (all versions), C#, WF, WCF, WPF, ...

Comments and Discussions

 
GeneralThank you: Assisted SQL Diagnosis Pin
JTepoortenSMSmt23-May-11 19:35
JTepoortenSMSmt23-May-11 19:35 

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.