display table properties via SQL

I know how to modify the table. How can I display the table structure when I'm done?

kudos to CFHub for this one....
http://cfhub.com/advanced/ado/index.cfm

<cfscript>
//-----------------------------------------------
// Microsoft ADO
//
// Copyright (c) 1996-1998 Microsoft Corporation.
//
//
//
// ADO constants include file for JavaScript
//(Modified for CFSCRIPT by CFHub.com)
//-----------------------------------------------

//---- CursorTypeEnum Values ----
adOpenForwardOnly ="0";
adOpenKeyset ="1";
adOpenDynamic ="2";
adOpenStatic ="3";

//---- CursorOptionEnum Values ----
adHoldRecords ="0x00000100";
adMovePrevious ="0x00000200";
adAddNew ="0x01000400";
adDelete ="0x01000800";
adUpdate ="0x01008000";
adBookmark ="0x00002000";
adApproxPosition ="0x00004000";
adUpdateBatch ="0x00010000";
adResync ="0x00020000";
adNotify ="0x00040000";
adFind ="0x00080000";
adSeek ="0x00400000";
adIndex ="0x00800000";

//---- LockTypeEnum Values ----
adLockReadOnly ="1";
adLockPessimistic ="2";
adLockOptimistic ="3";
adLockBatchOptimistic ="4";

//---- ExecuteOptionEnum Values ----
adAsyncExecute ="0x00000010";
adAsyncFetch ="0x00000020";
adAsyncFetchNonBlocking ="0x00000040";
adExecuteNoRecords ="0x00000080";

//---- ConnectOptionEnum Values ----
adAsyncConnect ="0x00000010";

//---- ObjectStateEnum Values ----
adStateClosed ="0x00000000";
adStateOpen ="0x00000001";
adStateConnecting ="0x00000002";
adStateExecuting ="0x00000004";
adStateFetching ="0x00000008";

//---- CursorLocationEnum Values ----
adUseServer ="2";
adUseClient ="3";

//---- DataTypeEnum Values ----
adEmpty ="0";
adTinyInt ="16";
adSmallInt ="2";
adInteger ="3";
adBigInt ="20";
adUnsignedTinyInt ="17";
adUnsignedSmallInt ="18";
adUnsignedInt ="19";
adUnsignedBigInt ="21";
adSingle ="4";
adDouble ="5";
adCurrency ="6";
adDecimal ="14";
adNumeric ="131";
adBoolean ="11";
adError ="10";
adUserDefined ="132";
adVariant ="12";
adIDispatch ="9";
adIUnknown ="13";
adGUID ="72";
adDate ="7";
adDBDate ="133";
adDBTime ="134";
adDBTimeStamp ="135";
adBSTR ="8";
adChar ="129";
adVarChar ="200";
adLongVarChar ="201";
adWChar ="130";
adVarWChar ="202";
adLongVarWChar ="203";
adBinary ="128";
adVarBinary ="204";
adLongVarBinary ="205";
adChapter ="136";
adFileTime ="64";
adPropVariant ="138";
adVarNumeric ="139";
adArray ="0x2000";

//---- DataTypeEnum "SimpleType" Array ----
SimpleType = ArrayNew(1);
SimpleType[16] = "Numeric";
SimpleType[2] = "Numeric";
SimpleType[3] = "Numeric";
SimpleType[20] = "Numeric";
SimpleType[17] = "Numeric";
SimpleType[18] = "Numeric";
SimpleType[19] = "Numeric";
SimpleType[21] = "Numeric";
SimpleType[4] = "Numeric";
SimpleType[5] = "Numeric";
SimpleType[6] = "Numeric";
SimpleType[14] = "Numeric";
SimpleType[131] = "Numeric";
SimpleType[11] = "Boolean";
SimpleType[10] = "Other";
SimpleType[132] = "Other";
SimpleType[12] = "Other";
SimpleType[9] =   "Other";
SimpleType[13] = "Other";
SimpleType[72] = "Other";
SimpleType[7] =   "Date";
SimpleType[133] = "Date";
SimpleType[134] = "Date";
SimpleType[135] = "Date";
SimpleType[8] =   "Text";
SimpleType[129] = "Text";
SimpleType[200] = "Text";
SimpleType[201] = "Text";
SimpleType[130] = "Text";
SimpleType[202] = "Text";
SimpleType[203] = "Text";
SimpleType[128] = "Other";
SimpleType[204] = "Other";
SimpleType[205] = "Other";
SimpleType[136] = "Other";

//---- DataTypeEnum "DataBaseType" Array ----
DataBaseType = ArrayNew(1);
DataBaseType[16] = "TinyInt";
DataBaseType[2] = "SmallInt";
DataBaseType[3] = "Integer";
DataBaseType[20] = "BigInt";
DataBaseType[17] = "UnsignedTinyInt";
DataBaseType[18] = "UnsignedSmallInt";
DataBaseType[19] = "Int";
DataBaseType[21] = "UnsignedBigInt";
DataBaseType[4] = "Single";
DataBaseType[5] = "Double";
DataBaseType[6] = "Currency";
DataBaseType[14] = "Decimal";
DataBaseType[131] = "Numeric";
DataBaseType[11] = "Boolean";
DataBaseType[10] = "Error";
DataBaseType[132] = "UserDefined";
DataBaseType[12] = "Variant";
DataBaseType[9] = "IDispatch";
DataBaseType[13] = "IUnknown";
DataBaseType[72] = "GUID";
DataBaseType[7] = "Date";
DataBaseType[133] = "DBDate";
DataBaseType[134] = "DBTime";
DataBaseType[135] = "DBTimestamp";
DataBaseType[8] = "BStr";
DataBaseType[129] = "Char";
DataBaseType[200] = "VarChar";
DataBaseType[201] = "LongVarChar";
DataBaseType[130] = "WChar";
DataBaseType[202] = "VarWChar";
DataBaseType[203] = "LongVarWChar";
DataBaseType[128] = "Binary";
DataBaseType[204] = "VarBinary";
DataBaseType[205] = "LongVarBinary";
DataBaseType[136] = "Chapter";

