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.

Thanks a lot,

Aly

 

[Rabbie]

Looks like you need to ask an expert in glpk. Your code sample doesn't look like Access to me

 

[amegahed3]

Actually I asked a glpk expert (actually the developer of it) and he couldn't help me saying that it is more of an SQL problem.

Also, I'm TOTALLY open to ANY code that would do what I want to do it. That is why I described in detail what I wanted to do. So could you please help me (weither correcting the code I had or providing me with ANY other code/idea).

Thanks,

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