Results 1 to 10 of 10
  1. #1
    tfurubay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    5

    Access and 2 gig limit - any tips on managing this?

    Hello,



    I'm a lifelong access user and I am going to build the dream access desktop (if you could call it that). I'm hoping to get some advice on the ideal setup (both software and hardware) to support a single use reporting database with a lot of data.

    I love access for it's flexibility and ease of use however i have to continuously compact my db and move files around just to produce a weekly report. This past week, the report was so complex that i had to run it in four chunks.

    I thought i read somewhere that access 64 bit does not have a cap of 2 gigs but i'll believe it when i see it.

    thank you!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Had not heard that about 64 bit Access.
    Options maybe:
    1. move up to SQL Server
    2. an archive process, similar to accounting year end journal transactions
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    have to continuously compact my db and move files around
    Mind if I ask why you have to continuously compact your dB? Are you using a lot of make table queries? What is causing the bloat?


    Access has a file size limit of 2gb. If you are using a split database, the BE can be 2gb. You can also use multiple BEs. Put the more static tables in one BE, and the more active larger tables in another BE. If you have one table per BE, that means *each* BE can be 2gb.

    The next step is SQL Express with a 4 gb size.
    And like June said, the next step is SQL Server (limited by HD space).

  4. #4
    tfurubay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    5
    Hi Steve,

    not sure what BE is .

    so i basically run an email engine of 1.4 million emails that have around 1 million parent accounts (more than one email per person). every week, i run a business process that:

    1) scores each email
    2) assigns the best available email to an individual
    3) assigns custom copy to the indivudual

    I've found that the best way for me to do this is have a dedicated database for source data, a dedicated db for ETL and a dedicated database for final output. each time i run the above passes, = around 600 mb of data.

    the problems i am experiencing are each list es around 6 - 7 hundred mb so i have to generally paste each run to new database. if i make a mistake (which is often), then i have to reset all the databases to 0 and re-run.

    finally, the process is not scalable. i may have half a dozen feeder tables that have other related information that i need to add to the final email and in many cases, my processes have overloaded access due to memory or 2gb issues.

    as my DB gets bigger, i need a better infrastructure to manage this process. i am considering mysql but access is so much more powerful from a reporting standpoint.

    thanks!

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    FE = frontend
    BE = backend

    These would be the parts of a split database.

    MySQL could be the backend - data tables.

    Access could be the frontend - links to BE tables, then has queries, forms, reports, code for user interface
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    tfurubay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    5
    thanks. if i set up a local mysql database and attached an access front end, would i still be limited by 2gb for processing? in many cases, if the query is too large or complex, access errors out b/c either the 2gb cap is breached, or there is not enough memory whatever that means.

    another thing that puzzles me is occasionally, i will run a query in access only to get "query is too complex". If i close and re-open the DB, and re-run the same query, it runs fine.

    anyway, thanks so much for all the tips.

    Todd

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I have never used MySQL (tried once and abandoned) so can't advise on specifics.

    Would have to examine data and queries to address the 'too complex' issue. I also have no experience with 64 bit Access.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    there is not enough memory whatever that means
    How much RAM do you have in your computer?

    If i close and re-open the DB, and re-run the same query, it runs fine.
    Sounds like memory is not being released. Do you have any/a lot of code in your dB?

  9. #9
    tfurubay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    5
    i don't have that much code if we're talking modules. i do have a lot of queries but not sure if that counts as "code".

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yes, VBA code. Saved queries are not code.
    Just wondering if record sets get closed and released; also any/all objects get released properly. Memory leaks had been a problem in the past; not sure about 64bit Access 2010.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-31-2012, 06:15 PM
  2. Managing a CPR class
    By DiPietro14 in forum General Chat
    Replies: 1
    Last Post: 02-06-2012, 06:02 PM
  3. Tips to make access database easy to maintain
    By alsaf in forum Programming
    Replies: 9
    Last Post: 12-11-2011, 01:51 PM
  4. Managing Inventory in Access
    By bushkanaka86 in forum Access
    Replies: 1
    Last Post: 11-25-2011, 08:31 PM
  5. maybe some tips on a .csv query?
    By dr_patso in forum Access
    Replies: 3
    Last Post: 07-13-2011, 12:08 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums