Create a primary key in a make table query

Similar Threads:

1.Create Table Primary Key after Make Table Query And Update Table

I have an application that requires data to be updated after reports are 
created.  The user is prompted from form for a report type needed via 
drop-down list (Combo13).  User selects option(s) = 10 - Hearing Letters, or 
13 Cancellation Letters, or 20 Finding Letters.  Upon click, form code 
executes following code to run Macro, pulling select records from table 
creating temporary table that will need to be updated after reports are 
created.  If user selects 10, only table records with value 10 in PrtNo_Num 
are selected, if user selects 13, only table records with value 13 in 
PrtNo_Num are selected, etc
Once the reports are ran, then field PrtNo_Num in temp file is set to null, 
and records need to be written back to originating table so an update table 
needs to be performed.
I also need to create a primary key on the temporary table for reporting 
purposes.  Primary key fields are Case_Num_Yr and Case_Num in that order.  
Ie tried the following code but get invalid syntax error, could someone 
provide corrected syntax to build primary key for temporary table?   Code 
follows:  Help is much appreciated, thanks,  Robert

Private Sub Combo13_Click()
    If Combo13 = 10 Then
        MsgBox " Now creating Hearing Letters ! "
            DoCmd.runMacro "FRM-CR10RW"
	ALTER TABLE DPS_FRQ_CR10RW ADD CONSTRAINT PK_CR10RW
		PRIMARY KEY(Case_Num_Yr,Case_Num)
	(run reports code)
	UPDATE DPS_FR_CASE_RECORDS
		SET DPS_FR_CASE_RECORDS.PrtNo_Num = €
			WHERE DPS_FR_CASE_RECORDS.Case_Num_Yr =
				DPS_FRQ_CR10RW.Case_Num_Yr AND
				DPS_FR_CASE_RECORDS.Case_Num = 
				 	DPS_FRQ_CR10RW.Case_Num
    ElseIf Combo13 = 13 Then
        MsgBox " Now Creating Cancellation Letters ! "
            DoCmd.runMacro "FRM-CR13RW"
	ALTER TABLE DPS_FRQ_CR13RW ADD CONSTRAINT PK_CR13RW
		PRIMARY KEY(Case_Num_Yr,Case_Num)
	(run reports code)
	UPDATE DPS_FR_CASE_RECORDS
		SET DPS_FR_CASE_RECORDS.PrtNo_Num = €
			WHERE DPS_FR_CASE_RECORDS.Case_Num_Yr =
				DPS_FRQ_CR13RW.Case_Num_Yr AND
				DPS_FR_CASE_RECORDS.Case_Num = 
				 	DPS_FRQ_CR13RW.Case_Num
    ElseIf Combo13 = 20 Then
        MsgBox " Now Creating Finding Letters ! "
        DoCmd.runMacro "FRM-CR20RW"
	ALTER TABLE DPS_FRQ_CR20RW ADD CONSTRAINT PK_CR20RW
		PRIMARY KEY(Case_Num_Yr,Case_Num)
	(run reports code)
	UPDATE DPS_FR_CASE_RECORDS
		SET DPS_FR_CASE_RECORDS.PrtNo_Num = €
			WHERE DPS_FR_CASE_RECORDS.Case_Num_Yr =
				DPS_FRQ_CR20RW.Case_Num_Yr AND
				DPS_FR_CASE_RECORDS.Case_Num = 
				 	DPS_FRQ_CR20RW.Case_Num
    Else
        MsgBox " Invalid Selection Chosen, Try Again ! "
    End If
    
End Sub

-------------------------------------------------------------------------------------------------
Macro FRM-CR10RW follows:
OpenQuery (queryname=)FRQ-CR10RW
(view=)DATASHEET
(datamode=)EDIT
-------------------------------------------------------------------------------------------------