//---- FieldAttributeEnum Values ----
adFldMayDefer ="0x00000002";
adFldUpdatable ="0x00000004";
adFldUnknownUpdatable ="0x00000008";
adFldFixed ="0x00000010";
adFldIsNullable ="0x00000020";
adFldMayBeNull ="0x00000040";
adFldLong ="0x00000080";
adFldRowID ="0x00000100";
adFldRowVersion ="0x00000200";
adFldCacheDeferred ="0x00001000";
adFldIsChapter ="0x00002000";
adFldNegativeScale ="0x00004000";
adFldKeyColumn ="0x00008000";
adFldIsRowURL ="0x00010000";
adFldIsDefaultStream ="0x00020000";
adFldIsCollection ="0x00040000";

//---- EditModeEnum Values ----
adEditNone ="0x0000";
adEditInProgress ="0x0001";
adEditAdd ="0x0002";
adEditDelete ="0x0004";

//---- RecordStatusEnum Values ----
adRecOK ="0x0000000";
adRecNew ="0x0000001";
adRecModified ="0x0000002";
adRecDeleted ="0x0000004";
adRecUnmodified ="0x0000008";
adRecInvalid ="0x0000010";
adRecMultipleChanges ="0x0000040";
adRecPendingChanges ="0x0000080";
adRecCanceled ="0x0000100";
adRecCantRelease ="0x0000400";
adRecConcurrencyViolation ="0x0000800";
adRecIntegrityViolation ="0x0001000";
adRecMaxChangesExceeded ="0x0002000";
adRecObjectOpen ="0x0004000";
adRecOutOfMemory ="0x0008000";
adRecPermissionDenied ="0x0010000";
adRecSchemaViolation ="0x0020000";
adRecDBDeleted ="0x0040000";

//---- GetRowsOptionEnum Values ----
adGetRowsRest ="-1";

//---- PositionEnum Values ----
adPosUnknown ="-1";
adPosBOF ="-2";
adPosEOF ="-3";

//---- BookmarkEnum Values ----
adBookmarkCurrent ="0";
adBookmarkFirst ="1";
adBookmarkLast ="2";

//---- MarshalOptionsEnum Values ----
adMarshalAll ="0";
adMarshalModifiedOnly ="1";

//---- AffectEnum Values ----
adAffectCurrent ="1";
adAffectGroup ="2";
adAffectAllChapters ="4";

//---- ResyncEnum Values ----
adResyncUnderlyingValues ="1";
adResyncAllValues ="2";

//---- CompareEnum Values ----
adCompareLessThan ="0";
adCompareEqual ="1";
adCompareGreaterThan ="2";
adCompareNotEqual ="3";
adCompareNotComparable ="4";

//---- FilterGroupEnum Values ----
adFilterNone ="0";
adFilterPendingRecords ="1";
adFilterAffectedRecords ="2";
adFilterFetchedRecords ="3";
adFilterConflictingRecords ="5";

//---- SearchDirectionEnum Values ----
adSearchForward ="1";
adSearchBackward ="-1";

//---- PersistFormatEnum Values ----
adPersistADTG ="0";
adPersistXML ="1";

//---- StringFormatEnum Values ----
adClipString ="2";

//---- ConnectPromptEnum Values ----
adPromptAlways ="1";
adPromptComplete ="2";
adPromptCompleteRequired ="3";
adPromptNever ="4";

//---- ConnectModeEnum Values ----
adModeUnknown ="0";
adModeRead ="1";
adModeWrite ="2";
adModeReadWrite ="3";
adModeShareDenyRead ="4";
adModeShareDenyWrite ="8";
adModeShareExclusive ="0xc";
adModeShareDenyNone ="0x10";
adModeRecursive ="0x400000";

//---- RecordCreateOptionsEnum Values ----
adCreateCollection ="0x00002000";
adCreateStructDoc ="0x80000000";
adCreateNonCollection ="0x00000000";
adOpenIfExists ="0x02000000";
adCreateOverwrite ="0x04000000";
adFailIfNotExists ="-1";

//---- RecordOpenOptionsEnum Values ----
adOpenRecordUnspecified ="-1";
adOpenSource ="0x00800000";
adOpenAsync ="0x00001000";
adDelayFetchStream ="0x00004000";
adDelayFetchFields ="0x00008000";

//---- IsolationLevelEnum Values ----
adXactUnspecified ="0xffffffff";
adXactChaos ="0x00000010";
adXactReadUncommitted ="0x00000100";
adXactBrowse ="0x00000100";
adXactCursorStability ="0x00001000";
adXactReadCommitted ="0x00001000";
adXactRepeatableRead ="0x00010000";
adXactSerializable ="0x00100000";
adXactIsolated ="0x00100000";

//---- XactAttributeEnum Values ----
adXactCommitRetaining ="0x00020000";
adXactAbortRetaining ="0x00040000";

//---- PropertyAttributesEnum Values ----
adPropNotSupported ="0x0000";
adPropRequired ="0x0001";
adPropOptional ="0x0002";
adPropRead ="0x0200";
adPropWrite ="0x0400";

