Define PK using code 2

[khwaja]

I am using the following statement to create a table using contents of a qyery 'Control Plan'.

dbs.Execute "SELECT [Control PLan].* INTO " _
& "[tblTest] FROM [Control Plan]

All I need is to be able to indicate a primary key. I tried using the following statement but it does not work as there is error message 'Syntax in FRom Clause. Will appreciate if you could help.

dbs.Execute "SELECT [Control PLan].* INTO " _
& "[tblTest] FROM [Control Plan]CONSTRAINT [ProjectNo]PRIMARY KEY;"

Cheers

AK

 

[jrbarnett]

AK

A Select Into query (sometimes referred to as a Make Table) doesn't have a primary key set as it runs, but by running the make table element:

"SELECT [Control Plan].* INTO [tblTest] FROM [Control Plan]"

followed by a data definition query to create the primary key can set this:

"ALTER TABLE [tblTest] ADD CONSTRAINT [ProjectNo] PRIMARY KEY;"

Note however that this query will only need to be run once, unless the table is deleted.

You can then use an ordinary select query to retrieve the data from your table.

John

 

[khwaja]

Thanks John. I have recently started learning writing code so excuse my ignorance. Based on your advice, I tried to use your additional code in the following two forms and it did not work in either:

Tried this first:

dbs.Execute "SELECT [Control PLan].* INTO " _
& "[tblTest] FROM [Control Plan] ALTER TABLE [tblTest] ADD CONSTRAINT [ProjectNo] PRIMARY KEY;"

Tried this later as a second statement after the statement above:

dbs.Execute "ALTER TABLE [tblTest] ADD CONSTRAINT [ProjectNo] PRIMARY KEY;"

I am sue I am making a mistake somewhere but not sure how else to add your code. I am deleting the table each time so PK should be available all the time.

Appreciate your help.

Cheers

AK

 

[jrbarnett]

AK,

SQL statements can either be run as a query (go to query -> new -> escape to bypass wizard and SQL on the toolbar to get to SQL view then copy and paste the code, then the run icon (red caution mark).
Alternative is to press Ctrl G to open the debug window and either dbs.Execute "SQL string" or DoCmd.RunSQL "SQL string"
as you have been doing.

The first query (Select Into) must be run before the alter table can be executed. It is not possible to join the two together in a single string.

John

 

[H82W8]

Using Access 2000, the following worked for me

dbs.Execute "ALTER TABLE tblTest " & _
"ADD CONSTRAINT makePK PRIMARY KEY (ProjectNo)"

DoCmd.RunSQL "ALTER TABLE tblTest " & _
"ADD CONSTRAINT makePK PRIMARY KEY (ProjectNo);"

DoCmd.RunSQL "SELECT * INTO tblTest " & _
"FROM [Control Plan];"

Note the constraint required an arbitrary name for which I chose "makePK".

The first required I create a database object named dbs using DAO references and set that object = CurrentDB().
Ex. Dim dbs as Database
Set dbs = CurrentDB()

Hope this helps,
Scott

 

[H82W8]

Correcting the sequence of commands...

DoCmd.RunSQL "SELECT * INTO tblTest FROM [Control Plan];"
DoCmd.RunSQL "ALTER TABLE tblTest " & _
             "ADD CONSTRAINT makePK PRIMARY KEY ProjectNo);"

OR

dbs.Execute "SELECT * INTO tblTest FROM [Control Plan];"
dbs.Execute "ALTER TABLE tblTest " & _
            "ADD CONSTRAINT makePK PRIMARY KEY (ProjectNo)"

 

[khwaja]

I am very grateful for the help you guys have provided. I was able to successfully assign the primary key using either of these solutions. There is a minor hitch though. Before assigning the PK, I need to delete duplications in the field to be assigned PK. I created the following code which takes me to the point where I can see duplicate entries which I delete manually. I would like to have this happen automatically before assigning the PK. Would you be kind enough to direct me on what statement need to appear to delete duplicates.

Sub DelDup()

'Quey to delete duplicate records in order to create unique project numbers

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb

'If query exists, delete it.
For Each qdf In dbs.QueryDefs
If qdf.Name = "qryDup" Then
dbs.QueryDefs.Delete qdf.Name
End If
Next qdf
strSQL = "SELECT DISTINCTROW tblConstruction.ProjectNo, tblConstruction.Location," _
& "tblConstruction.Dstatus FROM tblConstruction WHERE (((tblConstruction.ProjectNo)" _
& " In (SELECT [ProjectNo] FROM [tblConstruction]As Tmp GROUP BY [ProjectNo]" _
& "HAVING Count(*)>1 )) AND ((tblConstruction.Dstatus) Not Like '*Control*'))" _
& "ORDER BY tblConstruction.ProjectNo;"

Set qdf = dbs.CreateQueryDef("qryDup", strSQL)
'Open query in Datasheet view.
DoCmd.OpenQuery qdf.Name
Set dbs = Nothing
End Sub


Cheers

AK