Aha! That is the kind of outside the box thinking for which I was looking. Presuming that the SQL DSN user has Create permission (50/50 chance) this could work nicely. I'll pass it on to the co-worker dealing with this issue. He is having trouble with this HOF account, see CF-OT post to that affect.
Thank You Ian Adrian Lynch wrote: > You wouldn't have to "import the Access data into the SQL database", sorry, > you started the with the quotes ;O). You can just select the data from > Access, loop over it and generate the SQL in insert into a temp table. > Something like... > > <cfquery name="theAccessQuery" datasource="..."> > QUERY THE ACCESS DATA HERE > </cfquery> > > <cfquery name="theSQLQuery" datasource="..."> > > CREATE #temp (COLUMNS GO HERE) > > <cfloop query="theAccessQuery"> > INSERT INTO #temp (COLUMNS GO HERE) VALUES (VALUES GO HERE) > </cfloop> > > SELECT FROM THE SQL TABLE HERE > > </cfquery> > > Why would you do this? Well, you now have all the Access data in SQL Server > and have available all the functions and constructs. One that might be > interesting is CHECKSUM and BINARY_CHECKSUM which I don't think is available > in Access. > > Interesting post. > > Adrian > > -----Original Message----- > From: Todd [mailto:[EMAIL PROTECTED] > Sent: 14 December 2007 17:33 > To: CF-Talk > Subject: Re: Compare two tables > > > "we can not import the Access data into the SQL database at this time at > least." > > On Dec 14, 2007 12:11 PM, Dawson, Michael <[EMAIL PROTECTED]> wrote: > > >> Since everyone else mentioned QoQ, another option would be creating a >> temp table in SQL, loading the Access records, and then doing the query. >> >> M!ke >> >> -----Original Message----- >> From: Ian Skinner [mailto:[EMAIL PROTECTED] >> Sent: Friday, December 14, 2007 9:57 AM >> To: CF-Talk >> Subject: Compare two tables >> >> Anybody have a creative way to compare data in two tables and find all >> records in table A not in table B and vice-a-versa. The data share a >> common key, 'License Number'. >> >> The trick -- Table A is in an MS Access Database and Table B is in a MS >> SQL server database. They are not aware of each other and we can not >> import the Access data into the SQL database at this time at least. >> >> TIA >> Ian >> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294829 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
