Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Looking for an opinion ...

    ... about Access!!!

    Access as a database - how big is too big for MS Access?

    In terms of the number of records and/or the physical size of the MDB file, what're some opinions on when it's time to upgrade the back end?

    Let's assume that we've got ballooning under control, so the disk space is actual size. Also, let's assume it's strictly a back end - no code, forms, etc.

    tc

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So lets assume you are using JET
    Provding you are not blowing the physical limits of a JET database are around the 2GB limit - the help system will tell you in detail what the limiits are for your version.

    number of concurent users is 255

    however I suspect that you will hit the implementation limitations of JET long before you hit physical limitations

    JET teneds to run out of grunt around 15..50 users, the file server implementation sarts to choke. your network trolls will start to get peed off with tthe amount of traffic. the users will start to scream at response time (or lack of).

    so its best too upgrade when your numnber of concurrent users starts get above 15...20. However in myview its best to think of the process as a rewrite - you will almost certainly have to rewrite the app to get the best out of using a server. potentially not rewriting the app leads to the worst of both worlds. You can, and should if you think your app should ever use a server, write JET apps that upgrade relatively easily, but I'm guessing thats a little to late for your requirements
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    TC (BTW, what is you're name???),

    Access seems to be fine when the # of records in a table is less than 10,000. When the # gets above 100,000, things really start to drag ... In between you get steadily increasing slowness ... This is mitigated by what the data is, how it is keyed, and indexed, and obviously by how it is queried (joined to other tables etc ...)

    - Mike
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    TC (BTW, what is you're name???)
    Sorry, my name is Todd. C is for my middle name. TC Ace was a screen name from the long lost days of Doom and Castle Wolfenstein, and tc stuck.

    The application is in Acc 97 *gasp* for a few very poor reasons. I already upgraded it to a newer version, but they won't bite (other things would need to be upgraded, everything would need to be revalidated, there would be a cost associated with it, etc.)

    I have one BE in particular that houses the printed documents from the application. Official docs issued by the app are printed to SNP files in the temp directory and then the SNP file is saved in raw binary format into an OLE field. As storing things goes, this is very efficient: 1 kb of raw binary = 1 kb of DB growth. When ever we need to reproduce a specific document, I recreate the file in the temp directory from the raw binary and then open it in snapshot viewer (or IE if they don't have snapshot).

    There's 2721 documents stored in the DB at this time and the physical size is about 59 MB. So, on paper, I'm not close to any limits. But, the application goes into wider use soon, so the rate of growth will increase.

    In an effort to be proactive, I want to suggest that we consider (in this order of preference) either migration, updating or an "archive and remove" function and this thread is meant to gather opinions on other's experiences with ACTUAL limits of usefulness versus the published limits.

    Thanks,
    Todd

    PS: I'd be willing to post the code for the "import to binary" trick in the proposed code library since the bits and pieces mostly came from this forum anyway. It is very useful for storing images without ballooning.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    YIKES!!!! That is the problem with storing docs within access itself (thru the OLE Object) ... You use up the DB fast. If/when this thing goes into general use, you're gonna have 2 things happen: The system will bog down to less than that of a speeding snail or you'll pop access' cork with pounding that 1 gig limit of db size (A97) ... and relatively quickly (depending on how many docs are added).

    Another popular way of having doc tracking is to setup either file/path pointers to the doc (simple text w/ path & filename) or use hyperlinks.
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Fyi

    I'm capable of easily exceeding 15 users in the same Access mdb (using Jet tables) and I believe I can even exceed 255 concurrent users. I write my code using unbound forms and functions to write/retrieve/update/delete to the forms (doesn't anybody else do this???) It's the coding techniques we were taught in school and it will make your mdb fly!
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Another popular way of having doc tracking is to setup either file/path pointers to the doc (simple text w/ path & filename) or use hyperlinks.
    I know, I know. There's a regulatory requirement that requires 100% traceability and a full audit trail. In other words, the program must always know what happened, who did it, when it was done and what they did.

    If I store the files physically somewhere, then somebody could go in and change, add and remove files outside the control of the application. If that happens, then regulatory requirement has not been met.

    So far, it has not been an issue, but I sense issues on the horizon. My first choice will be to try and get them to move it to some version of a SQL Server (I don't care which - any would be better than Jet). From there, using old Acc 97 for the FE won't be such a big deal.

    tc

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quote Originally Posted by pkstormy
    I write my code using unbound forms and functions to write/retrieve/update/delete to the forms (doesn't anybody else do this???)
    YES - I do! I very rarely use bound forms. It's like high speed internet - once you have it, you need it. Once you go recordset, you never go back.

    At first it was due to necessity - the relationships driving the way the data was combined for the particular form was too complex for Access to handle without choking. On top of that, they had VERY SLOW ODBC tables coming in from an outside source. Unbound the form, setup a few add/return procedures and suddenly it's a lot faster and doesn't slow OTHER peolpe down. Plus, it keeps the network gouls at bay because it reduces network traffic.

    Most important to the app in question is the regulatory requirement I mentioned in the another reply to this post. It's really easy to construct an audit record when I have the "new" data on the unbound form and the "original" data in the table.

    tc

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unbound forms

    Thank you Tcace!! I get a little irked when I hear people talk about the limitations of MSAccess and you can't do this...you can't do that...

    Unbound forms in my opinion is definately the route to go (kind of like object oriented programming for Access.) It relieves a lot of stress on the jet tables and makes life a lot easier although it is more code writing. I guess you have to find the point where you can utilitize bound and unbound forms and which is the best technique for that part of the app.

    In college I was taught these techniques and put them to use with MSAccess, but I think too many users find it TOO easy to create a bound form (and then complain that Access is too slow or limited by XX users).

    I keep saying that it's all in the way you write the code (and of course, how you set up the tables and relationships)!!

    It's nice to hear that someone else is using these techniques.
    Last edited by pkstormy; 03-29-06 at 11:36.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tcace
    I know, I know. There's a regulatory requirement that requires 100% traceability and a full audit trail. In other words, the program must always know what happened, who did it, when it was done and what they did.

    If I store the files physically somewhere, then somebody could go in and change, add and remove files outside the control of the application. If that happens, then regulatory requirement has not been met.

    So far, it has not been an issue, but I sense issues on the horizon. My first choice will be to try and get them to move it to some version of a SQL Server (I don't care which - any would be better than Jet). From there, using old Acc 97 for the FE won't be such a big deal.

    tc
    Well since it is a requirement ... The only real option then is to migrate your BE to another db ...
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Jan 2005
    Posts
    20
    I do my databases in this way too. Access is a great tool when used in this way

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Audit

    Tcace, in regards to the audit trail...

    We use SQL Server as our backend and have audit trail tables against our main tables. To make it 100% reliable, we use triggers on SQL Server to write to the audit table should a field value be changed. The nice thing about the audit trail table on SQL Server is we also have 2 fields in that table, one being DateChanged and the other being ChangedBy. The date field defaults to (getdate()) (which also stores the time) and the ChangedBy field defaults to (suser_sname()) so the exact time and username are recorded when a new record is written into this table via the trigger. The other fields are in this table are: FieldName, OldValue, and NewValue. This is all done behind the scenes so the table doesn't have to be linked into the app and there isn't really any code writing to do in the Access app (just writing the trigger against the main table). If you decide to go the route of SQL Server, Lugiment also has an application which will let you read tranlogs for SQL Server (which is our backup for seeing exactly what happened). It's a little pricey though (I think around $1500.00). I'm a little amazed that Microsoft hasn't created an application to read tranlogs on SQL Server.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    In Access 97 you are limited to a 1GB database file. But, you are not limited in the number of database files you can have. I have thousands of report objects and a light-weight frontend. I have a table for database locations (path and filename). I also have a table for report object name and database where it resides. Whenever I need a report I determine where the report object is and import it into the front end along with the associated queries and linked tables. When the front end gets too big then the user gets a new copy of the front end.

    I think you might be able to adapt this structure to your needs.

  14. #14
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I like it! I already use a rather unorthadox method for the front end:

    Each user has a small executable file on their workstation. The exe file checks the registry to make sure the necessary software is installed (Access 97 or Access 97 run time, some version of Excel and some version of Word).

    It then verifies network connection and that it can find the back end.

    Finally, it copies the FE to an archaic filename in the user's temp directory and opens it.

    Benefits of this technique:
    1) It completely handles the multiple office version problem where newer versions of office will overwrite the references, preventing running the app with older version of office.
    2) It essentially hides the MDB from the user - they don't know where it is located on the network.
    3) Updates are a snap - no matter how many users are using the program, nobody is in the FE on the network. Replace the network version and then instruct users to exit and reopen. *Poof* they are updated. Plus, it updates EVERY WORKSTATION by simply updating the network version - IT loves this feature .....

Posting Permissions

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