How large is large in Access?

[Marcia]

I have been a spreadsheet user since the advent of Lotus 123 although in very simple operations. One office in our agency is handling voluminous tax records and I am helping them transfer and manage their database from Excel to Access because my understanding is that Access will solve the problem of large files and slow processing by Excel. I have only transferred 30% of the files but when I open a table, query or report, Access tells me "system resource exceeded", something that Excel never did although it sometimes hangs in the middle of file loading. My database file is 188 MB. I stopped exporting the rest of the Excel files. Is this a hardware problem? Any advice on how to solve this issue would be most welcome. Just when I am enjoying learning and applying Access, now this. Thank you.

[June7]

188MB should not be an issue. Review https://access-programmers.co.uk/for...d.php?t=306662

Run Compact & Repair periodically, frequently during development, certainly after every design edit.

Have you Googled that topic?

[Marcia]

Thank you June. I googled it many are blaming Access 2013. Apparently they didn't have this issued in Access 2010.

[ArviLaanemets]

Access database can be up to 2 GB in size. You have imported ~1/3 f data from Excel - so total size of imported data will be ~1 GB (NB! it is only imported data - for sure you have to add some additional info). It looks like there must not be any problems currently, but there also is not much room for growth. You have to consider SQL Server backend and Access front-end - over 500 TB for full version, 10 GB for SQL Server Express (freeware, but with some severe limitations, like inability to define jobs). Probably there will be no issues with import to SQL Server database.

Another thing is, Excel and Access are based on very different logic. Almost for sure any Excel table you have doesn't have structure proper for use in relational database (neither in Access or SQL Server) directly. Usually it means, you have to split info from one Excel table between several relational database tables, add primary and foreign keys, etc. So you import data, create empty Access/SQL Server tables, read imported data into Access/SQL Server tables, and then delete imported tables.

[CJ_London]

my research indicates it could be any number of factors from the way your code works to the version of access, version of windows and patches installed.

examples include

holding too much data in memory

• for example table 'width': importing multiple memo/long text fields from excel - so check your files and also datatypes in the destination tables
• table 'height': appending/updating too many rows - i.e. append/update/delete queries that can generate a message which ask you to commit without giving you a number. So are you importing large numbers of blank rows which you are then deleting?
• combination of both
• not closing (large) recordsets when done with. Depends on your code. An example might include having multiple forms/subforms with 'whole table' recordsources

reaching 2Gb limit by creating multiple temp tables in main app before deleting then compacting

some windows updates can cause issues and are normally resolved pretty quickly - but best to check you have the latest windows and office updates

[Marcia]

Thank you Arvi and Ajax. I will have to research on the Sql Server backend and Access frontend and Sql Server Express.

[CJ_London]

not sure how moving to a sql server BE will solve your problem - the issue is with memory