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

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 engineers 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"

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"

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

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"

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"

         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 =

          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"