//---- ErrorValueEnum Values ----
adErrProviderFailed ="0xbb8";
adErrInvalidArgument ="0xbb9";
adErrOpeningFile ="0xbba";
adErrReadFile ="0xbbb";
adErrWriteFile ="0xbbc";
adErrNoCurrentRecord ="0xbcd";
adErrIllegalOperation ="0xc93";
adErrCantChangeProvider ="0xc94";
adErrInTransaction ="0xcae";
adErrFeatureNotAvailable ="0xcb3";
adErrItemNotFound ="0xcc1";
adErrObjectInCollection ="0xd27";
adErrObjectNotSet ="0xd5c";
adErrDataConversion ="0xd5d";
adErrObjectClosed ="0xe78";
adErrObjectOpen ="0xe79";
adErrProviderNotFound ="0xe7a";
adErrBoundToCommand ="0xe7b";
adErrInvalidParamInfo ="0xe7c";
adErrInvalidConnection ="0xe7d";
adErrNotReentrant ="0xe7e";
adErrStillExecuting ="0xe7f";
adErrOperationCancelled ="0xe80";
adErrStillConnecting ="0xe81";
adErrInvalidTransaction ="0xe82";
adErrUnsafeOperation ="0xe84";
adwrnSecurityDialog ="0xe85";
adwrnSecurityDialogHeader ="0xe86";
adErrIntegrityViolation ="0xe87";
adErrPermissionDenied ="0xe88";
adErrDataOverflow ="0xe89";
adErrSchemaViolation ="0xe8a";
adErrSignMismatch ="0xe8b";
adErrCantConvertvalue ="0xe8c";
adErrCantCreate ="0xe8d";
adErrColumnNotOnThisRow ="0xe8e";
adErrURLIntegrViolSetColumns ="0xe8f";
adErrURLDoesNotExist ="0xe8f";
adErrTreePermissionDenied ="0xe90";
adErrInvalidURL ="0xe91";
adErrResourceLocked ="0xe92";
adErrResourceExists ="0xe93";
adErrCannotComplete ="0xe94";
adErrVolumeNotFound ="0xe95";
adErrOutOfSpace ="0xe96";
adErrResourceOutOfScope ="0xe97";
adErrUnavailable ="0xe98";
adErrURLNamedRowDoesNotExist ="0xe99";
adErrDelResOutOfScope ="0xe9a";
adErrPropInvalidColumn ="0xe9b";
adErrPropInvalidOption ="0xe9c";
adErrPropInvalidValue ="0xe9d";
adErrPropConflicting ="0xe9e";
adErrPropNotAllSettable ="0xe9f";
adErrPropNotSet ="0xea0";
adErrPropNotSettable ="0xea1";
adErrPropNotSupported ="0xea2";
adErrCatalogNotSet ="0xea3";
adErrCantChangeConnection ="0xea4";
adErrFieldsUpdateFailed ="0xea5";
adErrDenyNotSupported ="0xea6";
adErrDenyTypeNotSupported ="0xea7";

//---- ParameterAttributesEnum Values ----
adParamSigned ="0x0010";
adParamNullable ="0x0040";
adParamLong ="0x0080";

//---- ParameterDirectionEnum Values ----
adParamUnknown ="0x0000";
adParamInput ="0x0001";
adParamOutput ="0x0002";
adParamInputOutput ="0x0003";
adParamReturnValue ="0x0004";

//---- CommandTypeEnum Values ----
adCmdUnknown ="0x0008";
adCmdText ="0x0001";
adCmdTable ="0x0002";
adCmdStoredProc ="0x0004";
adCmdFile ="0x0100";
adCmdTableDirect ="0x0200";

//---- EventStatusEnum Values ----
adStatusOK ="0x0000001";
adStatusErrorsOccurred ="0x0000002";
adStatusCantDeny ="0x0000003";
adStatusCancel ="0x0000004";
adStatusUnwantedEvent ="0x0000005";

//---- EventReasonEnum Values ----
adRsnAddNew ="1";
adRsnDelete ="2";
adRsnUpdate ="3";
adRsnUndoUpdate ="4";
adRsnUndoAddNew ="5";
adRsnUndoDelete ="6";
adRsnRequery ="7";
adRsnResynch ="8";
adRsnClose ="9";
adRsnMove ="10";
adRsnFirstChange ="11";
adRsnMoveFirst ="12";
adRsnMoveNext ="13";
adRsnMovePrevious ="14";
adRsnMoveLast ="15";

//---- SchemaEnum Values ----
adSchemaProviderSpecific ="-1";
adSchemaAsserts ="0";
adSchemaCatalogs ="1";
adSchemaCharacterSets ="2";
adSchemaCollations ="3";
adSchemaColumns ="4";
adSchemaCheckConstraints ="5";
adSchemaConstraintColumnUsage ="6";
adSchemaConstraintTableUsage ="7";
adSchemaKeyColumnUsage ="8";
adSchemaReferentialConstraints ="9";
adSchemaTableConstraints ="10";
adSchemaColumnsDomainUsage ="11";
adSchemaIndexes ="12";
adSchemaColumnPrivileges ="13";
adSchemaTablePrivileges ="14";
adSchemaUsagePrivileges ="15";
adSchemaProcedures ="16";
adSchemaSchemata ="17";
adSchemaSQLLanguages ="18";
adSchemaStatistics ="19";
adSchemaTables ="20";
adSchemaTranslations ="21";
adSchemaProviderTypes ="22";
adSchemaViews ="23";
adSchemaViewColumnUsage ="24";
adSchemaViewTableUsage ="25";
adSchemaProcedureParameters ="26";
adSchemaForeignKeys ="27";
adSchemaPrimaryKeys ="28";
adSchemaProcedureColumns ="29";
adSchemaDBInfoKeywords ="30";
adSchemaDBInfoLiterals ="31";
adSchemaCubes ="32";
adSchemaDimensions ="33";
adSchemaHierarchies ="34";
adSchemaLevels ="35";
adSchemaMeasures ="36";
adSchemaProperties ="37";
adSchemaMembers ="38";
adSchemaTrustees ="39";

