Finding out through SQL or OLEDB which of an Access tables' columns is autoincremented?

Question

I need to find all the multiple or non-autoincremented primary keys, make them normal keys, and make the primary key an autoincrement column. But I need to check if there is already an autoincrement column, so I make that one a primary key, in case if it's not.

Answer 1

Based on this Microsoft article on How To Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C# .NET I have written a little code for you to pick the field with the auto increment set to True,

  OleDbConnection cn = new OleDbConnection();
  OleDbCommand cmd = new OleDbCommand();
  DataTable schemaTable;
  OleDbDataReader myReader;

  //Open a connection to the SQL Server Northwind database.
  cn.ConnectionString = "...";
  cn.Open();

  //Retrieve records from the Employees table into a DataReader.
  cmd.Connection = cn;
  cmd.CommandText = "SELECT * FROM Employees";
  myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

  //Retrieve column schema into a DataTable.
  schemaTable = myReader.GetSchemaTable();

  var myAutoIncrements = schemaTable.Rows.Cast<DataRow>().Where(
              myField => myField["IsAutoIncrement"].ToString() == "True");

  foreach (var myAutoInc in myAutoIncrements)
  {
      Console.WriteLine((myAutoInc[0]));
  }

  Console.ReadLine();

  //Always close the DataReader and connection.
  myReader.Close();
  cn.Close();

You can simply paste this on you app or even a new console app and see the results of shown Fields with the IsAutoIncrement set to true.