Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2014
    Posts
    3

    Unanswered: Access database balloons from 500 Mb to nearly 2 GB

    Hello All. I'm new to the forum (and to postgres, really). Recently, our database became to big for Microsoft Access to handle. Therefore we are commuting that data to postgres. The transition from sequel server to postgresql has been a challenge in terms of rewriting the queries. I'll just start with what I hope is a simple question.

    In Microsoft Access queries, I relied heavily on inline if (IIF) statements to return the data I wanted. Am I correct in understanding that postgreSQL doesn't use these statements? If so, what is the best equivalent expression-type to use when working with queries in Postgres?

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry I find it hard to believe that SQL server was not up to the job. blithely switiching from SQL server to POSTGRES in an Access application isn't going to achieve anything if the performance int he Access applciation is suspect.
    you need to redesign the Access applicatiion first
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2014
    Posts
    3
    The size limit for access is 2 GB. The database itself was only 500 mb. However, once I started running queries, the data caching would approach the size limit, and we started having problems with instability. It was recommended to me by a friend that does database management that I should try postgreSQL instead.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you have between 200-500 hours to really learn PostgreSQL and how to integrate it into your MS-Access application, then this is probably a good idea. PostgreSQL itself doesn't take all that long to learn. Figuring out how to do the integration that Microsoft has already done between Access and SQL Server will be the bulk of your effort.

    If your database is only 500 MB, the SQL Server Edition that ships with Microsoft Access shouldn't complain at all. I've seen many databases that sit near the 1.8 Gb size that run for months or years without any attention.

    I'm with healdem in this case... There's nothing wrong with PostgreSQL and I use the product myself, but I wouldn't recommend switching to PostgreSQL based on what you've described of your problem so far.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2014
    Posts
    3
    I guess my issue becomes how to remedy this problem in Access. I just have a number of tables and queries that modify the data and return new tables. After I run through these queries I will often get a message saying that I am approaching the size limit in Access. Once I compact and repair I'm fine. But is there something I should be doing to prevent the DB from ballooning up every time I run these queries?

    As a follow-up, what do I do when I do reach the 2 GB limit? Should I purchase Sequel Server, since the SQL syntax is the same. Or do I go to postgresql or MySQL at that point?

    Thanks.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm going to move this discussion to the Microsoft Access forum. You'll get a lot better advice about the care and feeding of Access databases there than you will get from me...

    I rarely have Access databases "balloon up", but that may be because I habitually avoid whatever feature or process you're using that is causing the growth. It might also be because I have many SQL Servers, so I don't encounter the limit!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you comapcted & repaired the db to remove bloat

    the usual reasons for an Access file to bloat asre:-
    frequent changes to the design of forms, reports. or local caching of data

    if you are using SQL server but not using pass through queries then you will hit performance problems
    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
  •