What is MS Access ODBC SQL syntax for a table with AutoIncrement and Memo columns?

Question

I am migrating a full framework .NET app to core. It used ADOX to create an MS Access database and tables. It used OLE DB to populate data. In ADOX, the data type was ADOX.DataTypeEnum.adInteger and the syntax from c# was:

fld.Properties["Autoincrement"].Value = true;
fld.Properties["Seed"].Value = 1;
fld.Properties["Increment"].Value = 1;

For memo, ADOX data type was ADOX.DataTypeEnum.adLongVarWChar.

Since we can no longer use ADOX under .NET Core, I have to use SQL DML through ODBC. When I attempt to create a table using syntax similar to

create table t (
  col1 int not null identity(1,1),
  col2 varchar(512)
)

I get a syntax error. When I try to adjust this for Access according to this and other related articles in the same documentation tree, I also get syntax error.

create table t (
  col1 counter,
  col2 text
)

What is the correct syntax for these columns that is compatible with .NET Core and MS Access ODBC driver?

Answer 1

I'm not sure adLongVarWChar is a valid type in Access but you can use MEMO like this:

CREATE TABLE [T](
    [col1] COUNTER,
    [col2] MEMO,
    CONSTRAINT [PrimaryKey] PRIMARY KEY ([col1])
)

That final CONSTRAINT is critical to go from just an AutoIncrement to your primary key, which I assume is what you are trying to do.

The other format that Access likes looks like this:

CREATE TABLE [T] (
  [col1] AUTOINCREMENT CONSTRAINT [PrimaryKey] PRIMARY KEY UNIQUE NOT NULL,
  [col2] LONGTEXT
)

Let me know which one works for you.