-
I am trying to create a recordset using the SHAPE APPEND command, and have the following written:
rst.Open "SHAPE {SELECT * from Asset WHERE Field1='S65A0010'} " & _
"APPEND ({SELECT * from ValAssetFeat} " & _
"RELATE AssetID TO AssetID) AS NIC1", cnn
The tables Asset and ValAssetFeat are linked by the AssetID key (pk in Asset and fk in ValAssetFeat).
This will work, BUT, if I try the following:
rst.Open "SHAPE {SELECT SerialNo, Field1 from Asset WHERE Field1='S65A0010'} " & _
"APPEND ({SELECT * from ValAssetFeat} " & _
"RELATE AssetID TO AssetID) AS NIC1", cnn
where I just want the SerialNo and Field1 fields returned, I get an error as follows:
Run-time error '-2147217900 (80040e14)':
The referenced column (AssetID) does not exist in the appropriate rowset.
I have tried SELECT dbo.Asset.SerialNo, SELECT Asset.SerialNo and the following whole new command:
rst.Open "SHAPE {SELECT SerialNo, Field1 from Asset} " & _
"APPEND ({SELECT dbo.ValAssetFeat.ValString FROM dbo.ValAssetFeat INNER JOIN " & _
"dbo.Asset ON dbo.ValAssetFeat.AssetId = dbo.Asset.AssetId} " & _
"RELATE AssetID TO AssetID) AS chapNIC1", _
cnn
all to no avail, getting various errors.
Does anyone have any idea how to write a stupid SHAPE APPEND statement that actually works?? This is so frustrating!!
Thanks in advance.
Cindy
-
You need to add the field called "AssetID" to the parent recordset so that you would be able to link it to the child recordset:
rst.Open "SHAPE {SELECT AssetID, SerialNo, Field1 from Asset WHERE Field1='S65A0010'} " & _
"APPEND ({SELECT * from ValAssetFeat} " & _
"RELATE AssetID TO AssetID) AS NIC1", cnn
Good Luck!!!