//---- FieldStatusEnum Values ----
adFieldOK ="0";
adFieldCantConvertValue ="2";
adFieldIsNull ="3";
adFieldTruncated ="4";
adFieldSignMismatch ="5";
adFieldDataOverflow ="6";
adFieldCantCreate ="7";
adFieldUnavailable ="8";
adFieldPermissionDenied ="9";
adFieldIntegrityViolation ="10";
adFieldSchemaViolation ="11";
adFieldBadStatus ="12";
adFieldDefault ="13";
adFieldIgnore ="15";
adFieldDoesNotExist ="16";
adFieldInvalidURL ="17";
adFieldResourceLocked ="18";
adFieldResourceExists ="19";
adFieldCannotComplete ="20";
adFieldVolumeNotFound ="21";
adFieldOutOfSpace ="22";
adFieldCannotDeleteSource ="23";
adFieldReadOnly ="24";
adFieldResourceOutOfScope ="25";
adFieldAlreadyExists ="26";
adFieldPendingInsert ="0x10000";
adFieldPendingDelete ="0x20000";
adFieldPendingChange ="0x40000";
adFieldPendingUnknown ="0x80000";
adFieldPendingUnknownDelete ="0x100000";

//---- SeekEnum Values ----
adSeekFirstEQ ="0x1";
adSeekLastEQ ="0x2";
adSeekAfterEQ ="0x4";
adSeekAfter ="0x8";
adSeekBeforeEQ ="0x10";
adSeekBefore ="0x20";

//---- ADCPROP_UPDATECRITERIA_ENUM Values ----
adCriteriaKey ="0";
adCriteriaAllCols ="1";
adCriteriaUpdCols ="2";
adCriteriaTimeStamp ="3";

//---- ADCPROP_ASYNCTHREADPRIORITY_ENUM Values ----
adPriorityLowest ="1";
adPriorityBelowNormal ="2";
adPriorityNormal ="3";
adPriorityAboveNormal ="4";
adPriorityHighest ="5";

//---- ADCPROP_AUTORECALC_ENUM Values ----
adRecalcUpFront ="0";
adRecalcAlways ="1";

//---- ADCPROP_UPDATERESYNC_ENUM Values ----

//---- ADCPROP_UPDATERESYNC_ENUM Values ----

//---- MoveRecordOptionsEnum Values ----
adMoveUnspecified ="-1";
adMoveOverWrite ="1";
adMoveDontUpdateLinks ="2";
adMoveAllowEmulation ="4";

//---- CopyRecordOptionsEnum Values ----
adCopyUnspecified ="-1";
adCopyOverWrite ="1";
adCopyAllowEmulation ="4";
adCopyNonRecursive ="2";

//---- StreamTypeEnum Values ----
adTypeBinary ="1";
adTypeText ="2";

//---- LineSeparatorEnum Values ----
adLF ="10";
adCR ="13";
adCRLF ="-1";

//---- StreamOpenOptionsEnum Values ----
adOpenStreamUnspecified ="-1";
adOpenStreamAsync ="1";
adOpenStreamFromRecord ="4";

//---- StreamWriteEnum Values ----
adWriteChar ="0";
adWriteLine ="1";

//---- SaveOptionsEnum Values ----
adSaveCreateNotExist ="1";
adSaveCreateOverWrite ="2";

//---- FieldEnum Values ----
adDefaultStream ="-1";
adRecordURL ="-2";

//---- StreamReadEnum Values ----
adReadAll ="-1";
adReadLine ="-2";

//---- RecordTypeEnum Values ----
adSimpleRecord ="0";
adCollectionRecord ="1";
adStructDoc ="2";
</cfscript>



<cfobject type="COM"
          name="ConnectionObject"
          class="ADODB.Connection"
          action="CREATE">

<cfscript>

  Driver="Microsoft Access Driver (*.mdb)";
  DataBasePath="D:\Inetpub\wwwroot\EE\ee.mdb";
  Source="Driver=#Driver#;DBQ=#DataBasePath#";

  ConnectionObject.Open("#Source#",
                       adModeUnknown,
                       adFailIfNotExists,
                       adOpenRecordUnspecified);

  MyFieldList=ConnectionObject.OpenSchema(adSchemaColumns);
 
    AllRecords=MyFieldList.Fields;

        /*These prperties are available
          through the OpenSchema method.*/
      ColumnName=AllRecords.Item("COLUMN_NAME");
      DataType=AllRecords.Item("DATA_TYPE");
      TableName=AllRecords.Item("TABLE_NAME");
      
  while(NOT MyFieldList.EOF){  
    WriteOutput(TableName.value);
    WriteOutput(" <br> ");  
    WriteOutput(ColumnName.value);
    WriteOutput(" <br> ");
    WriteOutput(DataBaseType[DataType.value]);
    WriteOutput(" <br> ");
    WriteOutput(SimpleType[DataType.value]);        
    WriteOutput(" <hr> ");  
           
    MyFieldList.MoveNext();  
  }

</cfscript>          


