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

Posted on 2003-11-13
Medium Priority
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">


Thanks for the help.
Question by:Max_Davis
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

Expert Comment

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#



Author Comment

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


Author Comment

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

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

- Max
ATEN's HDBaseT Presentation at InfoComm 2017

Hear ATEN Product Manager YT Liang review HDBaseT technology, highlighting ATEN’s latest solutions as they relate to real-world applications during her presentation at the HDBaseT booth at InfoComm 2017.


Author Comment

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

Size: 225

- Max

Expert Comment

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?

Author Comment

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

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

      <title>Table Creator</title>



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

<CFIF IsDefined("Form.CreateIt")>
  <CFQUERY name="q_InsertTable" datasource="evodb">
   CREATE TABLE #Form.TableName#
       <CFLOOP from="1" to="#Form.TableFields#" index="i">
      #Evaluate("form.Field#i#Name")# #Evaluate("form.Field#i#Type")#,
    <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>

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

<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">
   FROM #Name#
  <CFSET ColumnCount = 0>
  <CFLOOP list="#q_GetRecordCount.columnList#" index="columnName">
   <CFSET ColumnCount = ColumnCount + 1>
    <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>
 <CFIF IsDefined("url.ViewTable")>
  <CFQUERY name="q_GetColumnNames" datasource="evodb">
   FROM #url.ViewTable#
  <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 --->
  <CFLOOP list="#q_GetColumnNames.columnList#" index="columnName">
    <td class="gridend">#columnName#</td><!--- td>#q_GetColumnSpecs.data_type#</td><td>#q_GetColumnSpecs.length#</td --->

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

 <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"))>
   <table cellspacing="0" cellpadding="3" border="0">
   <tr><td colspan="3" class="gridtopend"><b>Create Columns</b></td></tr>
     <td class="grid">##</td>
     <td class="grid">Name</td>
     <td class="gridend">DataType</td>
    <CFLOOP from="1" to="#Form.TableFields#" index="i">
     <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>
   <input type="hidden" value="yes" name="CreateIt">
   <input type="submit" value="Create Table">
    <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>




Expert Comment

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...
LVL 12

Accepted Solution

jyokum earned 1400 total points
ID: 9744678
I had been working on this sometime ago and this seems to work...

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

//---- 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";

adCriteriaKey ="0";
adCriteriaAllCols ="1";
adCriteriaUpdCols ="2";
adCriteriaTimeStamp ="3";

adPriorityLowest ="1";
adPriorityBelowNormal ="2";
adPriorityNormal ="3";
adPriorityAboveNormal ="4";
adPriorityHighest ="5";

adRecalcUpFront ="0";
adRecalcAlways ="1";



//---- 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";

<cfobject type="COM"


  Driver="Microsoft Access Driver (*.mdb)";
  DataBasePath="D:\path\to\database.mdb";             // THIS SHOULD BE THE FULL PATH TO THE DATABASE


  WriteOutput("Recordcount: " & myfieldlist.recordcount & "<br><br>"); // here's the recordcount

        /*These prperties are available
          through the OpenSchema method.*/
  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> ");  


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


Author Comment

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

- Max

Expert Comment

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

Expert Comment

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

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

Featured Post

WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

Question has a verified solution.

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

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
Suggested Courses

771 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