Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 January 31st, 2005, 06:15 PM
Authorized User
 
Join Date: Jan 2005
Location: Pinckneyville, IL, USA.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to mnoon Send a message via MSN to mnoon Send a message via Yahoo to mnoon
Default How to detect if a table exists?

How do you detect if a table exists. I have a table tblAvailability and I need to delete it and create it, and I am trying to avoid errors by knowing in advance if the table exists or not.

I have seen the following used...

Function fTableExists3(strTableName As String) As Boolean
    On Error Resume Next
    fTableExists3 = IsObject(CurrentDb.TableDefs(strTableName))
End Function

But not sure if this is the ticket.

Regards,


Marc Noon

Coding is a game. Live it, love it, break it, run it.
__________________
Marc Noon

Coding is a game. Live it, love it, break it, run it.
 
Old January 31st, 2005, 06:19 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

I have always used this one to detect if a table is present and then Drop it to create a new one. rs is an ADODB recordset and cn is an ADODB connection. RBRVS is that name of the table in question.

Hope that helps

Mike

strSQL = "SELECT Count(*) AS Present FROM MSysObjects WHERE Name = 'RBRVS' AND Type = 1"

    rs.Open strSQL, cn

    If rs.Fields("Present") = 1 Then
        strSQL = "DROP TABLE RBRVS"
        cn.Execute strSQL
    End If

Mike
EchoVue.com
 
Old January 31st, 2005, 06:20 PM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

If you are using SQL server, then u can check in sysobjects
for that object_id (table).

Om Prakash
 
Old February 8th, 2005, 02:14 PM
Authorized User
 
Join Date: Jan 2005
Location: Pinckneyville, IL, USA.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to mnoon Send a message via MSN to mnoon Send a message via Yahoo to mnoon
Default

Another question... how would one go about dropping a table... one that I create and drop often, if two or more people use it to view data through a query?

Thanks,

Marc Noon

Marc Noon

Coding is a game. Live it, love it, break it, run it.
 
Old February 11th, 2005, 09:03 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

For your case I'd recommend using Front end and back end databases.

Keep the shared tables (tables that you don't drop (delete) and create) in the back end. Link to those tables from the front end.

Give everyone their own copy of the front end. Put the table(s) you do drop (delete) in the front end. No worries about two people trying to view that table since each have their own copy.


Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old February 11th, 2005, 09:43 AM
Authorized User
 
Join Date: Jan 2005
Location: Pinckneyville, IL, USA.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to mnoon Send a message via MSN to mnoon Send a message via Yahoo to mnoon
Default

How do you go about separating the database in access. Do create a separate work-flow process for each session, and just poll often from the main database. Just interested in the model and demarcation lines more than anything.

Marc Noon

Coding is a game. Live it, love it, break it, run it.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Ch3 Moviesite table already exists? aiche_ag BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 0 August 7th, 2007 01:29 PM
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
If table exists in database meghana VB Databases Basics 1 September 29th, 2004 10:39 AM





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