Solved

SQL/Coldfusion query to MS Access for Column's Data Type

Posted on 2003-11-13
11
825 Views
Last Modified: 2013-12-24
How would a write a SQL/Coldfusion query to get the data type and size of a column in a MS Access database?

table name is #url.ViewTable#
column name is #columnName#

<CFQUERY name="q_GetColumnSpecs" datasource="evodb">
   
?????????

</CFQUERY>

Thanks for the help.
0
Comment
Question by:Max_Davis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 2

Expert Comment

by:Crazee
ID: 9743263
When you say "size of a column" do you mean the character length of data stored in each field of a given column ( metadata for the attribute ) or do you mean how many records there are?

As far as I know, you can't get metadata through the SQL query, you should know the characteristics of the tables you are operating on ( one of the reasons is the security )

On the other hand, if you want the number if records returned by the query you use it's RecordCount property...

<CFQUERY name="q_GetColumnSpecs" datasource="evodb">
SELECT #columnName# FROM #url.ViewTable#
</CFQUERY>

<cfOutput>#q_GetColumnSpecs.RecordCount#</cfOutput>

cheers
0
 

Author Comment

by:Max_Davis
ID: 9743312
No, I need the number of columns in a table.

For example if I queried this table it would return 3:

TableA [ Column1, Column2, Column3 ]

So, this isn't possible?

- Max

0
 

Author Comment

by:Max_Davis
ID: 9743324
Sorry.. I mean it would return:

Int, Varchar, Int for DataType
and 2, 225, 6 for Size

- Max
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:Max_Davis
ID: 9743331
Or just querying one column would return something like:

Type: VARCHAR
Size: 225

- Max
0
 
LVL 2

Expert Comment

by:Crazee
ID: 9743562
As far as I know, it's not possible... Theoreticaly, metadata is stored in a table as well, but I'm not sure that the user can access it. Is there any reason why you can't have a look at the metadata in Access?
0
 

Author Comment

by:Max_Davis
ID: 9743597
I am making an interface to easily create tables in a Access database without downloading the mdb file adding tables and then reuploading (using a hosting service), my application also browses current tables in the database, I want to be able to show the data for the columns.

Here is what I have so far:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<link href="/Evo57Style.css" rel="stylesheet" type="text/css">

<!--- CONFIRM WIDGET --->
<SCRIPT LANGUAGE="JavaScript"><!--
function doubleCheck() {
    var agree=confirm("Are you sure you wish to delete this table?");
    if (agree)
         return true ;
    else
         return false ;
}
//--></SCRIPT>
<!--- CONFIRM WIDGET --->

<html>
<head>
      <title>Table Creator</title>
</head>

<body>

<CFOUTPUT>

<CFIF IsDefined("Form.DeleteTable")>
 <CFQUERY name="q_DeleteTable" datasource="evodb">
  DROP Table "#Form.DeleteTable#"
 </CFQUERY>
</CFIF>

<CFIF IsDefined("Form.CreateIt")>
 
 <CFTRY>
  <CFQUERY name="q_InsertTable" datasource="evodb">
   CREATE TABLE #Form.TableName#
    (
     ID AUTOINCREMENT NOT NULL,
       <CFLOOP from="1" to="#Form.TableFields#" index="i">
      #Evaluate("form.Field#i#Name")# #Evaluate("form.Field#i#Type")#,
       </CFLOOP>
     PRIMARY KEY(ID)
    );
   </CFQUERY>
   
   <CFCATCH>
   
    <p class="warning">
       <cfswitch expression = #cfcatch.ErrorCode#>
     <cfcase value="37000">No table created: You cannot have spaces in your table or column names.</cfcase>
       <cfcase value="S0001">No table created: You already have a table named #Form.TableName#.</cfcase>
       <cfcase value="S0021">No table created: You had two columns with the same name.</cfcase>
     <cfdefaultcase>#cfcatch.message#</cfdefaultcase>
     </cfswitch>
      </p>
   
   </CFCATCH>
   
  </CFTRY>
 
</CFIF>

<CFQUERY name="q_ListTables" datasource="evodb">
 SELECT Name, DateCreate
 FROM  MSysObjects
 WHERE Type=1
 AND Flags=0
 ORDER BY Name;
</CFQUERY>

<CFIF q_ListTables.RecordCount gt 0>
 <table cellspacing="0" cellpadding="3" border="0">
  <tr><td colspan="5" class="gridtopend"><b>Current Tables</b></td></tr>
  <tr><td class="grid">Name</td><td class="grid">Date Created</td><td class="grid">Record Count</td><td class="grid">Column Count</td><td class="gridend">Del</td>
  <CFLOOP query="q_ListTables">
 
  <CFQUERY name="q_GetRecordCount" datasource="evodb">
   SELECT *
   FROM #Name#
  </CFQUERY>
 
  <CFSET ColumnCount = 0>
  <CFLOOP list="#q_GetRecordCount.columnList#" index="columnName">
   <CFSET ColumnCount = ColumnCount + 1>
  </CFLOOP>
 
   <tr>
    <td class="grid"><a href="tcreator.cfm?ViewTable=#Name#">#Name#</a></td>
    <td class="grid">#DateFormat(DateCreate, "mmmm dd, yyyy")# - #TimeFormat(DateCreate, "hh:mm tt")#</td>
      <td class="grid">#q_GetRecordCount.RecordCount#</td>
      <td class="grid">#ColumnCount#</td>
    <form action="tcreator.cfm" method="post" name="DeleteTable"><td class="gridend"><input type="hidden" name="DeleteTable" value="#Name#"><input onClick="return doubleCheck()" type="image" src="/images/close.gif" alt="Delete Table"></td></form>
   </tr>
  </CFLOOP>
 </table>
 
 <CFIF IsDefined("url.ViewTable")>
  <CFQUERY name="q_GetColumnNames" datasource="evodb">
   SELECT *
   FROM #url.ViewTable#
  </CFQUERY>
  <br>
  <table cellspacing="0" cellpadding="3" border="0">
  <tr><td colspan="4" class="gridtopend"><b>Columns in #url.ViewTable#</b></td></tr>
  <tr><td class="gridend">Name</td><!--- td class="gridtop">Data Type</td><td class="gridtopend">Size</td --->
  </tr>
  <CFLOOP list="#q_GetColumnNames.columnList#" index="columnName">
   <tr>
    <td class="gridend">#columnName#</td><!--- td>#q_GetColumnSpecs.data_type#</td><td>#q_GetColumnSpecs.length#</td --->
   </tr>
  </CFLOOP>
  </table>
 
 
 </CFIF>
 
