Writing values to Access

[amegahed3]

Hi All,



I'm trying to write values back to an access database from an open source mathematical programming language (called glpk) using ODBC. I succeeded in doing so in most variables except one. The problem is described below:

The following code gets an error when writing the data:

 
table x3_iii {j in TF, k in C, p in P, t in T: x3[j,k,p,t]>0} OUT 'ODBC'
  'FileDSN=.\d2.dsn'

  'UPDATE ((Channels a INNER JOIN ChannelPeriodProducts b'
'ON a.ChannelID = b.ChannelID)'
'INNER JOIN TransformerTypes d'
'on a.OriginFacilityID = d.FacilityID'
'SET b.Quantity = ?'
  'WHERE d.TransformerTypeID = ?'
  'AND a.OriginFacilityID =?'
  '    AND b.ProductID = ?'
  '    AND b.PeriodID = ?':
  x3[j, k, p, t], j, k, p, t;

Note that the sql is on multiple lines, because of a maximum strings limit in the glpk (but the way I used in the code is fine and worked without problems for writing other variables).

Now, let me describe what I am basically trying to do: I'm trying to write the values for the variable x3[j,k,p,t] in its field "Quantity" in the table 'ChannelPeriodProducts'. The indices for that variable are j,k,p,t.

The 'ChannelPeriodProducts' table has a "ChannelID", "PeriodID", "ProductID" and "Quantity" fields. The index t is corresponding to "PeriodID", the index p is corresponding to the field "ProductID", and the variable x3[j,k,p,t] itself, as I said, is corresponding to the field "Quantity".

The 'Channels' table has a "ChannelID", "OriginFacilityID" and "DestinationFacilityID" fields. The index k is corresponding to the field "DestinationFacilityID". The "ChannelID" field in it is corresponding to the "ChannelID" in the 'ChannelPeriodProducts' table.

Finally, the 'TransformerTypes' table has a "FacilityID" and a "TransformerTypeID" fields. The index k is corresponding to the field "TransformerTypeID". The "FacilityID" field in this 'TransformerTypes' table is corresponding to the 'DestinationFacilityID' field in the Channels table.

Note that there are existing records in all tables, with the "Quantity" field in these records in the table 'ChannelPeriodProducts' empty (as it is to be filled by the output of the model).

Hope it is clear. I'd REALLY appreciate any help telling me how I can modify the above code in order to do what I want to do as explained above, or even provide me with a different code that does what I want to do!

Thanks a lot,

Aly

[amegahed3]

Well, after MANY trials and asking different people, I was able to get the correct code. Here it is for anyone's reference:

table x3_iii {j in TF, k in C, p in P, t in T: x3[j,k,p,t]>0} OUT 'ODBC'
'FileDSN=.\d2.dsn'


'UPDATE ((Channels a INNER JOIN ChannelPeriodProducts b'
'ON a.ChannelID = b.ChannelID)'
'INNER JOIN TransformerTypes d'
'on a.OriginFacilityID = d.FacilityID)'
'INNER JOIN Customers cc'
'on a.DestinationFacilityID=cc.FacilityID'

'SET b.Quantity = ?'

'WHERE d.TransformerTypeID = ?'
'AND cc.FacilityID =?'
' AND b.ProductID = ?'
' AND b.PeriodID = ?':
x3[j, k, p, t], j, k, p, t;


Best,

Aly