DOCUMENT:Q166284 09-JAN-2000 [vbwin] TITLE :HOWTO: Use ODBC API SQLTables Function with Visual Basic and RDO PRODUCT :Microsoft Visual Basic for Windows PROD/VER:WINDOWS:5.0,6.0 OPER/SYS: KEYWORDS:kbGrpDSVBDB ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - 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 (c) Microsoft Corporation 1997, All Rights Reserved. Contributions by Jon Fowler, Microsoft Corporation Additional query words: kbRDO kbODBC kbAPI kbVBp500 kbVBp600 kbdse kbDSupport kbVBp ====================================================================== Keywords : kbGrpDSVBDB Technology : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600 Version : WINDOWS:5.0,6.0 Issue type : kbhowto ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2000.