Microsoft Computing: Microsoft Query

[MCWebsite.Staff]

** This thread discusses the article: Microsoft Computing: Microsoft Query **
** This thread discusses the Content article: Microsoft Computing: Microsoft Query **
0

[Guest.Visitor]

Microsoft Computing: Microsoft Query

** This thread discusses the article: Microsoft Computing: Microsoft Query **
I'm running Office XP, and I've used MS Query in the past and it's been marginally useful, but I find that it's syntax checker is far too restrictive. There's a lot of syntax that it doesn't allow for, such as the renaming of coulmns and calls to database specific functions. I get the impression that it only checks for basic SQL sytax and automatically fails if it finds a deviation rather than checking it against the database. In order to circumvent this problem, I create a dummy query in MS Query and return it to the spreadsheet. This creates a QueryTable object associated with the spreadsheet it's returned to. I then create the more complex query in other SQL editors like Crystal SQL Designer or the SQL interface that comes with iSeries Access. Then I use a small VBA script to manually set the CommandText property of the QueryTable to the SQL that I actually want to run. It's an ugly workaround, but it gives you a table in Excel that you can simply refresh in order to get the most recent information from the DB. Perhaps they don't have this limitation in Office 2003 and MS Query checks the syntax against the database, but this should be useful for anyone running Office XP.

[Guest.Visitor]

Microsoft Computing: Microsoft Query

** This thread discusses the article: Microsoft Computing: Microsoft Query **
how do you change the ODBC data source to include other libraries?

[David Abramowitz]

Microsoft Computing: Microsoft Query

** This thread discusses the article: Microsoft Computing: Microsoft Query **
Not everyone can use VB, or Crystal. The fact is that if you want a quick way to get AS/400 data into MS Office desktop applications, MS Query is the only readily available tool for end users, and the only universal tool at the help desk. It is unfortunate that IBM stopped with the file transfer add-in. It must have been known that the rules for naming DB2 files and fields do not correspond with the allowable names in MS Query. Crystal had enough input from mainframe and AS/400 users to allow ODBC and OLE data sources to work despite characters like #, $, and "." in the file and field names. MS hasn't had the input or just doesn't care. That being said, and IMHO, IBM should step up to the plate and go beyond the ODBC driver, to a full access interface to MS Office applications. Dave

[steve@dossin.com]

Microsoft Computing: Microsoft Query

** This thread discusses the article: Microsoft Computing: Microsoft Query **
You can specify the library in the SQL. It is know as schema in the SQL world. This allows you to get to libraries not specified in the ODBC connection, but they do not come up in the wizards.

[Guest.Visitor]

Microsoft Computing: Microsoft Query

** This thread discusses the article: Microsoft Computing: Microsoft Query **
David Abramowitz wrote: > It must have been known that the rules for naming DB2 files and > fields do not correspond with the allowable names in MS Query. > Crystal had enough input from mainframe and AS/400 users to allow > ODBC and OLE data sources to work despite characters like #, $, and > "." in the file and field names. MS hasn't had the input or just > doesn't care. That being said, and IMHO, IBM should step up to the > plate and go beyond the ODBC driver, to a full access interface to MS > Office applications. I don't know if it's really necessary for IBM to do much more. If characters in the field name cause problems, use the ALIAS feature in DDS to fix it. example: the field name is SFEXT$, but the alias is SFEXT. Bill