<!--- Close Object--->    
<cfset ConnectionObject.Close()>

just change the following to the appropriate path

DataBasePath="D:\Inetpub\wwwroot\EE\ee.mdb";

Well there is a custom tag that can be used :
pls go thru the code below - there are 2 files !

dbinfo.cfm =================================================================================



<!---
  ===================================================================
  utility:          cf_dbinfo
  author:           anuj gakhar
  email:            jimmy_282@yahoo.com
  ===================================================================
  description:

  this tag will give you all the information about your MSACCESS,MSSQL and MYsql databases.
  For MSAccess:
          tables,queries,reports,forms,modules and macros
For MSSQL:
            tables, triggers, stored procedures.                  
For Mysql:
            tables      
 
  For MSSQL and Mysql:
        this tag can return column names and types of a particular table or yield info abt a sp or trigger.

For MSACESS:
  this tag makes use of the msysobjects system object lying inside
  your databse. if you get an error like
  "there is no read permission on msysobjects"
  while executing this tag, then follow these steps.
  1) open your .mdb file in microsoft access.
  2) go to tools->security->user and group permissions.
  3) click on msysobjects and assign it the "read data" permission.
 
  now, if you don't see msysobjects in "user group and permissions"
  follow these steps.
  1)go to tools->options->view.
  2)check the "system objects" checkbox.
  3)click ok.
 
  _______________________________________________
  Keep this header IN here if you are using this tag
  Written By : Anuj Gakhar
  E-mail : anuj@the-whizkids.com
  ________________________________________________  
--->
      <CFPARAM name="proceed" default="yes">
      <CFPARAM name="error_message" default="">
      <CFPARAM name="attributes.dsn" default="">
      <CFPARAM name="attributes.dbusername" default="">
      <CFPARAM name="attributes.dbpassword" default="">
      <CFPARAM name="attributes.dbtype" default="MSACCESS">
      <CFPARAM name="attributes.object" default=""><!--- check for required attribute --->            
      <CFIF (isdefined("attributes.dsn") is "no")>            
            <CFSET proceed = "no">            
            <CFSET error_message = "dsn attribute is required!">
      </CFIF>
      <!--- check that required attribute is not empty --->            
      <CFIF trim("#attributes.dsn#") eq "">            
            <CFSET proceed = "no">            
            <CFSET error_message = "dsn attribute should not be left blank!">
      </CFIF>
                  <CFIF not Listfind("MSACCESS,MSSQL,MYSQL","#attributes.dbtype#")>            
            <CFSET proceed = "no">            
            <CFSET error_message = "the parameter DBTYPE is Invalid. Only MSACCESS,MSSQL and MYSQL are supported">
      </CFIF>
                  <CFIF proceed>            <!--- send a dummy query to the db to test the connectivity --->
            <CFTRY>
            <CFQUERY datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select 1;
            </CFQUERY>
            <CFCATCH type="database">            
            <CFSET proceed = "no">            
            <CFSET error_message = "#cfcatch.message#"></CFCATCH></CFTRY>
      </CFIF>
      <!--- everything seems to be fine, so let them in --->            
      <CFIF proceed>
            <CFSWITCH expression="#attributes.dbtype#">
            <CFCASE value="MSACCESS"><!--- tables --->
            <CFQUERY name="gettables" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (left$([name],4) <> 'msys')
                              and (msysobjects.type)=1
                              order by msysobjects.name;
            </CFQUERY>
            <!--- add the tables to a list --->            
            <CFSET tables = "">
            <CFLOOP query="gettables">
                                    <CFSET tables= #listappend(tables,#name#)#>
            </CFLOOP>
            <!--- send this list to the caller template --->            
            <CFSET caller.tables = tables><!--- queries --->
            <CFQUERY name="getqueries" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)=5
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET queries = "">
            <CFLOOP query="getqueries">
                                    <CFSET tables= #listappend(queries,#name#)#>
            </CFLOOP>
                        <CFSET caller.queries = queries><!--- forms --->
            <CFQUERY name="getforms" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)=-32768
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET forms="">
            <CFLOOP query="getforms">
                                    <CFSET forms = #listappend(forms,#name#)#>
            </CFLOOP>
                        <CFSET caller.forms = forms><!--- reports --->
            <CFQUERY name="getreports" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)= -32764
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET reports="">
            <CFLOOP query="getreports">
                                    <CFSET reports = #listappend(reports,#name#)#>
            </CFLOOP>
                        <CFSET caller.reports=reports><!--- modules --->
            <CFQUERY name="getmodules" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)= -32761
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET modules="">
            <CFLOOP query="getmodules">
                                    <CFSET modules = #listappend(modules,#name#)#>
            </CFLOOP>
                        <CFSET caller.modules=modules><!--- macros --->
            <CFQUERY name="getmacros" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)= -32766
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET macros="">
            <CFLOOP query="getmacros">
                                    <CFSET macros = #listappend(macros,#name#)#>
            </CFLOOP>
                        <CFSET caller.macros=macros></CFCASE>
            <CFCASE value="MSSQL">                  
            <CFIF not len("#attributes.object#")>                  <!--- tables --->
                  <CFQUERY name="gettables" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        SELECT name FROM sysobjects WHERE xtype='U' ORDER BY name ;
                  </CFQUERY>
                  <!--- add the tables to a list --->                  
                  <CFSET tables = "">
                  <CFLOOP query="gettables">
                                                <CFSET tables= #listappend(tables,#name#)#>
                  </CFLOOP>
                  <!--- send this list to the caller template --->                  
                  <CFSET caller.tables = tables><!--- stored Procedures --->
                  <CFQUERY name="getsps" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        SELECT name FROM sysobjects
                                WHERE type='P' AND
                                ObjectProperty(ID,'IsMSShipped')=0 ;
                  </CFQUERY>
                                    <CFSET sps = "">
                  <CFLOOP query="getsps">
                                                <CFSET #listappend(sps,#name#)#>
                  </CFLOOP>
                                    <CFSET caller.sps = sps><!--- Triggers --->
                  <CFQUERY name="gettriggers" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        SELECT name FROM sysobjects WHERE xtype='TR';
                  </CFQUERY>
                                    <CFSET triggers = "">
                  <CFLOOP query="gettriggers">
                                                <CFSET #listappend(triggers,#name#)#>
                  </CFLOOP>
                                    <CFSET caller.triggers = triggers>
            <CFELSE>
