What you're going to gain is *not* going to be speed but increased stability *under load*. There's an Allaire KB in there somewhere about using Access in a production environment, where the only real point discussed, if I recall correctly, was that your server is a lot less likely to bust a gut if you're using OLEDB drivers.
Once I got rid of most of the Access db's on my server my memory requirements went way down (leaky odbc drivers) and the thing suddenly became solid as a rock. If you're stuck with Access then try the OLEDB as it will work better, if it works at all. If you have control of your own server mySQL for Windows is probably your next best bet, unless you have an extra $5,000 you need to spend, or a dire need for stored procs or triggers. ----------------------------------------- Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc. http://mysecretbase.com ----------------------------------------- ----- Original Message ----- From: "Andrew Tyrone" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, November 02, 2001 8:46 AM Subject: RE: Access OLE DB vs. ODBC I've screamed about this OLE DB vs. ODBC for almost a year, and no one ever gives me any concrete answers or benchmarks! There was an article in CF Developer's Journal a year or so back about how to set up OLE DB... The author "couldn't disclose the speed differences" because they were "done without adhering to any strict standards", whatever that means. It was just a bunch of bull, because the article focused more on getting OLE DB to work (what a chore it was on CF 4.x -- don't know about CF 5 server). After the author jumped through numerous hoops, I bet he found no speed gain, probably a speed deficiency, so he downplayed benchmarking. Wonderful! Now I have an OLE DB connection which took a half-hour to set up, and it's slow as molasses anyway. All I know is, OLE DB is SLOWER than ODBC when I ran a bunch of tests, with Access. I have seen no speed gain with OLE DB. Access using ODBC is a lot faster for small to medium-sized queries; when you hit queries that return super-large result sets, the two get closer together in speed. But let's face it, if you need an Enterprise database solution, you won't be using Access, because it WILL crap out sooner or later under load. Andy > -----Original Message----- > From: Matt Robertson [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 31, 2001 7:58 PM > To: CF-Talk > Subject: Re: Access OLE DB vs. ODBC > > > My experience was it either worked very well or not at all. I > have one big site that uses still Access 2k and OLEDB and gives > me absolutely no trouble. Works so well I've been lazy and left > it alone for over a year like that. > > I wasn't always so lucky. There's one particular weird error > that crops up for no apparent reason on some files. If you have > the problem with your file spec you'll know immediately. Was > never able to track down exactly what it was that caused the > trouble. From what I saw in the CF forums at the time I wasn't > the only one so afflicted, and no one else had any luck tracking > down the root cause, either. > > Give it a shot. You can always switch back. Heck, you have > nowhere to go but up ;D > > --------------------------------------- > Matt Robertson [EMAIL PROTECTED] > MSB Designs, Inc., www.mysecretbase.com > --------------------------------------- > > > ---------- Original Message ---------------------------------- > from: "Jim McAtee" <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > date: Wed, 31 Oct 2001 13:13:25 -0700 > > We've got a couple of customers that we host who insist on using Access 97 > as their database. One of them has a fairly busy site and a single large > Access database of about 1/2 GB. The CF server that hosts the > site becomes > occasionally unresponsive, no doubt due to the use of Access. > Migrating the > site to MS SQL is in the works, but we're not in the loop on > that, so we can > only wait for the customer to complete the job. In the meantime, > we need to > get the system as stable as we can. > > Does anyone have much experience using OLE DB instead of ODBC to > connect to > Access databases that might be able to say whether it's any more > stable? I > know that articles in the Allaire knowledgebase recommend OLE DB > over ODBC. > Are there any differences in SQL statements when using OLE DB - > that is, any > code changes required when changing from Access ODBC to OLE DB? > > Thanks, > Jim > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
