Unanswered: [ODBC Microsoft Access Driver] System Resource Exceeded
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:
DRIVER=Driver do Microsoft Access (*.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):
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).
The error is the same but the situation is very different. I was using the ODBC driver for SQL Server and whenever I exported a large chunk of data (50K records) to SQL Server my append query would fail.
After much tinkering, it turned out that it was due to the Memo fields that I was occasionally updating. When those fields were included the query would fail. When I took the Memo fields out of the append query then the query would execute properly.
I don't know if these two issues are at al related, but I figured I would throw it out there. Do you have any Memo fields in Access?
I have not fiddled around in this side of Access for a while, but I seem to remember issues with constraints tied to Jet and other pieces of the Access plumbing long ago. I recall performing a registry edit to up the limit of data Jet would sling at one time. Although you are using ODBC, the Acess side of the equation is probably using Jet.
You can experiment by adding "TOP X" (where X is a number) to your selection query to see how big X can be before it stops working.
Try searching the MSDN site for information on record limits with Jet and how to get around them.
As a work-around, can you move the data in chunks? Add two boolean fields MoveReady and MoveComplete.
1) Take the "TOP 500" and update MoveReady to true
2) Move all records with MoveReady True and MoveComplete False
3) Set MoveComplete True for all records with MoveReady True and MoveComplete False
Choose an appropriate number (I threw in 500 as an example) based on your setup.