I think they're all encrypted... -----Original Message----- From: Jonathon S [mailto:[EMAIL PROTECTED] Sent: Monday, February 05, 2007 2:17 PM To: CF-Talk Subject: Re: programmatically create an Access Datasource
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:268709 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
