Have you looked at the new Administrator API? It should do everything you need regarding adding, deleting, and modifying datasources. Specifically, look at the datasource.cfc component. I dont know what version of CF your running, but it is only available in CF 7. The components are located in the /cfide/adminapi directory.
HTH Steve not me wrote: > Hi all! > > I've got a basic DB with a Samples table in it to > track a bunch of... Samples :)... for each of our > Clients. Setup looks something like this: > > Samples > sampleID [PK] > clientID [FK] > .... > otherFields > .... > > "The boss" has requested that each Client be able to > download their Samples in an Access database. I > figured "hey, no problem -- I'll just have CF create a > new Access file, run a few CREATE and INSERT queries > and I'll send them off to the result." > > And it all went well until I realized that to run > those CREATE and INSERT queries, I need to have a > Datasource for each Access file I set up. Now I > thought ColdFusion had the ability to programatically > add a datasource. But my google searches, and > houseoffusion searches have led me to believe that > that isn't possible. The only thing I could find that > remotely dealt with my issue is: > > http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_18656 > > The workaround Adobe provided there isn't going to > work either. That IN clause they talk about will not > work with an INSERT statement. I can't get it to work > anyway... here's some code if you'd like to try it. I > manually set up a pass-through Datasource to an empty > ..mdb file through my CFadministrator as Adobe > suggested. That Datasource name is saved in > Request.DSName_passthrough: > > First I create the new table. > <cfquery name="create" > datasource="#Request.DSName_passthrough#"> > CREATE TABLE Samples ( > sampleID INTEGER, > ..., > otherFields, > ... > ) > IN "C:\myNewAccessFile.mdb" > </cfquery> > > Boom -- I get a "friendly" error message saying: > > [Macromedia][SequeLink JDBC Driver][ODBC > Socket][Microsoft][ODBC Microsoft Access Driver] > Syntax error in CREATE TABLE statement. > > I take out the IN statement, and it runs fine. Well > crap. Apparently that IN statement doesn't work in > conjunction with CREATEs. Darn. > > I had the idea that I could set up a pre-built Access > database with an empty Samples table already in it and > copy that rather than trying to create my own table > schema everytime. So I did that -- Created an empty > table named Samples in a new Access file. Everytime I > run the script, it copies that Access file to a new > destination: clientAccessFile[clientID].mdb and tries > to run through some INSERTS: > > <cfloop query="clientSamples"> > <cfquery name="insertTest" > datasource="#Request.DSName_passthrough#"> > INSERT INTO Samples (sampleID, ..., otherFields, > ....) > VALUES ( > <cfqueryparam value="#clientSamples.sampleID#" > cfsqltype="cf_sql_integer" />, > ..., > otherFields, > ... > ) > IN > "C:\clientAccessFile#clientSampls.clientID#.mdb"; > </cfquery> > </cfloop> > > Bam... new error: > > [Macromedia][SequeLink JDBC Driver][ODBC > Socket][Microsoft][ODBC Microsoft Access Driver] > Missing semicolon (;) at end of SQL statement. > > I take out the IN statement, and it runs without > errors -- but I can't be storing each client's Samples > in the same database. They each need their own Access > file -- the client's cannot be able to see eachother's > records as a business rule. > > I'm at a loss as to what I can do here -- any > suggestions? > > Thanks! > > Jonathon > > > > ____________________________________________________________________________________ > Want to start your own business? > Learn how on Yahoo! Small Business. > http://smallbusiness.yahoo.com/r-index > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268585 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
