locked
What is Access DB size limit and Time Format Issue RRS feed

  • Question

  • Please help, I am having trouble importing a .txt file (900 MB contains 12 columns contain number, text, date, address, time & etc) to MS Access 2003. I think there are at least 8-10 millions records in the .txt file. My system (Intel Dual P 2.8GHz 4GB memory) was running extreamly slow while trying to import the data and it also gave me a warning that "system is out of disk space", but I have at least 500 GB free space. At the end, the system finally gave up, I received an error message "invalid argument " from Access right before Access DB reached alittle over 2GB. However, earlier, I was able to import a similar .txt file which had 5 millions records without any problem. So is there a size limit on the MS Access db? If there is, what should I do? Should I use Access to proccess such large file? Any suggestion?

    I also ran into a problem with the time format, the time in text file was in 24hr format "hh:nn:ss", when I imported it to Access it changed to the long date format (21:00:00 became 09:00:00 PM) I reformated the time field under "hh:nn:ss" and it appeared all the time were converted to the proper format, but when I clicked on some time entries in table, some of them still showed long date format. Any idea how to convert time properly? Because of this, some query did no return the correct result based on the time.

    Any assistance would be greatly appreciated.  Thanks!

     

    Tuesday, March 1, 2011 4:24 AM

Answers

  • Access databases are limited to 2GByte. It's quite possible that with the overhead of indexes, etc. that your 10 million record table will exceed this limit. At the best, you'll need to Compact and Repair the database - or create a brand new empty one - prior to attempting the import; and I'd suggest using External Data... Link to link to the file and running an Append query to populate the table. This is pushing the limits of what Access is designed to do!

    A Date/Time value is actually stored as a Double Float number, a count of days and fractions of a day (times) since midnight, December 30, 1899. 21:00 or 9:00PM are both stored identically (as 0.875); the Format property of the table field or of a form or report textbox will control how that 0.875 is displayed, but not affect the actual stored data. It will go to Long Time (or Long Date, #12/30/1899 09:00:00PM#) format when you edit the value. This can be controlled using your computer's Control Panel (Regional... Date/Time) settings, but it really shouldn't matter much - you can enter any reasonable date/time format and Access will interpret it and store it correctly.

     


    John W. Vinson/MVP
    • Marked as answer by Bruce Song Tuesday, March 8, 2011 9:55 AM
    Tuesday, March 1, 2011 5:18 AM
  • Hi Icymoon
     
    When Access reaches the 2GByte limit in the database, it starts to behave unpredictable. It's the limit of an MDB file. If you reach it, you are beyond of what you should do in Access.
    If you try to import a 500 MByte text file it depends on the table structure if it fits in or not.
    First of all: use a new empty database, so you won't have lost pages yet.
    Now create a table without any indices that contains the structure of your text file. Ensure that you allow NULL values, if some datafields are empty.
     
    Then try to import the text file into this table. Maybe it runs faster when you do it by VBA code instead of the import wizard to not loose additional resources.
     
    I for myself would try to use a SQL Server database instead of an MDB. The express editions nowaday allow 4GByte database files IIRC, this should be enough for your 500MByte text file. You then can link this table to your MDB and access it the same way you would access an internal table. Maybe you should add a IDENTITY column (to use later als Primary Key) to the table and a TIMESTAMP column (for performance). After the import you can start to create your keys (Primary Key first). It could be your keys use to much space.
     
    Henry
     
     

    Please help, I am having trouble importing a .txt file (900 MB contains 12 columns contain number, text, date, address, time & etc) to MS Access 2003. I think there are at least 8-10 millions records in the .txt file. My system (Intel Dual P 2.8GHz 4GB memory) was running extreamly slow while trying to import the data and it also gave me a warning that "system is out of disk space", but I have at least 500 GB free space. At the end, the system finally gave up, I received an error message "invalid argument " from Access right before Access DB reached alittle over 2GB. However, earlier, I was able to import a similar .txt file which had 5 millions records without any problem. So is there a size limit on the MS Access db? If there is, what should I do? Should I use Access to proccess such large file? Any suggestion?

    I also ran into a problem with the time format, the time in text file was in 24hr format "hh:nn:ss", when I imported it to Access it changed to the long date format (21:00:00 became 09:00:00 PM) I reformated the time field under "hh:nn:ss" and it appeared all the time were converted to the proper format, but when I clicked on some time entries in table, some of them still showed long date format. Any idea how to convert time properly? Because of this, some query did no return the correct result based on the time.

    Any assistance would be greatly appreciated.  Thanks!

     

    • Marked as answer by Bruce Song Tuesday, March 8, 2011 9:55 AM
    Tuesday, March 1, 2011 8:49 AM

All replies

  • Access databases are limited to 2GByte. It's quite possible that with the overhead of indexes, etc. that your 10 million record table will exceed this limit. At the best, you'll need to Compact and Repair the database - or create a brand new empty one - prior to attempting the import; and I'd suggest using External Data... Link to link to the file and running an Append query to populate the table. This is pushing the limits of what Access is designed to do!

    A Date/Time value is actually stored as a Double Float number, a count of days and fractions of a day (times) since midnight, December 30, 1899. 21:00 or 9:00PM are both stored identically (as 0.875); the Format property of the table field or of a form or report textbox will control how that 0.875 is displayed, but not affect the actual stored data. It will go to Long Time (or Long Date, #12/30/1899 09:00:00PM#) format when you edit the value. This can be controlled using your computer's Control Panel (Regional... Date/Time) settings, but it really shouldn't matter much - you can enter any reasonable date/time format and Access will interpret it and store it correctly.

     


    John W. Vinson/MVP
    • Marked as answer by Bruce Song Tuesday, March 8, 2011 9:55 AM
    Tuesday, March 1, 2011 5:18 AM
  • Hi Icymoon
     
    When Access reaches the 2GByte limit in the database, it starts to behave unpredictable. It's the limit of an MDB file. If you reach it, you are beyond of what you should do in Access.
    If you try to import a 500 MByte text file it depends on the table structure if it fits in or not.
    First of all: use a new empty database, so you won't have lost pages yet.
    Now create a table without any indices that contains the structure of your text file. Ensure that you allow NULL values, if some datafields are empty.
     
    Then try to import the text file into this table. Maybe it runs faster when you do it by VBA code instead of the import wizard to not loose additional resources.
     
    I for myself would try to use a SQL Server database instead of an MDB. The express editions nowaday allow 4GByte database files IIRC, this should be enough for your 500MByte text file. You then can link this table to your MDB and access it the same way you would access an internal table. Maybe you should add a IDENTITY column (to use later als Primary Key) to the table and a TIMESTAMP column (for performance). After the import you can start to create your keys (Primary Key first). It could be your keys use to much space.
     
    Henry
     
     

    Please help, I am having trouble importing a .txt file (900 MB contains 12 columns contain number, text, date, address, time & etc) to MS Access 2003. I think there are at least 8-10 millions records in the .txt file. My system (Intel Dual P 2.8GHz 4GB memory) was running extreamly slow while trying to import the data and it also gave me a warning that "system is out of disk space", but I have at least 500 GB free space. At the end, the system finally gave up, I received an error message "invalid argument " from Access right before Access DB reached alittle over 2GB. However, earlier, I was able to import a similar .txt file which had 5 millions records without any problem. So is there a size limit on the MS Access db? If there is, what should I do? Should I use Access to proccess such large file? Any suggestion?

    I also ran into a problem with the time format, the time in text file was in 24hr format "hh:nn:ss", when I imported it to Access it changed to the long date format (21:00:00 became 09:00:00 PM) I reformated the time field under "hh:nn:ss" and it appeared all the time were converted to the proper format, but when I clicked on some time entries in table, some of them still showed long date format. Any idea how to convert time properly? Because of this, some query did no return the correct result based on the time.

    Any assistance would be greatly appreciated.  Thanks!

     

    • Marked as answer by Bruce Song Tuesday, March 8, 2011 9:55 AM
    Tuesday, March 1, 2011 8:49 AM