Create a query in a new database

[Biggles]

I want to open an MPD file and insert the following query,

ssql(19) = "TRANSFORM First(MSP_TEXT_FIELDS.TEXT_VALUE) AS
FirstOfTEXT_VALUE " & _
"SELECT MSP_TEXT_FIELDS.PROJ_ID, MSP_TEXT_FIELDS.TEXT_REF_UID
" & _
"INTO tblCT_TEXT_FIELDS " & _
"From MSP_TEXT_FIELDS " & _
"GROUP BY MSP_TEXT_FIELDS.PROJ_ID,
MSP_TEXT_FIELDS.TEXT_REF_UID " & _
"PIVOT MSP_TEXT_FIELDS.TEXT_FIELD_ID "


but I am running this from Project and the following does not work:

Set AccessApp = CreateObject("access.application")
AccessApp.opencurrentdatabase proj_file

ssql(19) = "TRANSFORM First(MSP_TEXT_FIELDS.TEXT_VALUE) AS
FirstOfTEXT_VALUE " & _
"SELECT MSP_TEXT_FIELDS.PROJ_ID, MSP_TEXT_FIELDS.TEXT_REF_UID
" & _
"INTO tblCT_TEXT_FIELDS " & _
"From MSP_TEXT_FIELDS " & _
"GROUP BY MSP_TEXT_FIELDS.PROJ_ID,
MSP_TEXT_FIELDS.TEXT_REF_UID " & _
"PIVOT MSP_TEXT_FIELDS.TEXT_FIELD_ID "

AccessApp.Execute ssql(19)
'CONN_PROJ.Execute ssql(19)
AccessApp.closecurrentdatabase

The MPD file will not be created until steps above this are taken to create
the project. is there something different I should be doing, or is the above
correct and I am running into limitations in project.

 

[Biggles]

I have moved on a little since I posted this, mainly in realizing I don't
need to create a table. What I would like to do is save ssql(19) as a new
query in the MPD file represented by file_path. From Project I don't quite
know how to do this, but I am hoping I can figure it out with a little VBA
help. Should this work:

Set AccessApp = CreateObject("access.application")
AccessApp.opencurrentdatabase proj_file

ssql(19) = "TRANSFORM First(MSP_TEXT_FIELDS.TEXT_VALUE) AS
FirstOfTEXT_VALUE " & _
"SELECT MSP_TEXT_FIELDS.PROJ_ID, MSP_TEXT_FIELDS.TEXT_REF_UID
" & _
"From MSP_TEXT_FIELDS " & _
"GROUP BY MSP_TEXT_FIELDS.PROJ_ID,
MSP_TEXT_FIELDS.TEXT_REF_UID " & _
"PIVOT MSP_TEXT_FIELDS.TEXT_FIELD_ID "

AccessApp.creatquerydef "tblCT_TEXT_FIELDS", ssql(19)
AccessApp.closecurrentdatabase

Alterntatively, can this be done. I need the output of the ssql(19) above
to inner join in another dataset later on:

Set RS_PROJ(7) = New ADODB.recordset

ssql(17) = "SELECT MSP_TASKS.PROJ_ID, MSP_TASKS.TASK_UID,
MSP_TASKS.TASK_ID, " & _
"MSP_TASKS.TASK_WBS, MSP_TASKS.TASK_NAME, " & _
"tblCT_TEXT_FIELDS.[188743731],
tblCT_TEXT_FIELDS.[188743734], " & _
"tblCT_TEXT_FIELDS.[188743737],
tblCT_TEXT_FIELDS.[188743740], " & _
"tblCT_TEXT_FIELDS.[188743743],
tblCT_TEXT_FIELDS.[188743746] " & _
"FROM MSP_TASKS INNER JOIN tblCT_TEXT_FIELDS " & _
"ON (MSP_TASKS.TASK_UID = tblCT_TEXT_FIELDS.TEXT_REF_UID)
" & _
"AND (MSP_TASKS.PROJ_ID = tblCT_TEXT_FIELDS.PROJ_ID)
" & _
"WHERE (MSP_TASKS.PROJ_ID = " & RS_PROJ(6)!PROJ_ID & ") "
& _
"ORDER BY Val([188743731]), MSP_TASKS.TASK_WBS"

RS_PROJ(7).Open ssql(17), CONN_PROJ

Is it possible to join two different recordsets and create a third?