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?

1 Answer 1


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

    [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:

  [col2] LONGTEXT

Let me know which one works for you.

  • Isn't UNIQUE redundant in the presence of PRIMARY KEY?
    – mustaccio
    Aug 17, 2021 at 22:42
  • It absolutely should be. That is what came out when I exported the SQL for the table created by the first example. So it's what access does internally. They are identical.
    – HackSlash
    Aug 17, 2021 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.