To all: This is in response to a request for more information. I thought others may find it useful.
> Thanks so much for your reply. It's good news to hear that > someone is relying on MyODBC for an important purpose, much of the > usage I've seen seemed rather small. Like I said, we have had not problems with it, other than the problems that Access is causing because of its non-standard implementation of ODBC. I will make note of the fact, however, that we *did* have some issues with the driver, but those were fixed, and those bugs are now no longer a problem. Again, I think when it came right down to it, it was Access issues, and not really MyODBC issues. > If you don't mind, I'd like to ask just a few more questions... Not at all. I'll do my best. > - Can you describe what your application does a bit? I'm > also interested in why you chose the Access/MyODBC route. My > reasoning is a desire to PHP on the Web side of things, and certainly > the easse and cost of running a MySQL server... The application primarily provides for entry, editing, and procession (through various states) of student government legislation. It also keeps track of who is in what postion, when they were placed and removed, and why. We also plan to implement a module that prints agendas and minutes. We chose Access/ODBC for a few reasons. 1) We were rewriting an existing application, and while it *was* a total rewrite, and went from using a local Access database to using a MySQL server, we wanted to keep the interface and behavior as similar as possible. 2) We wanted more control over format of reports. As much as I hate to admit it, Access does have strong (and pretty easy) report design and generation. This especially comes in handy when printing out official documents. Also, I didn't want to design reports by hand, as I would have had to do in Perl (formating issues, etc). 3) When wanting to update fields on the fly, e.g. change the values available in another field based on a drop down box, it can be very cumbersome to convert that to the web. Either you have to use Javascript and pull a new page every time, or make it a multistep process. It is much easier, from a design viewpoint, to use the "instant" response of a local GUI. We will, however, have an online module pretty soon here to search and view existing legislation, which was why we moved it to a MySQL back-end in the first place. > - Any details on the 'gotcha' issues with Access you > mentioned you've been through is certainly appreciated. Some you may have heard, others you may not. 1) Make sure every table has a unique ID field (auto increment works just fine) and a TIMESTAMP field. Access will use those on updates to make sure nothing has changed since it pulled in the record for edit, and to make sure it's only affecting one record. 2) On your connect options (either the FLAGS in connect parameters, or in ODBC setup) make sure you select "Don't optimize column width" and "Return matching rows." Using both of these would make for FLAGS=3 in your connect statement 3) On direct connections, for some reason, using rst.addnew, adding values, and rst.update, then repeating that will repeat the value of the first rst.addnew. I'm quite sure it's an access bug. I have an MDB file with a test case if you are interested. 4) If you don't use the "Don't optimize" option above, updates using recordssets may end up with truncated data if the new data is longer than the old data. 5) Inserts on direct connections (and an empty table) using recordsets (not a direct INSERT query) fail, and insert blank records. Again, I have a test case for that. > - I'm assuming your Access application is all with linked > ODBC tables... what type of load does it receive and how is > performance (vs. a more typical SQL Server setup if you happen to > know)? We use a hybrid linked/direct connection set up. Partially for "Gotchas" 3, 4 and 5 above. But also for speed. We use some pretty hairy joins in our program. Some as large as 4 or 5 tables. When Access processes that, it breaks it down into 3 or 4 queries (for computability, I'm sure) and sends those. This makes it very slow. In one case, when I pull in a history, it could take 2 or 3 seconds (literally). When I optimized the query, and sent it over the direct connection, it could pull in histories as fast as I could click the mouse. We also use the hybrid setup because reports can't use direct connections. This does, of course, make for a rather dicey situation, since we need to do inserts and updates (sometimes with recordsets) on attached tables. Which means those tables need to be connected with the proper username/permissions. Which means we have the slightly ugly hack of attaching/unattaching the tables everytime someone logs in or out. It's not pretty, but it works. Our next time around for this application is going to be in VB (using the MyVBQL interface) or, more likely, in Delphi/Kylix so we can have clients for Windows or Linux. And then we won't have these problems anymore. :) If you have any more questions, feel free to ask. j----- k----- -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
