Results 1 to 6 of 6
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    158

    Large record count

    HI i'm looking to create a backend database that will be growing at a rate of about 250,000 per day.



    What i have in mind is a single table stored on a NAS unit . The table will have about 20 text fields per record
    The front end will be updating the backend table every minute with approx 500 records.
    All the queries / forms (functionality) will be managed via the front end (again MSAccess)

    My concern is any limitations the BE will have due to file size? Someone suggested using MySQL but i don't have any experience with this.

    Again the size of this table will be substantial (250,000 records/day = 5,000,000 per month = 600,000,000 records in 1 year.)
    I can split the table into smaller tables if that will help the cause but again i'm not sure it would?

    The other consideration i have is the hardware requirements to factor into this application. Do i need a 64bit vs 32 bit computer? will this be enough?

    Any thoughts on the design and hardware requirements to make this function would be appreciated.


    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,461
    ive put in 2 million records , but filled the db.
    you will need to either, separate the tables by : alphabetical, or state, or month, etc, to diminish size.
    or
    get SQL server to hold the BE data.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,647
    Where are the 500 records a minute coming from?

    Without knowing the background, I suspect access is not the right tool to manage this exercise and you could use functionality within sql server.

    Certainly the ace db engine which comes free with access won't have the storage capacity, mySql is similar to sql server but is free. So probably time to get some experience.

  4. #4
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    702
    SQL server express can also be downloaded for free and versio 17 comes with a max size of 10 GIG

  5. #5
    Join Date
    Apr 2017
    Posts
    1,169
    Quote Originally Posted by NoellaG View Post
    SQL server express can also be downloaded for free and versio 17 comes with a max size of 10 GIG
    The only downside is impossibility to define and run Jobs (Which probably would do nicely do for automating all data refreshing - it doesn't look as possible anyone is updating those 500 records per minute manually. But there is a workaround using Windows Task Manager of server to run according SQL database procedures on schedule.).
    Last edited by ArviLaanemets; 10-18-2020 at 01:01 PM.

  6. #6
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    702
    or use Powersheel to automate the input, it works seamlessly with SQL
    example:
    https://www.mssqltips.com/sqlservert...om-powershell/

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

Similar Threads

  1. Replies: 4
    Last Post: 01-15-2019, 11:50 AM
  2. Replies: 5
    Last Post: 02-24-2017, 08:09 AM
  3. Record is too large
    By james28 in forum Access
    Replies: 6
    Last Post: 08-04-2014, 06:01 AM
  4. Replies: 8
    Last Post: 11-07-2013, 08:33 AM
  5. Record too large error
    By ragpatil in forum Forms
    Replies: 6
    Last Post: 09-24-2011, 04:19 PM

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 - Senior Forums