Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Unanswered: [ODBC Microsoft Access Driver] System Resource Exceeded

    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

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    define "big". Access has a 2 GB limit.

    Perhaps use SQL Server (SQL Server Express is free and offers a much larger db limit)
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Aug 2010
    Posts
    6
    No, it's not that big at all, actually. It's only about 200 MB. That's why I believe there should be a way to avoid the memory problem I'm running into.

    Any ideas on answers to my 2 earlier questions?

    Thanks a lot for your reply.

    Aly

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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?

  5. #5
    Join Date
    Aug 2010
    Posts
    6
    Hi DCKunkle,

    Thanks a lot for your reply. No, I don't think I have any memo fields in my Access database!

    Aly

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    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.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •