[thelist] ColdFusion5/Dynamic Queries

[Douglas Rehg]

Hi all

I am running into a problem.  I am trying to connect to a database using
the dynamic queries ability of CF5.   From what I have read up on, you
can do something like this:

    <cfset MyConnectionString = "Driver={Microsoft Access Driver
    (*.mdb)};Dbq=MyDatabase.mdb;DefaultDir=D:\path2files\;Uid=Admin;Pwd=Password;">


    <cfquery name="GetExample" connectstring="#MyConnectionString#"
    dbtype="DYNAMIC">
    Select        *
    From          tablename
    Order by    fieldname
    </cfquery>

(of course, replacing the "D:\path2files\;" with the appropriate info),
but no luck with this.  I have tested it on my hosting company's servers
(NT based, using the drive:\path\information) and on my clients linux
based system (using the /dir/path/information).

I also heard that you can get rid of the defaultDir variable and concat
that to the db file name, i.e.

    <cfset MyConnectionString = "Driver={Microsoft Access Driver
    (*.mdb)};Dbq=D:\path2files\MyDatabase.mdb;Uid=Admin;Pwd=Password;">

but again, no glory.

Error messgaes on the windows based system say something like:

    ODBC Error Code = ()

    Unknown connection error for the data source, '__DYNAMIC__.'

    The error occurred while processing an element with a general
    identifier of (CFQUERY), occupying document position (3:1) to (3:81)

and on the linux system,

    ODBC Error Code = IM002 (Data source not found and no default driver
    specified)

    [MERANT][ODBC lib] Data source '' not found and no default driver
    specified

    Hint : In order to use ODBC data sources with Cold Fusion you must
    create the data source using the Data Sources page of the Cold
    Fusion Administrator. In addition, if you are running under Windows
    NT you must make sure that the Data Source is created as a System
    DSN in order for it to be available to ColdFusion.

    SQL = "Select * From tblUsers"

    Data Source = ""

Anyone have experience with this, and want to share their knowledge?  I
obviously would rather just use a straight DNS entry on the server, but
my client's hosting company has not been responding to requests, and I
want to make a deadline...

Thanks,
Doug Rehg