[ODBC Microsoft Access Driver] System Resource Exceeded

[amegahed3]

Hi all,

I connect to Access from an open source optimization language (GLPK) through ODBC, in order to read some data, which I use as model inputs in GLPK and then I make the GLPK writes outputs back to Access. I use Access 2007 and windows xp.This has been working pretty well, but now for a larger database, I'm able to read the data, but when it comes to writing the outputs back to Access, seems like I'm running into a memory problem; as I get the error: HY001:1:-1011:[Microsoft] [ODBC Microsoft Access Driver] System Resource Exceeded.

To give you an idea on how I connect through ODBC, I do the following: I have a .dsn file called d2.dsn which has the following lines written in it:

[ODBC]
DRIVER=Driver do Microsoft Access (*.mdb)
DBQ=./my_database.mdb

I call this file each time I need to read from or write to a certain table in the database my_database.mdb. An example is the following (that is part of my GLPK code):

table bom_jpvt IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'TransformerTypeAssemblyPeriodBOMProducts' :
bom_set <-[TransformerTypeID, InputProductID, OutputProductID, PeriodID], bom ~ InputRatio;


So, I have similar lines to this above code for all data inputs that I read, and then for all those that I write back to Access, and the problem as I mentioned above is that when the database is large (put in mind that the same code works perfectly for smaller databases), I get that resource exceeded error. I tried searching for ways to treat that error but all what I found didn't help (some people recommended restarting which didn't change anything, some recommended switching to MySQL (which I wouldn't prefer) and some recommended increasing the memory (I tried it on another machine with higher RAM but nothing changed much)).

Now my questions are:

1. Is there any way I can solve that error? I think either closing each ODBC connection after opening it or decreasing the number of times I open the ODBC connection might help, but I don't know how to do either. Does any of you guys know how I can do either, using the same framework I'm using?? or do you know of any other ideas to overcome that error?

2. I'm thinking that, maybe, switching to windows 7 (64 bit windows) might help, as that would give me more access to memory maybe. I tried so, but I was not able to ever connect to ODBC on windows 64 bit, as there seems to be 2 drivers for ODBC under 64 bit windows, and I don't know how to connect to the 64 bit driver, again using the same framework I'm currently doing (the one described above).

Any help would be greatly appreciated!

Thanks a lot.

Aly

 

[Hap007]

How about defining a Global Open statement that opens the DB or table once when the application starts.

GLOBAL DBname as Database
GLOBAL RSName as RecordSet
GLOBAL DBOpen as Integer
Function OpenDB()
IF DBOpen Then Exit Function
'Open DB code now
DBOpen = TRue
END Function


From that point, just perform you I/O against the Global open DB.

When you exit the App, you could close the connection if you need.

This way, you only open once.


Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[amegahed3]

Hi Hap,

Thanks a lot for your reply. I have the following questions, please:

1. Could you please specify exactly where I should write that code?

2. how to run it?

3. How would I call it each time I need to read or write to the database?

4. In the code, I don't see any reference to the database location in my hard drive, or any reference to its name?!

I'm sorry if the questions are basic, but I'm not an expert in either databases or programming at all!

Best,

Aly

 

[Hap007]

Hi Aly,

You would have to post a part of your code so I could see what you are doing.

I could then mark up your code with my suggestions

Waiting to see your sample

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[amegahed3]

Here is part of my code (but put in mind that it is a GLPK code). Also, the file d2.dsn that I call has only the lines I posted above.

Here is the part from my code (it is basically the exact same as my whole code, except that my code is a bit bigger, but that sample contains all distinct features of the complete version:



/* DELETE PREVIOUS OUTPUTS */



table prepp8 {bb8 in 1..1} OUT "ODBC"
'FileDSN=.\d2.dsn;'
# The preparatory SQL statement(s) follow
'Update ChannelPeriodProducts'
'SET ChannelPeriodProducts.Quantity=0;'
# The next SQL statement is needed to satisfy the GMPL syntax.
'SELECT ?;' : bb8;

table prepp9 {bb9 in 1..1} OUT "ODBC"
'FileDSN=.\d2.dsn;'
# The preparatory SQL statement(s) follow
'Update TransformerTypeAssemblyPeriodBOMProducts '
'SET TransformerTypeAssemblyPeriodBOMProducts.InputQuantity=0;'
# The next SQL statement is needed to satisfy the GMPL syntax.
'SELECT ?;' : bb9;

table prepp10 {bb10 in 1..1} OUT "ODBC"
'FileDSN=.\d2.dsn;'
# The preparatory SQL statement(s) follow
'Update TransformerTypeAssemblyPeriodProducts '
'SET TransformerTypeAssemblyPeriodProducts.OutputQuantity=0;'
# The next SQL statement is needed to satisfy the GMPL syntax.
'SELECT ?;' : bb10;










/* DEFINE AND READ INPUTS from ACCESS */



set S;

table set_i IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'Suppliers':
S<- [FacilityID];

set P;
table set_p IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'Products':
P<- [ProductID];

set TF;
table set_j IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'TransformerTypes':
TF<- [TransformerTypeID];

set R;
table set_r IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'Resources':
R<- [ResourceID];

set TR;
table set_j IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'Transformers':
TR<- [FacilityID];

set tcap_irt_set, dimen 3;
param tcap_irt{(i,r,t) in tcap_irt_set};
table tcap_irtt IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'SupplierPeriodResources' :

tcap_irt_set <-[FacilityID, ResourceID, PeriodID], tcap_irt ~ Capacity;
set tcap_ipt_set, dimen 3;
param tcap_ipt{(i, p, t) in tcap_ipt_set};
param pc{(i, p, t) in tcap_ipt_set};
table tcap_iptt IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'SupplierPeriodProducts' :
tcap_ipt_set <-[FacilityID, ProductID, PeriodID], pc ~ UnitPurchasePrice, tcap_ipt ~ Supply;

set Ptcap_ipt_set, dimen 1;
table Ptcap_iptttt IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'SELECT DISTINCT ProductID FROM SupplierPeriodProducts':
Ptcap_ipt_set<- [ProductID];

set tres_iprt_set, dimen 4;
param tres_iprt{(i,p,r,t) in tres_iprt_set};
table tres_iprtt IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'SupplierPeriodProductResources' :
tres_iprt_set <-[FacilityID, ProductID, ResourceID, PeriodID], tres_iprt ~ UnitUse;

set ccap_ijrt_set, dimen 4;
param ccap_ijrt{(i, j, r, t) in ccap_ijrt_set};
param crc_ijrt{(i, j, r, t) in ccap_ijrt_set};
table ccapijrttt_ijrt IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'SELECT'
'a.OriginFacilityID, '
'b.ResourceID,'
'b.PeriodID, b.Capacity,'
'd.TransformerTypeID,'
'b.UnitCost'
'FROM ((Channels a INNER JOIN ChannelPeriodResources b'
'ON a.ChannelID = b.ChannelID)'
'INNER JOIN Suppliers c'
'on a.OriginFacilityID = c.FacilityID)'
'INNER JOIN TransformerTypes d'
'on a.DestinationFacilityID = d.FacilityID':
ccap_ijrt_set <-[OriginFacilityID, TransformerTypeID, ResourceID, PeriodID], ccap_ijrt ~ Capacity, crc_ijrt ~ UnitCost;








/* DEFINE MATHEMATICAL MODEL */



var pq{(i, p, t) in tcap_ipt_set}>=0;
var x1{(i, j, p, t) in ccap_ijpt_set}>=0;
var x2{(j, o , p, t) in ccap_jopt_set}>=0;
var x3{(j, k, p, t) in ccap_jkpt_set}>=0;

minimize cost: sum{(i, p, t) in tcap_ipt_set} pc[i,p,t] * pq[i,p,t]

s.t. tcap_irttt{(i,r,t) in tcap_irt_set}: sum{p in P: (i,p,t) in tcap_ipt_set} tres_iprt[i,p,r,t] * pq[i,p,t] <= tcap_irt[i,r,t];

s.t. tcap_ipttt{(i, p, t) in tcap_ipt_set}: pq[i,p,t] <= tcap_ipt[i,p,t];
solve;









/* WRITE OUTPUTS TO ACCESS */



table pq_iii {(i, p, t) in tcap_ipt_setq[i,p,t]>0} OUT 'ODBC'
'FileDSN=.\d2.dsn'
'UPDATE SupplierPeriodProducts'
' SET Quantity = ?'
' WHERE FacilityID = ?'
' AND ProductID = ?'
' AND PeriodID = ?':
pq[i, p, t], i, p, t;


table iq_iii {(j, p, t) in icap_jpt_set:iq[j,p,t]>0} OUT 'ODBC'
'FileDSN=.\d2.dsn'
'UPDATE TransformerTypeStoragePeriodProducts'
' SET StorageQuantity = ?'
' WHERE TransformerTypeID = ?'
' AND ProductID = ?'
' AND PeriodID = ?' :
iq[j, p, t], j, p, t;



Hope that helps. Your help is certainly appreciated!

Best,

Aly

 

[Hap007]

Hi Ali,

I am sorry, but I guess I did not understand your original request/problem.

I know Access, VB, VBA and SQL, but not GLPK.

This is an Access forum, sounds like you need a GLPK forum.
GLPK Forum:

http://old.nabble.com/Gnu---GLPK---Help-f1654.html

Perhaps someone else can help you on this forum or direct you to the correct forum.

Here are several Links:

http://www.connectionstrings.com/

http://www.gnu.org/software/glpk/#documentation

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[amegahed3]

Hi Hap,

That is the thing. I tried asking experts in GLPK, and they were not able to help me, as the problem is related to ODBC/Access connection issues, that take a large amount of memory and so my GLPK code crashes before it's finished.

So, my question was if there is a way that I can close a connection each time I open it and/or opening fewer connections. Note that I open the connection using the file 'd2.dsn' which has nothing but the three lines I indicated above. Here are those lines again for your convenience:

[ODBC]
DRIVER=Driver do Microsoft Access (*.mdb)
DBQ=./my_database.mdb

Also, my second question was how to get the ODBC connection on a 64 bit windows (as 64 bit windows have 2 drivers, one for 32 bit and one for 64 bit), and when I use the same method I am using, it uses the 32 bit one, while I need to use the 64 bit one in order to get access to more memory.

So, as you can see both questions are related to ODBC/Access not GLPK!

Hope that helps, and hope any of you guys can help me!

Best,

Aly