Microsoft KB Archive/166284

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

Article ID: 166284

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q166284



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.

 Start a new project in Visual Basic and choose "Standard EXE." Form1 is created by default. Add one CommandButton, Command1, and two list boxes, List1 and List2, to Form1.  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   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:

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

 Change the DSN, UID, and PWD in Form1 to match the ODBC database you are using. Start the program or press the F5 key. Click the Command1 button to execute the code, fill each list box, and display the table count and performance numbers in the debug window.</li></ol>

<div class="references_section">