ODBC DSN 'on-the-fly'

[randall2nd]

I am trying to connect to an excel file as a table. does anyone have a sample of creating the ODBC DSN 'on-the-fly'. If it is not for an excel file that is fien just need to know how to do this.

 

[tlhawkins]

Hi Randall2nd.

I think this might do it for you, this is a way to do a DSN-less connection.

<cfobject type=&quot;COM&quot;
          name=&quot;MyConn&quot;
          class=&quot;ADODB.Connection&quot;
          action=&quot;CREATE&quot;>

<CFSCRIPT>
DSNtemp = &quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot;;
DSNtemp = DSNtemp & &quot;DBQ=d:\data\mydb.mdb&quot;; 
MyConn.Open(&quot;#DSNtemp#&quot;, &quot;&quot;, &quot;&quot;, -1);

SQL = &quot;SELECT * FROM MyTable&quot;;

MyRecordset = MyConn.Execute(SQL, 0, 8);

MyFields = MyRecordset.Fields;

/* Make a variable for recordcount */

RecordCount = 0;
    while(NOT MyRecordset.EOF){
        RecordCount = RecordCount + 1;   
        MyRecordset.MoveNext();          
    }        

Columns = &quot;&quot;;    
</CFSCRIPT>

<!--- Get the Column Name from the MyFields collections --->
<CFLOOP COLLECTION=&quot;#MyFields#&quot; ITEM=&quot;this&quot;>
    <CFSET Columns = ListAppend(Columns, this.Name)>
</CFLOOP>

<!--- Populate a newly made query with columns --->
<CFSET MyRecords = QueryNew(Columns)>

<!--- Add 'RecordCount' rows to hold the data --->
<CFSET QueryAddRow(MyRecords, RecordCount)>

<!--- Go to the first row of the recordset --->
<CFSET MyRecordset.MoveFirst()>

<!--- And populate the recordset... --->
<CFLOOP FROM=&quot;1&quot; TO=&quot;#RecordCount#&quot; INDEX=&quot;i&quot;>
    <CFLOOP COLLECTION=&quot;#MyFields#&quot; ITEM=&quot;this&quot;>
        <CFSET QuerySetCell(MyRecords, Trim(this.name), this.value, i)>
    </CFLOOP>
    <CFSET MyRecordset.MoveNext()>
</CFLOOP>

<!--- Ouput the data like this: --->
<CFOUTPUT QUERY=&quot;MyRecords&quot;>
    <TR>
    <CFLOOP LIST=&quot;#MyRecords.ColumnList#&quot; INDEX=&quot;this&quot;>
        <TD>#Evaluate(this)#</TD>
    </CFLOOP>        
    </TR>
</CFOUTPUT>
</TABLE>

<!--- Close the ODBC connection (like you do in ASP)--->
<CFSET MyConn.Close()>

I believe IQOF188 is the author of this code.

This is coded to work for an Access database but I believe it will work for excell as well. Just change the database driver.

Hope it works for you.

 

[randall2nd]

Thanks, that should work great!