Query FRQ-CR10RW follows
-------------------------------------------------------------------------------------------------
SELECT DPS_FR_CASE_RECORDS.CASE_NUM_YR AS CASE_NUM_YR, 
DPS_FR_CASE_RECORDS.CASE_NUM AS CASE_NUM, DPS_FR_CASE_RECORDS.TICKLE_DATE AS 
TICKLE_DATE, DPS_FR_CASE_RECORDS.PRTNO_NUM AS PRTNO_NUM, 
DPS_FR_CASE_RECORDS.PRTD_CDE AS PRTD_CDE, DPS_FR_CASE_RECORDS.FR_FILE_NUM AS 
FR_FILE_NUM, DPS_FR_CASE_RECORDS.TYPIST_INIT_TXT AS TYPIST_INIT_TXT, 
DPS_FR_CASE_RECORDS.LIC_STAT_CDE AS LIC_STAT_CDE, 
DPS_FR_CASE_RECORDS.LIC_DL_STAY_CDE AS LIC_DL_STAY_CDE, 
DPS_FR_CASE_RECORDS.RESULT_CDE AS RESULT_CDE, DPS_FR_CASE_RECORDS.REVO_DATE 
AS REVO_DATE, DPS_FR_CASE_RECORDS.OFCR_NUM AS OFCR_NUM, 
DPS_FR_CASE_RECORDS.ACC_DATE AS ACC_DATE, DPS_FR_CASE_RECORDS.CNTY_NUM AS 
CNTY_NUM, DPS_FR_CASE_RECORDS.REQ_RECD_DATE AS REQ_RECD_DATE, 
DPS_FR_CASE_RECORDS.HRG_DATE AS HRG_DATE, DPS_FR_CASE_RECORDS.HRG_TIME_TXT AS 
HRG_TIME_TXT, DPS_FR_CASE_RECORDS.HRG_AM_PM_TXT AS HRG_AM_PM_TXT, 
DPS_FR_CASE_RECORDS.LOC_CDE AS LOC_CDE, DPS_FR_CASE_RECORDS.ATTY_NUM AS 
ATTY_NUM, DPS_FR_CASE_RECORDS.LIC_FIRST_NME AS LIC_FIRST_NME, 
DPS_FR_CASE_RECORDS.LIC_MIDDLE_NME AS LIC_MIDDLE_NME, 
DPS_FR_CASE_RECORDS.LIC_LAST_NME AS LIC_LAST_NME, 
DPS_FR_CASE_RECORDS.LIC_SUBT_TXT AS LIC_SUBT_TXT, 
DPS_FR_CASE_RECORDS.LIC_ADDR_TXT AS LIC_ADDR_TXT, 
DPS_FR_CASE_RECORDS.LIC_CITY_NME AS LIC_CITY_NME, 
DPS_FR_CASE_RECORDS.LIC_STATE_CDE AS LIC_STATE_CDE, 
DPS_FR_CASE_RECORDS.LIC_ZIP_CDE AS LIC_ZIP_CDE, 
DPS_FR_CASE_RECORDS.LIC_ZIP4_CDE AS LIC_ZIP4_CDE, 
DPS_FR_CASE_RECORDS.LIC_DL_NUM AS LIC_DL_NUM, 
DPS_FR_CASE_RECORDS.SECURITY_AMT AS SECURITY_AMT, DPS_FR_CASE_RECORDS.DOA_NME 
AS DOA_NME, DPS_FR_CASE_RECORDS.DOA_ADDR_TXT AS DOA_ADDR_TXT, 
DPS_FR_CASE_RECORDS.DOA_CITY_NME AS DOA_CITY_NME, 
DPS_FR_CASE_RECORDS.DOA_STATE_CDE AS DOA_STATE_CDE, 
DPS_FR_CASE_RECORDS.DOA_ZIP_CDE AS DOA_ZIP_CDE, 
DPS_FR_CASE_RECORDS.DOA_ZIP4_CDE AS DOA_ZIP4_CDE, 
DPS_FR_CASE_RECORDS.FLAG_CDE AS FLAG_CDE, DPS_FR_CASE_RECORDS.BATCH_DATE AS 
BATCH_DATE, DPS_FR_CASE_RECORDS.BATCH_NUM AS BATCH_NUM, 
DPS_FR_CASE_RECORDS.MEMO1_TXT AS MEMO1_TXT, DPS_FR_CASE_RECORDS.MEMO2_TXT AS 
MEMO2_TXT, DPS_FR_CASE_RECORDS.MEMO3_TXT INTO DPS_FRQ_CR10RW
FROM DPS_FR_CASE_RECORDS
WHERE (((DPS_FR_CASE_RECORDS.PRTNO_NUM)=10))
ORDER BY DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM;
------------------------------------------------------------------------------------------------------------

-- 
Robert Nusz
Sr. Programmer Analyst II

2.create Primary Key in a Make Table query???

Is it possible, in either a straight-up Make Table query,
or in an SQL statement run from a macro or module, to tell
the table that one of the fields is a Primary Key?

Thanks for any help with this!

3.Create Primary Key in Make Table Table

I need to set a field as the Primary Key in a make table query, there is
only one field and there will not be any duplicates. Can I do this in the
query?
Regards

4.Create Table with a multiple column primary key

I am trying to create a table with a multiple column primary key. When I run 
the code below I receive the follwoing error message:

"Cannot add multiple PRIMARY KEY constraints to table 'Daily_Price_ImportT'"


What is the correct coding to create a table with a multiple column primary 
key?

Thanks,

Gerry




Sub CreateImportTable(cnxn As ADODB.Connection)

  Dim TableStr As String

On Error GoTo Import_Daily_Price_Err


'Create Import tables

   TableStr = "IF NOT EXISTS(SELECT TABLE_NAME FROM 
INFORMATION_SCHEMA.TABLES " & _
              "WHERE TABLE_NAME = 'Daily_Price_ImportT') " & _
              "Create Table Daily_Price_ImportT " & "( " & _
              "TerminalNo Int NOT NULL PRIMARY KEY, " & _
              "Supplier nvarchar(100) NOT NULL PRIMARY KEY, " & _
              "Terminal_Name nvarchar(50) NOT NULL PRIMARY KEY, " & _
              "Terminal_Abbr nvarchar(20) Null, " & _
              "Terminal_City nvarchar(50) Null, " & _
              "Terminal_State nvarchar(5) Null, " & _
              "Product_Name nvarchar(120) Null, " & _
              "Brand_Type nvarchar(5) Null, " & _
              "Effective_Date datetime Null, " & _
              "Effective_Time datetime Null, " & _
              "Price real Null, " & _
              "Change real Null, " & _
               ")"
               
   cnxn.Execute TableStr

5.Creating a primary key on imported table

Hi again!

I've imported the text file to a table "customer list"

Now I want the first column in the table ('Field1') to be a primary
key. I tried to save it as a specification, that I can re-use when
doing the import but I think it's not saved there, as it didn't work.

So my question - I've got 4 different tables in Access. I'm importing
text file to a fifth table. And I want to make the 'Field1' a primary
key using VBA code. How can I achieve that?

Thanks

6. create a primary key in an existing table

7. Creating Tables from VB Code - Primary Key

8. creating tables from code - Primary Key