Microsoft Knowledge Base

• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

When you use either the SQLGetSchema function in a Visual Basic procedure or the SQL.GET.SCHEMA function in a version 4.0 macro, and you use the TypeNum (type_num for version 4.0 macro) value 4, the function may fail to return an array of table names.

CAUSE

This behavior occurs if you do not use the correct syntax for the QualifierText (qualifier_text for version 4.0 macro) argument, the database name and owner name for the database that contains the tables that you want to return. For example, the following statement returns an array that contains the #N/A! error value instead of an array that contains the table names in the DB1.MDB database:

   result = SQLGetSchema(conn, 4, "db1.mdb")

When you use the database name and the owner name for a given database in the SQLGETSCHEMA function, the database name and owner name must be enclosed in single quotation marks. Additionally, in the case of an Access database, the full path to the database file must be included.

WORKAROUNDS

To avoid receiving an error value or an error message when you return an array of table names located in a given database, use any of the following methods.

NOTE: In the following examples, result is defined as Variant data type (value returned should be an array) and conn is a variable representing the unique connection ID of the data source.

Method 1: To return a list of tables located in the DB1.MDB database use

          either of the following:

             result = SQLGetSchema(conn, 4, "'c:\access\db1.mdb'")

             -or-

             result = SQLGetSchema(conn, 4, "'c:\access\db1.mdb'.")

             result = SQLGetSchema(conn, 4, "")

             -or-

             result = SQLGetSchema(conn, 4, ".")

             -or-

             ' If necessary to store current database name for future use:
             ' Return name of current database enclosed in brackets
             dbrackets = SQLGetSchema(conn, 7)

             ' Define variable database as name of current database
             ' without the brackets
             database = Mid(dbrackets, 2, Len(dbrackets) - 2)

             ' Use database name without brackets in SQLGetSchema function,
             ' enclosed in single quotation marks
             result = SQLGetSchema(conn, 4, "'" & database & "'")

MORE INFORMATION

You can use the SQLGetSchema Function in a Visual Basic procedure to return information about the structure of the data source on a particular connection. This function is available in the XLODBC.XLA add-in. Before you use the function, you must reference the add-in file in your Visual Basic Module using the References command from the Tools menu.

You can use the SQL.GET.SCHEMA function in a version 4.0 macro to return this same information. Before you use the function, you must load the ODBC add-in, XLODBC.XLA, using the Add-ins dialog box.

Visual Basic Code Examples

The following Visual Basic procedure returns the name of the first table in the DB1.MDB database:

   Sub ReturnTables()

      Dim conn As Integer
      Dim result As Variant

      conn = sqlopen("DSN=access 2.0")

      result = SQLGetSchema(conn, 4, "'c:\access\db1.mdb'.")

      MsgBox result(1, 1)

      ' Close the connection to the database
      sqlclose (conn)

   End Sub

   Sub ReturnTables()

      Dim conn As Integer
      Dim result As Variant
      Dim x As Integer

      conn = sqlopen("DSN=access 2.0;DBQ=c:\access\db1.mdb")
      result = SQLGetSchema(conn, 4, "")

      For x = 1 To UBound(result, 1)
         Worksheets("sheet1").Range("a1").Offset(x - 1, 0).Value = _
            result(x, 1)
      Next x

      'Close the connection to the database
      sqlclose (conn)

   End Sub

Microsoft Excel Version 4.0 Macro Code Example

The following Microsoft Excel version 4.0 macro returns an array of the names of all of the tables in the DB1.MDB database as the defined name "tables."

   A1: conn=SQL.OPEN("dsn=Access 2.0")
   A2: =SET.NAME("tables",SQL.GET.SCHEMA(conn,4,"'c:\access\db1.mdb'"))
   A3: =SQL.CLOSE(conn)
   A4: =RETURN()

REFERENCES

For more information about SQLGetSchema Function, choose the Search button in the Visual Basic Reference and type:

    SQLGetSchema Function

    SQL.GET.SCHEMA function