How can one return the primary key value during an Insert Into command using MS Access with an OleDb connection?

Question

I am able to insert a new row of data into my MS Access table ("Table1") from a desktop application (C#, WinForms), but I cannot figure out how to return the value of the primary key ("UniqueID") for the newly added row. I am using an OleDbConnection to perform all of my queries. I have tried using an OUTPUT clause, shown below, and also SCOPE_IDENTITY() to return the value, but I only get the exception: "Syntax error in INSERT INTO statement."

I have seen many example of how to do this using SQL Server, but is it possible to return the primary key value using Access and an OleDbConneciton?

int PrimaryKey;
using (OleDbConnection Connection = new OleDbConnection(CONNECTION_STRING))
{
  Connection.Open();

  OleDbCommand Command = new OleDbCommand();
  Command.Connection = Connection;
  Command.CommandText = String.Format(
    "INSERT INTO Table1 (JobNumber, ItemNumber) " +
    "OUTPUT INSERTED.UniqueID " +
    "VALUES (@jobnumber, @itemnumber)");

  Command.Parameters.AddRange(new OleDbParameter[]
  {
    new OleDbParameter("@jobnumber", JobNumber),
    new OleDbParameter("@itemnumber", ItemNumber)
  });

  PrimaryKey = Convert.ToInt32(Command.ExecuteScalar());
}

Answer 1

Both OUTPUT INSERTED and SCOPE_IDENTITY() are not valid statements for Microsoft Access SQL (A.K.A. JetSql the sql engine for Microsoft Access). Also Jet doesn't support batch statements so no multiple commands separated by semicolons are accepted.
You need to send two different commands to the engine.

int PrimaryKey;
using (OleDbConnection Connection = new OleDbConnection(CONNECTION_STRING))
{
  Connection.Open();

  OleDbCommand Command = new OleDbCommand();
  Command.Connection = Connection;
  Command.CommandText = @"INSERT INTO Table1 
                          (JobNumber, ItemNumber) 
                          VALUES (@jobnumber, @itemnumber)";

  Command.Parameters.AddRange(new OleDbParameter[]
  {
    new OleDbParameter("@jobnumber", JobNumber),
    new OleDbParameter("@itemnumber", ItemNumber)
  });
  Command.ExecuteNonQuery();

  // Send the next command
  Command.Parameters.Clear();
  Command.CommandText = "SELECT @@IDENTITY";
  PrimaryKey = Convert.ToInt32(Command.ExecuteScalar());
}