Wrox Programmer Forums
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 14th, 2005, 06:26 AM
Authorized User
Join Date: Jan 2005
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default Microsoft ODBC for Oracle

Dim conn As New ADODB.Connection
conn.Open "driver={Microsoft ODBC for Oracle};" & _
    "server=js;" & _
Set Cat = New ADOX.Catalog
Cat.ActiveConnection = conn
TotTab = Cat.Tables.Count

The issue here is
Surprisingly TotTab got a value much larger than what I was expecting.
The total number of user-defined tables & views are expected to be not more than 1500. But it shows some 8500 tables.

Is there any way to :
1) count the tables created by the users (all other system tables etc excluded)
2) when was a table last modified and by which user

Old August 22nd, 2005, 10:33 AM
Authorized User
Join Date: May 2005
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts

1.) You can query the table USER_TABLES and get a count of distinct tables that are not system tables as follows:

select Count(Table_name) from user_tables;

2.) This is a function as to what is logged on your database or whether your tables have audit trail fields on them. This type of logging that you wish to implement is rather resource intensive. Typically, a database design that needed to keep track of such info might add fields to each record on the table that contained a transaction date/time, and userid. Then all you would have to do is query the table for the MAX value of date/time and you would have the most rcent update time and the user id who did it. Oracle does not automatically log such information.

Similar Threads
Thread Thread Starter Forum Replies Last Post
ERROR [42000] [Microsoft][ODBC driver for Oracle][ sanjeev jha General .NET 0 June 28th, 2008 12:50 AM
ODBC Microsoft Access wehave74 Java Databases 1 January 29th, 2007 01:25 PM
ODBC - insert on a linked field - [microsoft][ODBC chimp MySQL 0 April 7th, 2005 04:44 AM
[Microsoft][ODBC Microsoft Access Driver] Syntax e chinedu Classic ASP Databases 3 November 18th, 2004 03:48 PM
[ODBC Microsoft Access Driver] !!!Error!!! Varg_88 Classic ASP Databases 3 August 30th, 2004 06:19 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.