<!--- see the object passes is a table or sp or trigger--->
                  <CFQUERY name="ch" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        SELECT name FROM sysobjects WHERE xtype='u' AND name='#trim(attributes.object)#' ORDER BY name ;
                  </CFQUERY>
                                          <CFIF ch.recordcount>                        <!--- Its a table --->
                        <CFTRY>
                        <CFQUERY name="ch1" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                              exec sp_columns @table_name='#trim(attributes.object)#';
                        </CFQUERY>
                        <CFCATCH type="database">                        
                        <CFSET proceed="no">
                        <CFOUTPUT>
<STRONG>DBINFO Error</STRONG>: #cfcatch.message#
                        </CFOUTPUT>
                        <CFABORT></CFCATCH></CFTRY>
                        <CFLOOP query="ch1">
                              <CFOUTPUT>
                                    #ch1.column_name# --- #ch1.type_name#                                          
                                    <CFIF #ch1.type_name# EQ "varchar" OR #ch1.type_name# EQ "char">                                          (#ch1.length#)
                                    </CFIF>
<BR>
                              </CFOUTPUT>
                        </CFLOOP>
                  <CFELSE>
<!--- Its a SP or Trigger --->
                        <CFTRY>
                        <CFQUERY name="ch1" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                              exec sp_helptext '#trim(attributes.object)#';
                        </CFQUERY>
                        <CFCATCH type="database">                        
                        <CFSET proceed="no">
                        <CFOUTPUT>
<STRONG>DBINFO Error</STRONG>: #cfcatch.message#
                        </CFOUTPUT>
                        <CFABORT></CFCATCH></CFTRY>
                        <CFLOOP query="ch1">
                              <CFOUTPUT>
                                    #ch1.text#<BR>
                              </CFOUTPUT>
                        </CFLOOP>
                  </CFIF>
            </CFIF>
</CFCASE>
            <CFCASE value="MYSQL">
            <CFQUERY name="getdb" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select database() as d;
            </CFQUERY>
                              <CFIF not len("#attributes.object#")>                  <!--- tables --->
                  <CFQUERY name="gettables" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        show tables ;
                  </CFQUERY>
                  <!--- add the tables to a list --->                  
                  <CFSET tables = "">
                  <CFLOOP query="gettables">
                                                <CFSET tables= #listappend(tables,#Evaluate("tables_in_" & #getdb.d#)#)#>
                  </CFLOOP>
                  <!--- send this list to the caller template --->                  
                  <CFSET caller.tables = tables>
            <CFELSE>
                  <CFQUERY name="ch1" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        describe #trim(attributes.object)#;
                  </CFQUERY>
<STRONG>Field - Type - Null - Key - Default - Extra</STRONG><BR>
                  <CFLOOP query="ch1">
                        <CFOUTPUT>
                              #field# - #type# - #null# - #key# - #default# - #extra#<BR>
                        </CFOUTPUT>
                  </CFLOOP>
            </CFIF>
</CFCASE>
            <CFDEFAULTCASE>            
            <CFSET proceed = "no">            
            <CFSET error_message = "the parameter DBTYPE is Invalid. Only MSACCESS,MSSQL and MYSQL are supported"></CFDEFAULTCASE></CFSWITCH>
      <CFELSE>
            <CFOUTPUT>
<H4>dbinfo error!<BR>#error_message#</H4>
            </CFOUTPUT>
            <CFABORT>
      </CFIF>



dbinfotest.cfm==========================================================================
<!--- Some Example Usage
FOR MSACCESS
<cf_dbinfo dsn="test" dbusername="sa" dbpassword="123">
no need of dbtype here - default is MSACCESS
----------------------------------------
FOR MSSQL
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="mytable">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="MyStoredProc">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="MyTrigger">
-------------------------------------
FOR MYSQL
<cf_dbinfo dsn="test" dbtype="MYSQL" dbusername="root" dbpassword="">
OR
<cf_dbinfo dsn="test" dbtype="MYSQL" dbusername="sa" dbpassword="123" object = "MyTable">
-------------------------------------
__________________________________________________
All Errors, Bugs, Feature Requests, Comments to anuj@the-whizkids.com
___________________________________________________
ENJOY!
--->
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123">

