Question

display table properties via SQL

Asked by: loe

I need to modify the structure of several tables in an Access database using SQL and ColdFusion. The database is on a live server so I can't open it directly, but I want to verify the changes I made to be sure they are correct. How can I display a list of fields, data types, indexes, keys, etc., using ColdFusion and/or SQL statements?

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2003-06-24 at 13:26:24ID20658768
Tags

table

,

sql

,

properties

,

display

Topics

ColdFusion Application Server

,

ColdFusion Studio

Participating Experts
3
Points
250
Comments
30

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Update live SQL server
    I have a live SQL server for the web and developer SQL server. Sometime I change the table structure (Add/delete table or field), and modify some record in tables. Can I transfer the new data, table to the live database without shutdown the database? If the answer is yes, I ...
  2. index table
    I have a table in DB with ID as pk, ID is auto increased, but someone still ask to index this table, ID is not the index of this table, or, index table means something else? thanks!
  3. How to Create the structure and the indexes
    If I executed the command "CREATE TABLE TEMP1 AS (SELECT * FROM TEMP2)" does it create the structure and the indexes ? If that doesn't work is there another statement that can do the job?
  4. Good SQL Table Structure
    Hi experts, Please kindly give some opinions which one is the best sql table structures and why? or you have a better structure beside below structures, please advise. This table will has millions records. 3-5 millions records. For an easy examples, some of the fields are:...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: loePosted on 2003-06-24 at 13:53:45ID: 8792628

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

 

by: jyokumPosted on 2003-06-24 at 15:06:41ID: 8793094

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

 

by: jyokumPosted on 2003-06-24 at 15:07:24ID: 8793100

just change the following to the appropriate path

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

 

by: anandkpPosted on 2003-06-24 at 22:12:56ID: 8794753

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

 

by: jyokumPosted on 2003-06-24 at 22:24:01ID: 8794800

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.

 

by: loePosted on 2003-06-25 at 10:32:46ID: 8799448

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.

 

by: jyokumPosted on 2003-06-25 at 10:47:29ID: 8799528

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

 

by: loePosted on 2003-06-25 at 11:16:26ID: 8799711

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

 

by: jyokumPosted on 2003-06-25 at 11:20:01ID: 8799730

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.

 

by: loePosted on 2003-06-27 at 08:52:45ID: 8814296

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?

 

by: jyokumPosted on 2003-06-27 at 09:14:52ID: 8814454

try using a full path instead of a relative path

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

 

by: loePosted on 2003-06-27 at 09:58:22ID: 8814782

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?

 

by: jyokumPosted on 2003-06-27 at 10:53:56ID: 8815161

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

 

by: loePosted on 2003-06-27 at 11:39:18ID: 8815516

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"

 

by: jyokumPosted on 2003-06-27 at 11:51:53ID: 8815598

replace ConnectionObject with MyObject

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

 

by: loePosted on 2003-06-27 at 12:06:16ID: 8815713

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

 

by: loePosted on 2003-06-27 at 14:02:58ID: 8816482

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

 

by: jimmy282Posted on 2003-07-13 at 02:01:37ID: 8911483

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

 

by: jyokumPosted on 2003-07-13 at 07:27:36ID: 8912154

jimmy282,

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

jyokum

 

by: loePosted on 2003-07-16 at 08:33:19ID: 8935421

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

 

by: jyokumPosted on 2003-07-17 at 07:37:42ID: 8943052

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

 

by: loePosted on 2003-07-17 at 11:39:23ID: 8945306

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

 

by: loePosted on 2003-07-17 at 11:39:26ID: 8945308

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

 

by: jyokumPosted on 2003-07-17 at 12:33:16ID: 8945806

here's what i'm using. it's working for me. just change the value for DataBasePath

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

<cfscript>

  Driver="Microsoft Access Driver (*.mdb)";
  DataBasePath="D:\Inetpub\wwwroot\EE\ee.mdb"; // change this to the correct path
  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(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()>

 

by: loePosted on 2003-07-17 at 13:11:46ID: 8946106

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

 

by: jyokumPosted on 2003-07-17 at 13:45:33ID: 8946404

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

 

by: loePosted on 2003-07-17 at 14:09:14ID: 8946614

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

 

by: loePosted on 2003-07-23 at 12:25:41ID: 8989381

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.

 

by: jyokumPosted on 2003-11-16 at 18:57:55ID: 9761155

loe,
This has been open 145 days and there hasn't been a comment added in 116 days.
Please select a comment as the solution or give us an update.

jyokum

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...