HOWTO: Use ODBC API SQLTables Function with Visual Basic and RDO

• Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0

SUMMARY

Using the rdoTables collection is generally the best way to retrieve the tables from an rdoConnection because the collection is built-in and requires very little code.

If you want to take advantage of anything that the rdoTables collection does not provide, you can use the ODBC API SQLTables function. For example, SQLTables is a little faster at returning the table names; this can be especially apparent with an Oracle database. Also, the SQLTables function can return the table owner with each table. This article demonstrates each method in code so you can chose which is best for your situation.

MORE INFORMATION

The following example uses the rdoTables collection and the SQLTables ODBC API function to fill two list boxes with the table names and print the performance numbers.

For this example, SQL Server and the Pubs database are used, but you can change the DSN, UID, and PWD to match any ODBC database.

1. Start a new project in Visual Basic and choose "Standard EXE." Form1 is created by default.



2. Add one CommandButton, Command1, and two list boxes, List1 and List2, to Form1.



3. Paste the following code into the General Declarations section of Form1:

   
         Dim cn As New rdoConnection
   
            Private Sub Form_Load()
              'establish rdoConnection to use hDbc for SQLTables
   
              'SQL Server example
              'cn.Connect = _
              '  "Driver={SQL Server}; Server=MyServer; " & _
              '  "Database=Pubs; " & _
              '  "Uid=sa; Pwd="
   
              'Oracle example
              cn.Connect = _
                "Driver={Microsoft ODBC Driver for Oracle}; " & _
                "connectstring=dsenterprise.world; " & _
                "Uid=demo; Pwd=demo"
              cn.EstablishConnection
            End Sub
   
            Private Sub Command1_Click()
              Dim sngT As Single, intN As Integer
   
              'Get tables using ODBC API
              List1.Clear
              intN = 0
              sngT = Timer
              aTables = GetODBCTables(hDbc:=cn.hDbc, Owner:=True, _
                TableType:="'TABLE', 'VIEW'")
              Dim i As Integer
              For i = 0 To UBound(aTables) - 1
                List1.AddItem aTables(i)
                intN = intN + 1
              Next i
              Debug.Print intN, "odbc tables: ", Timer - sngT
   
              DoEvents
   
              'Get tables using rdoTables collection
              List2.Clear
              intN = 0
              sngT = Timer
              intN = cn.rdoTables.Count
              For i = 0 To intN - 1
                List2.AddItem cn.rdoTables(i).Name
              Next i
              Debug.Print intN, "rdo tables: ", Timer - sngT
            End Sub
   
            Public Function GetODBCTables(ByVal hDbc As Long, _
              Optional Owner = True, _
              Optional TableType = "'TABLE', 'VIEW', 'SYSTEM TABLE'") _
              As Variant
   
              On Error GoTo ehGetDSNTables
              Dim intHstmt As Long, intRc As Integer
              Dim strErrMsg As String * SQL_MAX_MESSAGE_LENGTH
              Dim strSqlState As String * 5
              Dim arrTables() As Variant, intCounter As Integer
              Dim strTable As String
              Dim strName As String * 255, strOwner As String * 255
              Dim lngLenRead As Long
   
   
              intRc = SQLAllocStmt(ByVal hDbc, intHstmt)
              intRc = SQLTables(ByVal intHstmt, ByVal 0&, SQL_NTS, _
                ByVal 0&, SQL_NTS, ByVal 0&, SQL_NTS, _
                ByVal CStr(TableType), Len(TableType))
              If intRc <> SQL_SUCCESS Then
                GoTo ehGetDSNTables
              End If
   
              strOwner = String(255, 0)       'Init string to hold data.
              strName = String(255, 0)        'Init string to hold data.
   
              intRc = SQLFetch(intHstmt)
              If intRc <> SQL_SUCCESS Then
                GoTo ehGetDSNTables
              End If
   
              intCounter = 0
              ReDim Preserve arrTables(intCounter)
              While intRc <> SQL_NO_DATA_FOUND
                intRc = SQLGetData(ByVal intHstmt, 2, SQL_C_CHAR, _
                  ByVal strOwner, 254, lngLenRead)
                intRc = SQLGetData(ByVal intHstmt, 3, SQL_C_CHAR, _
                  ByVal strName, 254, lngLenRead)
                strTable = IIf(Owner, Mid(strOwner, 1, _
                  InStr(strOwner, Chr(0)) - 1) & ".", "")
                strTable = strTable & Mid(strName, 1, _
                  InStr(strName, Chr(0)) - 1)
                ReDim Preserve arrTables(intCounter)
                arrTables(intCounter) = strTable
                intCounter = intCounter + 1
   
                strOwner = String(255, 0)    'Init string to hold data.
                strName = String(255, 0)     'Init string to hold data.
                intRc = SQLFetch(intHstmt)
              Wend
              intRc = SQLFreeStmt(ByVal intHstmt, SQL_CLOSE)
   
              GetODBCTables = arrTables
              Exit Function
            ehGetDSNTables:
              intRc = SQLError(ByVal henv, ByVal hDbc, _
                ByVal intHstmt, strSqlState, 0, strErrMsg, _
                SQL_MAX_MESSAGE_LENGTH, 0)
              Debug.Print "(" & strSqlState & ")" & strErrMsg
            End Function 