<cfoutput>
<!--- Display Tables --->
<cfif isdefined("tables")>
<cfif listlen(tables)>
<strong><u>Tables</u></strong><br>
<cfloop from="1" to="#listlen(tables)#" index="i">
#ListGetAt(tables,"#i#")# <br>
</cfloop>
<cfelse>
No Tables!
</cfif>
</cfif>
<br>
<!--- Display Queries --->
<cfif isdefined("queries")>
<cfif listlen(queries)>
<strong><u>Queries</u></strong><br>
<cfloop from="1" to="#listlen(queries)#" index="i">
#ListGetAt(queries,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Queries!</strong>
</cfif>
</cfif>
<br>
<!--- Display Reports --->
<cfif isdefined("reports")>
<cfif listlen(Reports)>
<strong><u>Reports</u></strong><br>
<cfloop from="1" to="#listlen(reports)#" index="i">
#ListGetAt(reports,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Reports!</strong>
</cfif>
</cfif>
<br>
<!--- Display Forms --->
<cfif isdefined("forms")>
<cfif listlen(Forms)>
<strong><u>Forms</u></strong><br>
<cfloop from="1" to="#listlen(forms)#" index="i">
#ListGetAt(forms,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Forms!</strong>
</cfif>
</cfif>
<br>
<!--- Display Macros --->
<cfif isdefined("macros")>
<cfif listlen(Macros)>
<strong><u>Macros</u></strong><br>
<cfloop from="1" to="#listlen(macros)#" index="i">
#ListGetAt(macros,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Macros!</strong>
</cfif>
</cfif>
<br>
<!--- Display Modules --->
<cfif isdefined("modules")>
<cfif listlen(Modules)>
<strong><u>Modules</u></strong><br>
<cfloop from="1" to="#listlen(modules)#" index="i">
#ListGetAt(modules,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Modules!</strong>
</cfif>
</cfif>

<!--- Display Stored Procedures --->
<cfif isdefined("sps")>
<cfif listlen(sps)>
<strong><u>Stored Procedures</u></strong><br>
<cfloop from="1" to="#listlen(sps)#" index="i">
#ListGetAt(sps,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Stored procedures!</strong>
</cfif>
</cfif>

<!--- Display Stored Procedures --->
<cfif isdefined("triggers")>
<cfif listlen(triggers)>
<strong><u>Triggers</u></strong><br>
<cfloop from="1" to="#listlen(triggers)#" index="i">
#ListGetAt(triggers,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Triggers!</strong>
</cfif>
</cfif>
</cfoutput>

==============================================================================

that shld solve ur worries for "Only MSACCESS,MSSQL and MYSQL"

K'Rgds
Anand

anand, this won't give you the fields in the tables, just the higher level objects (forms, table names, modules, etc.) and only if you modify the database security settings.

I need to see field names, types, and keys. If you are correct, jyokum, and this utility cannot provide that information, it won't help.

Have any of you tried using it?

I was really hoping for something simple like the DESCRIBE function in some flavors of SQL.

did you try using the code I posted? it displays the field names, types, and lengths

I haven't tried it. Can you tell me how to implement it?

just to see it work, copy all the code to a new page.
change the following line to reflect the actual path of your database

DataBasePath="D:\Inetpub\wwwroot\EE\ee.mdb";

then run it. All the table & fields will be displayed for you.

read the article at CFHub to get the particulars about it.

Hi jyokum,
Looks like you're on the right track. I found the tutorial you are referring to and am trying to work through the code. I created the adocfscript.cfm file (the one you pasted above, which appears on http://cfhub.com/advanced/ado/constants.cfm), and I'm trying to use the next script in the tutorial to connect to my database and there I'm having trouble. I created this file (based on tutorial part 2 at http://cfhub.com/advanced/ado/connection_string.cfm) and saved it as adotest.cfm:

<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm">

<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
          name="MyObject"
          class="ADODB.Connection"
          action="CREATE">        

<cfscript>
    //Use these variables to build a valid connection string
  Driver="Microsoft Access Driver (*.mdb)";
  DataBasePath="..\database\mydb.mdb";
 
  Source="Driver=#Driver#;DBQ=#DataBasePath#";

    //Open a connection to the "northwinds" database
  MyObject.Open("#Source#",
                 adModeUnknown,
                 adFailIfNotExists,
                 adOpenRecordUnspecified);
                 
  WriteOutput("<h3>Success!!</h3>");
</cfscript>          


<!--- Close --->    
<cfset MyObject.Close()>  

When I call up the file I get the following error message: "[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides." Obviously ColdFusion is looking for an ODBC connection, but the code seems to be trying to circumvent ODBC by creating a connection using ADO. How can I make it work?

try using a full path instead of a relative path

c:\whatever\data.mdb
instead of
..\data.mdb

That helped, the ADO connection is now working. Now I'm trying to implement some ADO methods. I get errors whenever I run any of them. For example, here's a line of code and the error it produces:

WriteOutput(MyFieldList.RecordCount);

"Error Occurred While Processing Request
Error Diagnostic Information
unknown exception condition
unknown error while executing a tag."

Now what?

depending on the type of cursor, recordcount may or may not be available to you.

try using a client cursor

ConnectionObject.CursorLocation = adUseClient;
ConnectionObject.Open("#Source#",
                     adModeUnknown,
                     adFailIfNotExists,
                     adOpenRecordUnspecified);

Now the whole thing bonks. Here's what I did: Commented out original code and replaced it with your code.

    //Open a connection to the database
//  MyObject.Open("#Source#",
//                 adModeUnknown,
//                 adFailIfNotExists,
//                 adOpenRecordUnspecified);
                         
ConnectionObject.CursorLocation = adUseClient;
ConnectionObject.Open("#Source#",
                    adModeUnknown,
                    adFailIfNotExists,
                    adOpenRecordUnspecified);                   

Here's the error: "Error resolving parameter CONNECTIONOBJECT.OPEN"

replace ConnectionObject with MyObject

MyObject.CursorLocation = adUseClient;
MyObject.Open("#Source#",
                   adModeUnknown,
                   adFailIfNotExists,
                   adOpenRecordUnspecified);

