//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
access.databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Access DB & ADO :: How do I determine if a table exists in an Access database?


How do I determine if a table exists in an Access database?

We can take one of our existing schema extractors from other articles, and modify it slightly: 
 
<% 
    tableToFind = "foo" 
    dbname = "/file.mdb"  
 
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source="  
        ConnStr = ConnStr & server.mappath(dbname) 
 
    set adoxConn = CreateObject("ADOX.Catalog")  
    set adodbConn = CreateObject("ADODB.Connection")  
    adodbConn.open ConnStr  
    adoxConn.activeConnection = adodbConn  
    found = false 
    for each table in adoxConn.tables 
        if lcase(table.name) = lcase(tableToFind) then 
            found = true 
            exit for 
        end if 
    next 
    adodbConn.close: set adodbConn = nothing  
    set adoxConn = nothing  
 
 
    if found then 
        response.write("Table exists.") 
    else 
        response.write("Table does not exist.") 
    end if 
%>
 
You could also query the MSysObjects table: 
 
<% 
    tableToFind = "foo" 
    dbname = "/file.mdb"  
 
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source="  
        ConnStr = ConnStr & server.mappath(dbname) 
 
    set adodbConn = CreateObject("ADODB.Connection")  
    adodbConn.open ConnStr  
 
    sql = "SELECT COUNT(*) FROM MSYSObjects WHERE Type = 1" & _ 
        " AND [Name]='" &     tableToFind & "'" 
 
    set rs = adodbConn.Execute(sql) 
    found = rs(0) 
 
 
    if found then 
        response.write("Table exists.") 
    else 
        response.write("Table does not exist.") 
    end if 
%>
 
For information on determining existence of tables in SQL Server, see Article #2458.

Related Articles

Why do I get General error Unable to open registry key 'DriverId'?
Why does Access give me 'unspecified error' messages?

 

 


Created: 8/10/2002 | Last Updated: 10/2/2003 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (144)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...