</CFIF>

<CFFORM action="tcreator.cfm" method="post">

 <table cellspacing="0" cellpadding="3" border="0">
 <tr><td colspan="2" class="gridtopend"><b>Create Table</b></td></tr>
 <tr>
  <td class="grid">Table Name:</td><td class="gridend"><input type="text" value="<CFIF IsDefined("Form.TableName") AND (NOT IsDefined("Form.CreateIt"))>#Form.TableName#</CFIF>" name="TableName"<CFIF IsDefined("Form.TableName") AND (NOT IsDefined("Form.CreateIt"))> READONLY</CFIF> size="30"></td>
 </tr>
 <tr>
  <td class="grid">Field Size:</td><td class="gridend"><input type="text" value="<CFIF IsDefined("Form.TableFields") AND (NOT IsDefined("Form.CreateIt"))>#Form.TableFields#</CFIF>" name="TableFields"<CFIF IsDefined("Form.TableFields") AND (NOT IsDefined("Form.CreateIt"))> READONLY</CFIF> size="3"></td>
 </td>
 </table>

 <CFIF IsDefined("Form.TableFields") and IsDefined("Form.TableName") and #Form.TableFields# lt 101 and #Form.TableFields# gt 0 and #Form.TableName# neq "" and #Form.TableFields# neq ""  AND (NOT IsDefined("Form.CreateIt"))>
 
   <br>
   <table cellspacing="0" cellpadding="3" border="0">
   <tr><td colspan="3" class="gridtopend"><b>Create Columns</b></td></tr>
    <tr>
     <td class="grid">##</td>
     <td class="grid">Name</td>
     <td class="gridend">DataType</td>
    <CFLOOP from="1" to="#Form.TableFields#" index="i">
    <tr>
     <td class="grid">#i#</td><td class="grid"><input type="text" value="" name="Field#i#Name" size="30"></td>
     <td class="gridend"><select name="Field#i#Type"><option value="int">Number</option><option value="varchar">Text</option><option value="datetime">Date/Time</option></select></td>
    </tr>
    </CFLOOP>
   </table>
   <br>
   <input type="hidden" value="yes" name="CreateIt">
   <input type="submit" value="Create Table">
 
   <CFELSE>
    <br>
    <input type="submit" value="Create Columns">
    <CFIF IsDefined("Form.TableFields") and IsNumeric("#Form.TableFields#") and #Form.TableFields# gte 101><br><br><span class="warning">Woah there! You can't create more than 100 fields.</span>
      <CFELSEIF IsDefined("Form.TableFields") and (NOT IsNumeric("#Form.TableFields#"))><br><br><span class="warning">Woah there! "Field Size" has to be a number.</span>
      </CFIF>
   </CFIF>

</CFFORM>

</CFOUTPUT>

</body>
</html>
0
 
LVL 2

Expert Comment

by:Crazee
ID: 9743817
Hmm...I know you can do it using JDBC ( ResultSetMetaData object ) but I never seen it done using strictly CF - perhaps someone else will know...
0
 
LVL 12

Accepted Solution

by:
jyokum earned 350 total points
ID: 9744678
I had been working on this sometime ago and this seems to work...
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20658768.html

towards the bottom of the script, you'll have to update the DataBasePath variable to the full path to your database.

<cfscript>
//---- 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:\path\to\database.mdb";             // THIS SHOULD BE THE FULL PATH TO THE DATABASE
  Source="Driver=#Driver#;DBQ=#DataBasePath#";

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

  MyFieldList=ConnectionObject.OpenSchema(adSchemaColumns);
  WriteOutput("Recordcount: " & myfieldlist.recordcount & "<br><br>"); // here's the recordcount
    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("table name: " & TableName.value);
    WriteOutput(" <br> ");  
    WriteOutput("column name: " & ColumnName.value);
    WriteOutput(" <br> ");
    WriteOutput("database type: " & DataBaseType[DataType.value]);
    WriteOutput(" <br> ");
    WriteOutput("simple name: " & SimpleType[DataType.value]);        
    WriteOutput(" <hr> ");  
           
    MyFieldList.MoveNext();  
  }

</cfscript>          


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

0
 

Author Comment

by:Max_Davis
ID: 9749389
Beautiful jyokum! Thank you. Don't you love when you can do things that others claim can't be done?

- Max
0
 
LVL 2

Expert Comment

by:Crazee
ID: 9751326
If you don't mind jyokum, I might use it in a future - I'll promise to give you points then...?
0
 
LVL 12

Expert Comment

by:jyokum
ID: 9751484
Max_Davis.. yes, i do love it.

Crazee... go for it, i'm here to help
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question