Here is the syntax:

Alter TABLE XYZ
Add column myID  COUNTER CONSTRAINT PrimaryKey PRIMARY KEY 

If we want to set the seed for the AutoNumber field, we can run this query:
alter table XYZ
alter column myID Counter(1000,1);

 

There is a documnet from Microsoft to demostrate:

How to Create and Drop Tables and Relationships Using SQL DDL

refer to this link: http://support.microsoft.com/?id=116145

You can also find more information on this topic from this document:

(How to use common Data Definition Language (DDL) SQL statements for the Jet database engine)

http://support.microsoft.com/default.aspx?scid=kb;en-us;180841

 

 

Here is a sample gridview and detailsview with editing capability with Access 2007 data. The key is to connect to the Access 2007 database table.

Connection string in your webconfig:

<connectionStrings>

<

add name="Access2007ConnectionString" connectionString="Dsn=MS Access Database;dbq=|DataDirectory|\Database1.accdb;" providerName="System.Data.Odbc" />

</

connectionStrings>

FYI: You can try another way to connect to Access 2007 in your code if you choose to:

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(".") + "\\App_Data\\Database1.accdb;" ;

Here is the sample code:

   <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="CartridgeName">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="CartridgeName" HeaderText="CartridgeName" ReadOnly="True"
                    SortExpression="CartridgeName" />
                <asp:BoundField DataField="UnitsInstock" HeaderText="UnitsInstock" SortExpression="UnitsInstock" />
                <asp:BoundField DataField="UnitsNeeded" HeaderText="UnitsNeeded" SortExpression="UnitsNeeded" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Access2007ConnectionString %>"
            DeleteCommand="DELETE FROM [Cartridge] WHERE ([CartridgeName] = ?)"
            InsertCommand="INSERT INTO [Cartridge] ([CartridgeName], [UnitsInstock], [UnitsNeeded]) VALUES (?, ?, ?)"
            ProviderName="<%$ ConnectionStrings:Access2007ConnectionString.ProviderName %>"
            SelectCommand="SELECT [CartridgeName], [UnitsInstock], [UnitsNeeded] FROM [Cartridge]"
            UpdateCommand="UPDATE [Cartridge] SET [UnitsInstock] = ?, [UnitsNeeded] = ? WHERE ([CartridgeName] = ?)">
            <DeleteParameters>
                <asp:Parameter Name="CartridgeName" Type="String" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="UnitsInstock" Type="String" />
                <asp:Parameter Name="UnitsNeeded" Type="String" />
                <asp:Parameter Name="CartridgeName" Type="String" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="CartridgeName" Type="String" />
                <asp:Parameter Name="UnitsInstock" Type="String" />
                <asp:Parameter Name="UnitsNeeded" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
        <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AllowPaging="True" DataSourceID="SqlDataSource1">
            <Fields>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>

This is to answer a question here:

http://forums.asp.net/t/1347791.aspx

Today i came across Switch function for a forum question. The user wants to sort his Month column in right order instead of alphabetically. It would be easy to do this in SQL Server with CASE statement. Thought IIF in Access should work, but it stops at nested level 10. Here comes the Switch function, the syntax of Switch is:

Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True) As SomeValue

Unlike IIf(), the Switch() function does not take a fixed number of arguments. It takes as many combinations of <Expression -> Statement>s as you need. Each expression is evaluated. If the expression evaluates to true, the statement that follows it executes.

http://www.yevol.com/en/access2007/Lesson13.htm

The solution for the user is:

Query:

SELECT * FROM (SELECT id, TalkMonth, CINT(Switch([TalkMonth]='January',1,
[TalkMonth]='February',2,
[TalkMonth]='March',3,
[TalkMonth]='April',4,
[TalkMonth]='May',5,
[TalkMonth]='June',6,
[TalkMonth]='July',7,
[TalkMonth]='August',8,
[TalkMonth]='September',9,
[TalkMonth]='Octeber',10,
[TalkMonth]='November',11,
[TalkMonth]='December',12,
[TalkMonth] is NULL,999)) As TalkMonthNumber
FROM 12Months) 
ORDER BY TalkMonthNumber

 And use the new numeric column in SortExpression for the string column.

<asp:BoundField DataField="TalkMONTH" HeaderText="TalkMONTH" SortExpression="TalkMonthNumber" />

However,it seems that Switch can only go upto level 14 for nesting.