-1

I'm struggling to find a valid answer to what I'm trying to find. Basically, I want to check if for example:

"tableNO1" exists or if every other table exists...

By the way, I'm using Access 2002-2003 if that somehow helps :) Do you think I should upgrade to the latest version?

Background: I'm trying to create run-time buttons that each one of them has a DB table, and when I close my program the tables that I created for each run-time created button will be saved. After I launch the program again I should click a button that will add these buttons that have DB tables (Each one of them has a dedicated table). for example, if 9 run-time buttons were created in the program before - each of them will have a DB table. I will have a max 9 button and each of them will be named tableNO(n) n=number of table when I click the button that creates run-time buttons for the first time, it will create a button called "tableNO1", the second time "tableNO2" will be created, and so on...

Thanks in advance.

SPekkOPs
  • 13
  • 2
  • `SELECT * FROM MSysObjects WHERE [Name] = 'tableNO1' AND Type = 1` (Type 1 means "Table") – Dai Mar 13 '22 at 13:48
  • Don't know why this was closed - the link provided is not c#, and the link provided is not tagged as .net - (so that link is a MAJOR FAIL on two counts. And that link has a un-managed code solution - not appropriate at all. As for the above suggested SELECT * from MySysOjbects, the problem is by default you need to add permissions to the MySysObjects for that query to work - and good luck setting permissions on that table from c#. The correct approach is to use the .net OleDB connection "GetSchema" to determine if a schema (table define) exists for given table, and elevated rights not require – Albert D. Kallal Mar 13 '22 at 17:41
  • @AlbertD.Kallal Reopened. You can now post that as an answer. – Dai Mar 13 '22 at 18:25
  • Not a huge deal - if that link had c#, and .net tags - I would 100% agree, support, and promote closing - and providing a link. My "gripe" was the provided link had neither - again, thanks - and not a huge deal. – Albert D. Kallal Mar 13 '22 at 18:27

1 Answers1

1

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.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51