XL5: ODBC Examples from NT Help File Using NWind Data Source

ID: Q125898

5.00 5.00c WINDOWS kbprg kbcode

 The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c

Microsoft Excel for Windows NT, version 5.0

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 chan

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"

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"

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"

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 reference words: 5.00

Keywords          : kbother kbprg 
Version           : 5.00 5.00c
Platform          : WINDOWS

Last Reviewed: May 17, 1999