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 February 21st, 2004, 09:47 PM
Registered User
 
Join Date: Feb 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default If table exists in database

I have a function in VB to check if the given table exists in SQL Server 7 Database

1.Using ADOX catalog as follows:
 Dim CatlogObj New ADOX.CatalogADOX.Catalog
 For i = 0 To CatObj.Tables.Count - 1
 'check if name present in the
 If UCase(CatlogObj.Tables(i).Name) = UCase(TableName) Then
     TableExists = True
     Exit Function
 End If

I found that using this function for database of 24,000 tables,the method returns false though the physical table is present.Table is getting created and after 45-60 secs the code checks if table exists.
This happens only when running 2 jobs in parallel and both are creating different tables in DB

Is it because somehow the catalog is not getting refreshed view of database or something like that.


I have another approach to do the same as follows:
2.Querying "sysobjects" table
  SELECT COUNT (NAME) AS CNT FROM SYSOBJECTS OBJ WHERE OBJ.NAME='CUSTOMER'
 and then if recodset has >0 records return true;

But I am not sure if "sysobjects" will be refreshed with that entry when function call is made.

Any suggestions are welcome

Thanks
Meghana
 
Old September 29th, 2004, 10:39 AM
Authorized User
 
Join Date: Sep 2004
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Lalit_Pratihari
Default

Hi,

Yes you can use the second approach. Just add to the query:

SELECT COUNT (NAME) AS CNT FROM SYSOBJECTS OBJ WHERE OBJ.NAME='CUSTOMER' AND XTYPE = 'U'
Hope this helps,

Lalit
Life Means More...;)





Similar Threads
Thread Thread Starter Forum Replies Last Post
how to check the username already exists in table asad_black ASP.NET 2.0 Basics 2 September 15th, 2008 07:19 AM
table already exists mussa MySQL 1 July 13th, 2006 03:01 AM
check if a column exists in the table sands SQL Server 2000 2 April 21st, 2006 11:08 PM
How to test if a column exists in a table jtrifts SQL Server 2000 2 February 16th, 2006 02:25 PM
How to detect if a table exists? mnoon Access 5 February 11th, 2005 09:43 AM





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