Okay, that's fixed. Unfortunately it bonked again on the same line:

WriteOutput(MyFieldList.RecordCount);

"Error Diagnostic Information
unknown exception condition
unknown error while executing a tag."

I'm outta here for the weekend. Will reply to any additional comments on Monday. Cheers.

Search for CF_DBINFO(*NEW*) on Macromedia Exchange
Thats my tag which does the same thing as you want.

jimmy282,

this doesn't return column names/data types for MS Access

jyokum

jyokum,
Would you continue walking me through the solution you proposed? I followed your suggestions so far, but got stuck at

WriteOutput(MyFieldList.RecordCount);

"Error Diagnostic Information
unknown exception condition
unknown error while executing a tag."

could you post all the code? I was able to output the recordcount without any problem.

I got this code to work:

<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm">

<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
          name="MyObject"
          class="ADODB.Connection"
          action="CREATE">        

<cfscript>
    //Use these variables to build a valid connection string
  Driver="Microsoft Access Driver (*.mdb)";
  DataBasePath="e:\web\myisp\data\mydb.mdb";
 
  Source="Driver=#Driver#;DBQ=#DataBasePath#";

    //Open a connection to the database
  MyObject.Open("#Source#",
                 adModeUnknown,
                 adFailIfNotExists,
                 adOpenRecordUnspecified);
                 
  WriteOutput("<h3>Success!!</h3>");
</cfscript>          

<!--- Close --->    
<cfset MyObject.Close()>  

----------

Then I tried outputting some information about the database:

----------

<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm">

<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
          name="MyObject"
          class="ADODB.Connection"
          action="CREATE">        

<cfscript>
    //Use these variables to build a valid connection string
  Driver="Microsoft Access Driver (*.mdb)";
  DataBasePath="e:\web\mnoffice.media3\data\NextStep.mdb";
 
  Source="Driver=#Driver#;DBQ=#DataBasePath#";

    //Open a connection to the database
//  MyObject.Open("#Source#",
//                 adModeUnknown,
//                 adFailIfNotExists,
//                 adOpenRecordUnspecified);
                         
MyObject.CursorLocation = adUseClient;
MyObject.Open("#Source#",
                    adModeUnknown,
                    adFailIfNotExists,
                    adOpenRecordUnspecified);                          

 MyFieldList=MyObject.OpenSchema(adSchemaColumns);

  WriteOutput(MyFieldList.RecordCount);
  WriteOutput("<br>");
                 
  WriteOutput("<h3>Success!!</h3>");
</cfscript>          


<!--- Close --->    
<cfset MyObject.Close()>


------------

It works until it hits the line   "WriteOutput(MyFieldList.RecordCount);"
This is all foreign syntax to me so I'm not much good troubleshooting it (obviously).

I got this code to work:

<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm">

<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
          name="MyObject"
          class="ADODB.Connection"
          action="CREATE">        

<cfscript>
    //Use these variables to build a valid connection string
  Driver="Microsoft Access Driver (*.mdb)";
  DataBasePath="e:\web\myisp\data\mydb.mdb";
 
  Source="Driver=#Driver#;DBQ=#DataBasePath#";

    //Open a connection to the database
  MyObject.Open("#Source#",
                 adModeUnknown,
                 adFailIfNotExists,
                 adOpenRecordUnspecified);
                 
  WriteOutput("<h3>Success!!</h3>");
</cfscript>          

<!--- Close --->    
<cfset MyObject.Close()>  

----------

Then I tried outputting some information about the database:

----------

<!---Include the ADO Defines file--->
<cfinclude template="adocfscript.cfm">

<!---Create the new "Conection Object" call it "MyObject"--->
<cfobject type="COM"
          name="MyObject"
          class="ADODB.Connection"
          action="CREATE">        

<cfscript>
    //Use these variables to build a valid connection string
  Driver="Microsoft Access Driver (*.mdb)";
  DataBasePath="e:\web\mnoffice.media3\data\NextStep.mdb";
 
  Source="Driver=#Driver#;DBQ=#DataBasePath#";

    //Open a connection to the database
//  MyObject.Open("#Source#",
//                 adModeUnknown,
//                 adFailIfNotExists,
//                 adOpenRecordUnspecified);
                         
MyObject.CursorLocation = adUseClient;
MyObject.Open("#Source#",
                    adModeUnknown,
                    adFailIfNotExists,
                    adOpenRecordUnspecified);                          

 MyFieldList=MyObject.OpenSchema(adSchemaColumns);

  WriteOutput(MyFieldList.RecordCount);
  WriteOutput("<br>");
                 
  WriteOutput("<h3>Success!!</h3>");
</cfscript>          


<!--- Close --->    
<cfset MyObject.Close()>


------------

It works until it hits the line   "WriteOutput(MyFieldList.RecordCount);"
This is all foreign syntax to me so I'm not much good troubleshooting it (obviously).

I tried it again, no luck. The error message is "Server busy or unable to fulfill request. The server is unable to fulfill your request due to extremely high traffic or an unexpected internal error. Please attempt your request again (if you are repeatedly unsuccessful you should notify the site administrator). (Location Code: 26)"

you may want to check your server to make sure you don't have a ton of MSACCESS.exe processes sitting out there.

I've got a request in to my hosting service. Will keep you posted.

A quick update: Today my hosting service told me that the Access locking file (.ldb) is getting in the way of the ADO connection. That makes no sense to me; if that were true, it would never be possible to connect to an Access database via ADO, since the .ldb file is always created when the database is opened. I'm going to keep pushing and see if they can come up with a more convincing reason.