4. From the Project menu, select Add Module, and then click on Module1. Then paste the following code into the General Declarations section of Module1:

   
         'As an alternative you could include the entire ODBC32.TXT* file
         Global Const SQL_NTS As Long = -3
         Global Const SQL_MAX_MESSAGE_LENGTH As Long = 512
   
         Global Const SQL_NO_DATA_FOUND As Long = 100
         Global Const SQL_SUCCESS As Long = 0
   
         Global Const SQL_CHAR As Long = 1
         Global Const SQL_C_CHAR As Long = SQL_CHAR
         Global Const SQL_CLOSE As Long = 0
   
         Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hDbc&, _
           phstmt&) As Integer
         Declare Function SQLTables Lib "odbc32.dll" (ByVal hstmt&, _
           szTblQualifier As Any, ByVal cbTblQualifier%, szTblOwner As Any, _
           ByVal cbTblOwner%, szTblName As Any, ByVal cbTblName%, _
           szTblType As Any, ByVal cbTblType%) As Integer
         Declare Function SQLFetch Lib "odbc32.dll" (ByVal hstmt&) As Integer
         Declare Function SQLGetData Lib "odbc32.dll" (ByVal hstmt&, _
           ByVal icol%, ByVal fCType%, ByVal rgbValue As String, _
           ByVal cbValueMax&, pcbValue&) As Integer
         Declare Function SQLFreeStmt Lib "odbc32.dll" _
           (ByVal hstmt&, ByVal fOption%) As Integer
         Declare Function SQLError Lib "odbc32.dll" _
           (ByVal henv&, ByVal hDbc&,_
         ByVal hstmt&, ByVal szSqlState$, pfNativeError&, _
           ByVal szErrorMsg$,_
           ByVal cbErrorMsgMax%, pcbErrorMsg%) As Integer 

   *For more information about the ODBC32.TXT file, see the following article in the Microsoft Knowledge Base:

   Q173050 FILE: ODBCTXT.EXE: ODBC API Declare Statements Text File




5. Change the DSN, UID, and PWD in Form1 to match the ODBC database you are using.



6. Start the program or press the F5 key.



7. Click the Command1 button to execute the code, fill each list box, and display the table count and performance numbers in the debug window.

REFERENCES

In Visual Basic Books Online see:

  Guide to Building Client Server Applications in Visual Basic (Enterprise)
    Part 3: Data Access Options
      Using the ODBC API
        ODBC API Overview
          Retrieving Information about ODBC Data Sources 

Additional query words: kbRDO kbODBC kbAPI kbVBp500 kbVBp600 kbdse kbDSupport kbVBp

Keywords : kbGrpVBDB
Version : WINDOWS:5.0,6.0
Platform : WINDOWS
Issue type : kbhowto