Create Table with Incremental Primary Key with Access

Create Table [Courses] (
    [course_id] Counter NOT NULL,
    [course_title] text(100) NOT NULL,
    [course_date] datetime NOT NULL,
    [course_description] memo NOT NULL,
    [course_places] int NOT NULL,
    [course_active] yesno not null,
    [course_created] timestamp NOT NULL,
    [course_location_id] int NOT NULL,
    foreign key (course_location_id) references locations(location_id),
    Primary Key (course_id)
)

• Posted in:
• SQL

6 comments

1. Nice, just what I did need, thanks :)
   
   Comment by Henrik – June 16, 2008
2. @Henrik, You're welcome :)
   It's one of those things that Access does differently to every other database!
   
   Comment by John Whish – June 16, 2008
3. Hi Whish,
   This article gives exact solution for my issue.
   Thankz a lot dude,
   FYI>>
   
   MyIssue : Creating dynamic Table through ASP in Access DB
   
   Solution
   <% @Language=VBScript%>
   <% Option Explicit %>
   
   <%
   
   Dim connection
   Dim SQL, sConnString
   sConnString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("Db/Sample.mdb")
   SQL = "CREATE TABLE Sample(SampleID Counter, CompanyName Text(100), ContactPerson Text(100), Phone Text(50), Email Text(100), WebsiteURL Text(100), Comments Text(250), QueryString Text(250), UniqueID Text(50), SignupDate Text(50), Referrer Text(50), SearchEngine Text(100), Primary Key (SampleID))"
   Set connection = Server.CreateObject("ADODB.Connection")
   connection.Open(sConnString)
   connection.execute(SQL)
   Response.write("ConLog table created")
   Connection.Close
   Set Connection = Nothing
   %>
   
   Comment by Bala – December 10, 2008
4. John,
   This was very helpful.
   Do you know of a list of syntax for all field types for Access?
   For example double on number field.
   
   Any way to find out how this differs from My SQL?
   
   Thanks for the post.
   John
   
   Comment by John Wells – March 03, 2009
5. Hi John, it's a long time since I've worked with an Access database so I can't remember off the top of my head. This might help:
   msdn.microsoft.com/en-us/library/ms714540(VS.85).aspx
   
   Comment by John Whish – March 05, 2009
6. That was it.
   Thanks!
   
   Comment by John Wells – March 05, 2009