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

ID: Q125898

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 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:

Keywords : kbother kbprg
Version : WINDOWS:5.0,5.0c; winnt:5.0
Platform : WINDOWS winnt
Issue type :