[GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
Bruce Tong wrote: Another window appeared and said "Error while executing the query. ERROR: The field being ordered by must appear in the target list (#1)." Since I hadn't specified any sorting, the "order by" part of the message had me confused. I decided to have MS Access show me the SQL it had generated: I finally figured out what the heck is going on with this order by problem in MS Access. I tested your query on a computer that has older versions of the Microsoft Jet db engine on it and guess what? It does the same thing you described! The reason that I used to have the problem and it miraculously went away was because I installed Visual Basic 5.0 and it upgraded the MS Jet dll's. Your right about the snapshot. It still tries to throw the order by clause in there. But, here's a simple workaround that you can easily try. When looking at your query in Access, right click on the fields area and select "Totals". This will add a group by clause but it won't have any real effect, other than it will let your query run! So, there are two things you must do to fully fix the order by problem: 1. Upgrade the MS Jet DLL (this is the real problem here) 2. Get the patch for the order by/group by from our web site (it will be in postgres 6.4 also) Interestingly, even if you get the postgres patch like we have here, the older MS Jet engine will still not let you sort the columns the way you want, because it always throws that same old order by in there. The working version of \windows\system\MSJET35.DLL is 3.51.0623.4 The broken version I tested was 3.50.3602.4 I'm not sure at what release they fixed this problem. I will dig around on MS web site. Does anyone know if its OK to redistribute the MS JET DB Engine DLL, like with our ODBC driver for example??? (That thing is big too, 1MB!!!) For that matter, is there a simple way to get it from Microsoft? Byron
[GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
Bruce Tong wrote: The working version of \windows\system\MSJET35.DLL is 3.51.0623.4 The broken version I tested was 3.50.3602.4 I'm not sure at what release they fixed this problem. I will dig around on MS web site. Does anyone know if its OK to redistribute the MS JET DB Engine DLL, like with our ODBC driver for example??? (That thing is big too, 1MB!!!) For that matter, is there a simple way to get it from Microsoft? Yes, I found the updated jet database engine on Microsoft site. http://support.microsoft.com/download/support/mslfiles/Jet35upd.exe I don't know the answers to these questions, but if you're just doing this to save me time there's no reason to put yourself out as that's something I should do for myself. If you're planning on distributing the DLLs along with the driver, that sounds like a great idea, assuming they'll let you. Well, this Access order by problem has bothered us and many others that use the odbc front end with postgres for quite some time. Its a great relief to finally have a clear understanding of the problem! I think we will probably just include a link to the above site off of our site and mention this problem. I take it I don't have to gather the log information you requested? No, thanks anyway. Byron
Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
At 10:36 AM 7/24/98, Byron Nikolaidis wrote: Bruce Tong wrote: Another window appeared and said "Error while executing the query. ERROR: The field being ordered by must appear in the target list (#1)." Since I hadn't specified any sorting, the "order by" part of the message had me confused. I decided to have MS Access show me the SQL it had generated: I finally figured out what the heck is going on with this order by problem in MS Access. I tested your query on a computer that has older versions of the Microsoft Jet db engine on it and guess what? It does the same thing you described! The reason that I used to have the problem and it miraculously went away was because I installed Visual Basic 5.0 and it upgraded the MS Jet dll's. FWIW: I seldom do much more than add the tables in MS Access's OPINION stupid /OPINION query design thingy. I immediately use the menu to view SQL and just type the SQL I want. Obviously, different folks think different ways, and maybe you actually understand and like that MS Access point and click interface that I hate, but I want to be sure you're at least aware that if it sticks an ORDER BY in there that you never asked for, you can just rip the damn thing out in the SQL view and then run the query... At least, that's how I make it give me the SQL I really want, instead of the SQL it thinks I want, which it never gets right anyway. Hope that helps. -- -- -- "TANSTAAFL" Rich [EMAIL PROTECTED]
Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
Richard Lynch wrote: FWIW: I seldom do much more than add the tables in MS Access's OPINION stupid /OPINION query design thingy. I immediately use the menu to view SQL and just type the SQL I want. Obviously, different folks think different ways, and maybe you actually understand and like that MS Access point and click interface that I hate, but I want to be sure you're at least aware that if it sticks an ORDER BY in there that you never asked for, you can just rip the damn thing out in the SQL view and then run the query... At least, that's how I make it give me the SQL I really want, instead of the SQL it thinks I want, which it never gets right anyway. Hope that helps. Thanks, I would probably agree with you, but it makes no difference if I like it or not, its our customers who want the graphical interface and FINALLY, we have a fix for it! They are most likely not the types who are gonna drop down to the pound sign and type some SQL into the monitor! And BTW, if you look at the SQL, you wouldn't even see the ORDER BY thing because this clause is placed on the "keyset" query, which is the collection of keys it uses to dynamically access rows in the query result (i.e. the Dynaset). Actually, I understand your hate of the Microsoft thing, but I only recently realized the benefit and power of this Jet database engine thing that Access uses. For example, it can update the results of a query that has several tables joined, right while your looking at the row on the screen, automatically translating the SQL2 syntax (i.e., INNER JOIN ... ON ...) into standard SQL that Postgres can use. Byron
Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
FWIW: I seldom do much more than add the tables in MS Access's OPINION stupid /OPINION query design thingy. I immediately use the menu to view SQL and just type the SQL I want. Obviously, different folks think different ways, and maybe you actually understand and like that MS Access point and click interface that I hate, but I want to be sure you're at least aware that if it sticks an ORDER BY in there that you never asked for, you can just rip the damn thing out in the SQL view and then run the query... At least, that's how I make it give me the SQL I really want, instead of the SQL it thinks I want, which it never gets right anyway. Since I'm learning SQL in my spare time, I tend to use these feature in MS Access and PgAccess to point me in the right direction or sometimes confirm, or deny my assertions. I like psql, but its not the kind of tool which suggests other alternatives. It just says "this part is bogus." That's fine, but when I fail to get it right after a dozen attempts, its nice to let something else take a stab at it. Bruce Tong | Got me an office; I'm there late at night. Systems Programmer | Just send me e-mail, maybe I'll write. Electronic Vision / FITNE | [EMAIL PROTECTED]| -- Joe Walsh for the 21st Century
Re: [GENERAL] Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!
Since I'm learning SQL in my spare time, I tend to use these feature in MS Access and PgAccess to point me in the right direction or sometimes confirm, or deny my assertions. I like psql, but its not the kind of tool which suggests other alternatives. It just says "this part is bogus." That's fine, but when I fail to get it right after a dozen attempts, its nice to let something else take a stab at it. Please tell use what else psql can tell the user. We have \h and \d commands. -- Bruce Momjian | 830 Blythe Avenue [EMAIL PROTECTED] | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup.| (610) 853-3000(h)
