Create Access table with autonumber field?

[VMI]

How can I create an Access table that has an autonumber field?

I usually create the sql string and then run it with ExecuteNonQuery(). But
I added the AUTONUMBER keyword and it's giving me a syntax error.

This is the sql script I'm using: create table myTable (line_id AUTONUMBER,
field1 TEXT(50), field2 TEXT(50))

Any help is appreciated. Thanks.

 

[Jeff Johnson]

How can I create an Access table that has an autonumber field?

I usually create the sql string and then run it with ExecuteNonQuery().
But
I added the AUTONUMBER keyword and it's giving me a syntax error.

This is the sql script I'm using: create table myTable (line_id
AUTONUMBER,
field1 TEXT(50), field2 TEXT(50))

Man, I had to dig to find this since it wasn't spelled out where it should
have been, but it looks like you can use the IDENTITY keyword just like in
SQL Server. In other words, you can just use it bare of you can use
IDENTITY(<start value>, <increment>). Using it bare is equivalent to
IDENTITY (1, 1). And it looks like you use it AS the datatype, not in
addition to.

 

[Jeff Johnson]

Man, I had to dig to find this since it wasn't spelled out where it should
have been, but it looks like you can use the IDENTITY keyword just like in
SQL Server. In other words, you can just use it bare
-OR-

you can use IDENTITY(<start value>, <increment>). Using it bare is
equivalent to IDENTITY (1, 1). And it looks like you use it AS the
datatype, not in addition to.

Stupid typos.

 

[Rich]

here is a sample for creating a table in an Access mdb from C# using an oleDB
dataAdapter.

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

OleDbDataAdapter da; OleDbConnection conn1; DataSet ds1;

string s1 = @"C:\db2test.mdb";
conn1 = new OleDbConnection();
conn1.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data Source = "
+ s1 + ";Jet OLEDB:database Locking Mode=0;Mode=Share Deny None";

da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand();
da.SelectCommand.Connection = conn1;

da.SelectCommand.CommandText = "CREATE TABLE Code3 (ID Counter,
Account TEXT(50),Code MEMO,Active Bit,Hits int,Rotation int)";

if (conn1.State == ConnectionState.Closed)
conn1.Open();

da.SelectCommand.ExecuteNonQuery();

conn1.Close();