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:268581 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
