How to make table based on query created via DAO

[khwaja]

I am using the following code to create a query. Could someone help me modify the code to make table instead of a query containing fields and data captured through SQL code.

Sub CP()
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb

'If RecentHires query exists, delete it.
For Each qdf In dbs.QueryDefs
If qdf.Name = "Control Plan" Then
dbs.QueryDefs.Delete qdf.Name
End If
Next qdf

strSQL = "SELECT [Stores DB].LocNo, [Stores DB].Location, tblProjects.ProjectNo," _
& " tblProjects.PCSubmission, tblProjects.Signoff, Max(tblControlPlan.CPRev) AS DAmend, Max(tblControlPlan.CPConstSent)" _
& " AS DDOC, 'Control Plan' AS Dstatus FROM ([Stores DB] INNER JOIN tblProjects ON" _
& " [Stores DB].LocNo = tblProjects.LocNo)INNER JOIN tblControlPlan" _
& " ON tblProjects.ProjectID = tblControlPlan.ProjectID GROUP BY [Stores DB].LocNo," _
& " [Stores DB].Location, tblProjects.ProjectNo,tblProjects.PCSubmission, tblProjects.Signoff ;"

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

End Sub

Cheers

AK

 

[DonQuichote]

Look in the help for the SELECT statement. Make table queries have a "SELECT INTO..." syntax

Best regards

 

[khwaja]

Thanks a lot. I will have a look.

Cheers

AK

 

[khwaja]

I looked at help and it worked well. I have opne question though, if you could answer. How do you indicate wgich is to become primary key as party of make table code? i could not quite find anything on this in help. Kind regards



Cheers

AK

 

[khwaja]

I looked at the help and it worked well. I have one question though, if you could answer. How do you indicate which is to become primary key as party of make table code? I could not quite find anything on this in help. Kind regards



Cheers

AK