MS Access 2003: How do you create a make table query and assign a primary key?

Question

I'm completely new to this demon spawn of software. This is a class assignment and it is demanding I create a table using make table query and then assign a primary key, but it doesn't seem to be possible. Does anyone know how to do this?

Answer 1

Put this sub in a module, change the table and field names to fit:



Sub MakeTable()

  Dim strSQL As String



  strSQL = "CREATE TABLE tblYY ( " & _

    "CourseID COUNTER, " & _

    "CourseTitle text(100) NOT NULL, " & _

    "CourseDescription memo NOT NULL, " & _

    "CoursePlaces int NOT NULL , " & _

    "Primary Key (CourseID) );"



  DoCmd.RunSQL strSQL



End Sub



Note the assignment of the primary key in the last part of the SQL statement

Answer 2

MS Access 2003: How do you create a make table query and assign a primary key?



Make Table Query creates fields from the existing table's fields (source file) and copies the records (normally with criteria) to it.



It does not modify the field attributes and properties including the primary key.



To have the field modified with a primary key, you can use SQL but you will need to know how to write SQL first.



See article on SQL syntax http://www.about-access-databases.com/sql-query-syntax.html and locate the keywords CREATE TABLE, DROP INDEX, ALTER TABLE for more information.



Hope this helped.

Answer 3

Setting a primary key as no duplicates only stops it duplicating in the same table. I can't think of any circumstances where you would need to do what you are trying to do unless there is a flaw in the design of the database.