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.

"VMI" <VMI@discussions.microsoft.com> wrote in message 
news:C6502B12-BA5E-4BD2-8C7D-BDF0586F4702@microsoft.com...

> 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" <i.get@enough.spam> wrote in message 
news:ui$oH%23bpKHA.3748@TK2MSFTNGP02.phx.gbl...

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

-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. 

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();









"VMI" wrote:

> 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.