Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: MS Access Limitations, need to upgrade

    Hi,

    I'm new here, so I apologize if this issue has been covered elsewhere. I am a relatively advanced user of MS Access, which we use as a frontend application with SQL Server as the backend. I routinely have issues with the 2GB limit for each database and also the inability to export more than 65K records to Excel (I know I can export as text and open in Excel as a work-around).

    Some of our tables and views have millions of records and sometimes I have to generate results that exceed the limitations (i.e., customer mailing lists with close to 2 million records and growing or item pricing files with 70K+ records that need to be manipulated in Excel).

    I was wondering if anyone had any recommendations for a higher capacity front end application for someone who is not a programmer, but fairly comfortable with SQL view in Access.

    Thanks,

    Jeri B

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't understand how you could have issues with the 2GB limit if the data are stored on a SQL Server and Access is only used as a Front-end.
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    2

    Sorry, I should have been more specific

    Hi,

    I have to generate very large make-tables at times with 1-2 million+ records, often more than one in a single database, that are then manipulated in queries. My currents issues are with customer segmentations, where I pull all customer records and then, based on various criteria that I also pull in like data to determine Recency, Frequency and Monetary stats, Website vs. Catalog order counts, Amazon vs non-Amazon order counts, customers with sales history in more than one of our divisions, etc., assign source codes for our catalog mailing lists.

    Sometimes, the convoluted nature of the segmentation requires me to match these tables against each other and generate yet another table for results. I can usually find work-arounds, or just bust up one project into multiple databases, but I was hoping there was a better and less aggrevating solution.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so use pass through queeries, leave the data on the server and leverage the power of the server db. let the server handle the data manipulation and use the front end to handle the user interface

    doing it your way means the worst of both worlds.. the overhead of the server, the overhead of transmitting the data down the network, with precious few of the advantages of switching to a server back end
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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