Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    23

    Unanswered: Access 2007 2 gig limitation and HCRIS db

    First...great FAQ...always nice to get a good laugh to start.

    Second...I am well aware that the HCRIS instructions caution against the use of Access to manipulate the HCRIS database...so I'm looking for a work-around if there is such a thing.

    The Facts I am an idiot at this level of database manipulation...I have worked with relatively large db's using canned queries...but I have an inquiring mind...I'm interested...you might say challenged.

    The Scenario - I work in a Microsoft office...we have a relatively large server and a pier to pier network with a panoply of Microsoft user machines, of which, I am one relatively insignificant unit using XP. We recently colluded to discover the vagaries of the HCRIS database while ignoring (not my decision) the 2 gig Access limitation and the warnings thereof.

    The Result...a freaking mess and repeated errors messages citing the Access 2007 2 gig memory limitation. For the uninitiated (to the HCRIS db) a single table (four each year...I need three years) will commonly contain 20 million records.

    The Question FINALLY!!!!

    Part 1 Is it likely there a reasonable workaround? I am getting by working in single years but am having trouble doing secondary queries (possibly poor terminology) . A simple opinion/answer will be appreciated, a reference to a published discussion (walk through) will be revered.

    Part 2 Would I do better moving to another server software? The HCRIS documentation suggests Oracle, SQL, etc. I dream of elegant solutions where I load all 12 tables and perform mysterious arith-metic operations to reveal great truths of great use. My investment at this point is a few Access books and too much time polishing the query data.

    The REAL Question if you (the helpful reader/expert) were to endeavor to migrate to another server software...Which one and why? While I may, at first blush, seem too dull to attain such heights, I am not unmotivated and believe my cause is good. This does not mean that I secretly desire a new vocation, ie...I would prefer that this suggestion might take the learning curve and software cost into consideration.

    Have I managed to compose a post that will be ignored or have I scratched the scab from a deep and festering wound?

    Opinions sincerely appreciated.

    Hittman
    Andy Bruce

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    easiest work around use an Access front end (the presentation l\yer, forms &reports etc), ditch the JET back end (the data storage bit) and replace with a suitable server back end which could be SQL server or anything elser ranging from MySQL to ORacle depending ion budget, hardware, experience and prejudices with the development or even management team)

    bear in mind JET isn't a server database product its a file server product) and isn't really intended fo ruse in large businesses.

    I doubt the 2gb limit will be lifted any time in the near future, theres an awful lot of odd limits wiht Access/JET which show how old the basic product is..
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    23
    Thanks Healdem,
    That was my suggestion from the beginning. I was led to believe that I wasn't "doin it right" when the memory messages started showing up and then suddenly everyone in the tech support dept got too busy to help me overcome my obvious ignorance.
    So now...since I'm on my own on this project...is there a free/cheap (less than $500) solution I could begin with to get my feet wet (and maybe garner support later). We all agree we need this information. I know there's probably more than one component I need. I've spent a little time in Access and don't really mind the interface but I will have to load this in my personal computer. That means I won't have a front end either...I doubt the tech guys who bungled this will help me solve the problem now. An additional consideration would be that I get the feeling Access is pretty easy to use, ie...I don't know anything about scripting or queries really.
    SO I would also appreciate a little guidance toward references and well supported systems such as those on this forum.
    For example, I was considering MySQL for the presumed quality of the open source user community and because I would guess that since I have no issues with design or maintenance that my needs are fairly basic...extraction of data.
    If I can figure out how to successfully query the HCRIS database to develop dynasets (may be an old term), then I could put them on a pin drive and import them into Access/Excel right?
    Any palpable reason I shouldn't choose MySQL? ...such as memory problems or won't run on XP? I have 8g of ram and plenty of hard disk space.
    Thanks again,
    Hitman
    Andy Bruce

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it don't come cheaper than MySQL, Free...
    there are others in that area.. Postgres, Firebird and so on. not knowing what HCRIS is limits realistic suggestions

    depending on the number of users you may also be able to use the desktop editions of SQL Server, DB2, Oracle and so on

    MySQL will run on very modest hardware, the more modest it is the slower it may be.. so whether its the 'right' solutuon for you depedns on what you regard as acceptable performance

    if you go down the MySQL route then choose to use INNODB tables not MyISAM.. as MyISAM hits a 4Gb limit

    dont' fall into the trap that Access is JET and JET is Access.. Access is a RAD a front end development tool its not and never will be a database per se... having said that the default datastore for Access is JET (ie unless you sepcify somehtign else you will use JET and JEt has all those nasty limits built in to it.. after all most of the fundamental design assumptions built into JET date back some 10..15 years

    again as said before I haven't a Scooby what HCRIS is so whether you need the Access front end or not I don't know. likewise I don't understand what it is you want to do. if 'all' you want to do is stuff data into a SQL engine and do comprisions then any server product should do.

    BTW if you are going down that route then you don't / shouldn't use 1 table per quarter of figures. use one table and identify the quarter by date within that one table. prune data as required
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2010
    Posts
    23

    more info...sorry

    HCRIS is one of the Medicare databases...quite large as I appreciate it.

    Nowhere in the documentation does the contractor suggest MySQL but Oracle is specifically mentioned. I don't want to slip into a money pit but I would prefer to use Oracle if it isn't too terribly expensive. The problem is I'm not sure what all I will actually need to prune data?... interesting term. I can buy the Standard Oracle database module ($350...minimum order 5 = $1750) or will the Oracle Web Logic Server - Std Edition ($200 named user plus...don't know what plus means) do the trick. AND will either of those give me what I need or just get me started on an expensive multi-module commitment.

    I could be the only user for now if i can create dynasets that I can export to Access/Excel.

    Please explain what you mean "1 table per quarter of figures". Is that a calendar quarter. Sorry about the really dumb question since I suspect that is one.

    What I trying to do is extract financial data over a period of years (3) and develop trend analyses for those periods (which could be done in Access/Excel relatively easily I think). Not really rocket science...I know what data I need and what I want to do with it. I'm simply running into the size limitation before I can get it to a useable format. It seems like the query methods I have been using in Access cause the data to grow in size (or, more likely eat up memory) because I cannot seem to figure out how to pull more than one or two data pieces without huge duplication and after Access removes the duplication I can't query again without hitting the limitation.

    Could be I'm not doing it right but I think I'm following published procedure. I should be much more specific and will be when I try the new server software. I simply think the db manager knows his product and will not specifically warn against a software that will do the job.

    Is Oracle relatively easy to learn? MySQL claims to be. I suspect you can tell I don't know much about this...but I can read and follow directions.

    Sorry for the rambling post. I just want to start on the right path this time. I appreciate your time and suggestions.

    Hittman
    Andy Bruce

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the one table per quarter is an extrapolation of what you have said earlier

    ie 3 years data, 12 tables so that suggests the data is ocming to you in 3 monthly chunks ie one quarter of a year.

    if this is a limited use data warehouse type (limited use in terms of number of people accessing the data you may be able to get away with one of the desktop server versions of the maor players.. all have released a free desktop version to compete with MySQL and stop loosing customers potetnail or otherwise to MySQL / Postgres and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2010
    Posts
    23
    yes... it is limited.

    OK...actually it's four tables per year but it works out the same...

    my heartfelt appreciation for your time and consideration.

    I'll let you know how it comes out

    Thanks again, Healdem.

    Hittman
    Andy Bruce

  8. #8
    Join Date
    Jun 2006
    Posts
    5

    Can I follow up on this size limit thread?

    I am a self-taught Access user. I've built a database for a client and will quickly run into the 2 gig limit. Can I use MySQL on my desktop to access the data stored on a network and still have people using Access as the front end? Are there any issues with the Access front end being linked to GIS data? Are there any books to walk me through these steps?

    Thanks.

    Marybeth

Posting Permissions

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