[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2003-11-13
11
Medium Priority
?
831 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 1400 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
Suggested Courses

649 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