Check If Access Table Exists

How to check if a table exists in MS Access for vb macros

Setting a reference to the Microsoft Access 12.0 Object Library allows us to test if a table exists using DCount.

Public Function ifTableExists(tblName As String) As Boolean

    If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then

        ifTableExists = True

    End If

End Function

Check if access table exists

You can use the hidden system table MSysObjects to check if a table exists:

If Not IsNull(DlookUp("Name","MSysObjects","Name='TableName'")) Then
    'Table Exists

However, I agree that it is a very bad idea to create a new table every day.

EDIT: I should add that tables have a type 1, 4 or 6 and it is possible for other objects of a different type to have the same name as a table, so it would be better to say:

If Not IsNull(DlookUp("Name","MSysObjects","Name='TableName' And Type In (1,4,6)")) Then
    'Table Exists

However, it is not possible to create a table with the same name as a query, so if you need a look up to test for a name, it may be best to add 5, that is query, to the Type list.

VBA Script to check if table exist on MS ACCESS, Delete if it does

Consider using the TableDefs collection where you iterate through items and conditionally drop or create using same DDL SQL statements passed in code.

Dim db As Database 
Dim tbldef As TableDef

Set db = CurrentDb

For each tbldef in db.TableDefs 
   If tbldef.Name = "TableName" Then 
      db.Execute "DROP TABLE " & tbldef.Name, dbFailOnError
   End if 
Next tbldef

db.Execute "CREATE TABLE TableName (...rest of SQL...);", dbFailOnError    

' UNINITIALIZE OBJECTS 
Set tbldef = Nothing 
Set db = Nothing

How to check Table exist in access database or not?

Put the function below this one into a public module.

Example code to call the function:

Dim result as boolean
result = IsExistingTable("c:\myFolder\myDatabase.mdb","myTableName")
If result Then
   'Do something
Else
   'Do something else.
Endif

Function

Public Function IsExistingTable( _
  ByVal Database As String, _
  ByVal TableName As String _
) As Boolean

Dim ConnectString As String
Dim ADOXConnection As Object
Dim ADODBConnection As Object
Dim Table As Variant

ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Database
Set ADOXConnection = CreateObject("ADOX.Catalog")
Set ADODBConnection = CreateObject("ADODB.Connection")
ADODBConnection.Open ConnectString
ADOXConnection.ActiveConnection = ADODBConnection
For Each Table In ADOXConnection.Tables
  If LCase(Table.Name) = LCase(TableName) Then
     IsExistingTable = True
     Exit For
  End If
Next
ADODBConnection.Close

End Function

How to know if a table exists in an Access Database in an OleDb connection

You can get the list of tables with

var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

and go through them:

foreach (var row in schema.Rows.OfType<DataRow>())
{
    string tableName = row.ItemArray[2].ToString();
}

or check for existence:

if (schema.Rows
          .OfType<DataRow>()
          .Any(r => r.ItemArray[2].ToString().ToLower() == tablename.ToLower()))
{
    // table exists
}

Ugly, I know. :(

Check for existence of a certain table in access DB using C# OleDB connection

Ok, there are several ways to do this, but I suggest this code:

   public Boolean TableExist(string sTable)
    {
        using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB2))
        {
            conn.Open();
            string[] sRestrict = new string[] {null,null,null,null};
            sRestrict[2] = sTable;
            DataTable MySchema = new DataTable();
            MySchema = conn.GetSchema("Columns",sRestrict);

            return (MySchema.Rows.Count > 0);
        }
    }

The above is also how you can get the schema (table def) as a table.

Thus, say

if (TableExist("tblHotels")
{
     // your code here
}

Now, because you are possible (likely) using a loop, then you might consider for reasons of performance to pass a valid connection to the TableExist function, and thus for your loop not re-create a connection each time - as that will slow things down quite a bit.

Note that "many" often suggest doing this:

SELECT * FROM MSysObjects WHERE [Name] = 'tableNO1' AND Type = 1

The problem with above is by default, the MySysObjects requires elevated rights. The database can be opened, and then using security settings in access the rights to MySysOjbects can be changed - but it more work then the above code/function.

---

Related Topics