I had not thought about that! I am running CFMX7 though, so I guess I do have that option.
Thanks! Jonathon --- Steve Milburn <[EMAIL PROTECTED]> wrote: > 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:268707 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
