Microsoft KB Archive/125898

= ODBC Examples from NT Help File Using NWind Data Source =

Article ID: 125898

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q125898



SUMMARY
The following examples show how to use each of the SQL Functions in a Visual Basic, Applications Edition, procedure.

NOTE: This information is from the Microsoft Excel for Windows NT version 5.0 VBA_XL.HLP help file.



MORE INFORMATION
Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

SQLBind Function Example
This example runs a query on the NWind sample database, and then uses the SQLBind function to display only the fourth and ninth columns of the query result set (the product name and quantity on order) on the Resultset worksheet. If Application.OperatingSystem Like "*Win*" Then databaseName = "NWind" Else     'Macintosh databaseName = "NorthWind" End If queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output1 = Worksheets("Resultset").Cells(1, 1) Set output2 = Worksheets("Resultset").Cells(1, 2) SQLBind chan, 4, output1 SQLBind chan, 9, output2 SQLRetrieve chan SQLClose chan

SQLClose Function Example
This example runs a query on the NWind sample database. The result of the query, displayed on the worksheet Resultset, is a list of all products that are currently on order. If Application.OperatingSystem Like "*Win*" Then databaseName = "NWind" Else     'Macintosh databaseName = "NorthWind" End If queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Resultset").Cells(1, 1) SQLRetrieve chan, output,, , True SQLClose chan

SQLError Function Example
This example generates an intentional error by attempting to open a connection to the NWind sample database using an incorrect connection string (NWind is misspelled). The error information is displayed on the worksheet named Resultset. chan = SQLOpen("DSN=NWin") returnArray = SQLError For i = LBound(returnArray, 1) To UBound(returnArray, 1) Worksheets("Resultset").Cells(1, i).Formula = returnArray(i) Next i SQLClose c

SQLExecQuery Function Example
This example runs a query on the NWind sample database. The result of the query, displayed on the worksheet Resultset, is a list of all products that are currently on order. If Application.OperatingSystem Like "*Win*" Then databaseName = "NWind" Else     'Macintosh databaseName = "NorthWind" End If queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Resultset").Cells(1, 1) SQLRetrieve chan, output,, , True SQLClose chan

SQLGetSchema Function Example
This example retrieves the database name and DBMS name for the NWind sample database, and then displays them in a message box. If Application.OperatingSystem Like "*Win*" Then databaseName = "NWind" Else     'Macintosh databaseName = "NorthWind" End If chan = SQLOpen("DSN=" & databaseName) dsName = SQLGetSchema(chan, 8) dsDBMS = SQLGetSchema(chan, 9) MsgBox "Database name is " & dsName & ", and its DBMS is " & dsDBMS SQLClose chan

SQLOpen Function Example
This example runs a query on the NWind sample database. The result of the query, displayed on the worksheet Resultset, is a list of all products that are currently on order. If Application.OperatingSystem Like "*Win*" Then databaseName = "NWind" Else     'Macintosh databaseName = "NorthWind" End If queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Resultset").Cells(1, 1) SQLRetrieve chan, output,, , True SQLClose chan

SQLRequest Function Example
This example runs a query on the NWind sample database. The result of the query, displayed on the worksheet Resultset, is a list of all products that are currently on order. The SQLRequest function also writes the full connection string to the worksheet named Connectstring. If Application.OperatingSystem Like "*Win*" Then databaseName = "NWind" Else     'Macintosh databaseName = "NorthWind" End If queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" returnArray = SQLRequest("DSN=" & databaseName, _        queryString, _         Worksheets("Connectstring").Cells(1, 1), _         2, True) For i = LBound(returnArray, 1) To UBound(returnArray, 1) For j = LBound(returnArray, 2) To UBound(returnArray, 2) Worksheets("Resultset").Cells(i, j).Formula = returnArray(i, j)         Next j     Next i

SQLRetrieve Function Example
This example runs a query on the NWind sample database. The result of the query, displayed on the worksheet Resultset, is a list of all products that are currently on order. If Application.OperatingSystem Like "*Win*" Then databaseName = "NWind" Else     'Macintosh databaseName = "NorthWind" End If queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Resultset").Cells(1, 1) SQLRetrieve chan, output,, , True SQLClose chan

SQLRetrieveToFile Function Example
This example runs a query on the NWind sample database. The result of the query, which is a list of all products that are currently on order, is written as a delimited text file OUTPUT.TXT, in the current directory or folder. If Application.OperatingSystem Like "*Win*" Then databaseName = "NWind" Else     'Macintosh databaseName = "NorthWind" End If queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString SQLRetrieveToFile chan, "OUTPUT.TXT", True SQLClose chan

Additional query words: XL5

Keywords: kbprogramming kbother KB125898

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.