Q166284: HOWTO: Use ODBC API SQLTables Function with Visual Basic and RDO
Article: Q166284
Product(s): Microsoft Visual Basic for Windows
Version(s): WINDOWS:5.0,6.0
Operating System(s):
Keyword(s): kbGrpDSVBDB
Last Modified: 09-JAN-2000
-------------------------------------------------------------------------------
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 1986-2002.