[ODBC Microsoft Access Driver] System Resource Exceeded (1 Viewer)

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

How large is your large database? Does it exceed or come close to 2GB, if so that is the physical limit for an Access 2Kx database, you will be able to read data from a db that size but will not be able to write any more to it.

Thanks a lot for your reply, DJKARL.

No, it's not that big at all actually. It's only about 200 MB.

Any ideas?

Well google didn't turn up much but one common theme I saw was improperly closed connections could lead to this error. If there is a way you can reboot the server/pc this database resides on then try to connect to it asap, if you succeed then either your process or another process may not be terminating the connection properly and causing Access to think too many users are connected at one time.

Thanks for your help.
No, unfortunately rebooting didn't help at all. The database and GLPK code are both located on my personal machine (not connected to any servers).

Any ideas on how to close the connection after calling it each time though?

Aly