How to handle large database more then 2GB in access

[adnancanada]

I am working on large database on Access 2013. I pull data for the whole year but data is getting more than 2 GB. when I run queries it behave very odd and freeze my computer.

Is there way to handle data , I have compressed data but still more than 2 Gb. Please help me out.

Thanks.

[orange]

Do you do
-regular Compact and Repair
-backups??

[CJ_London]

In addition to Orange's comments:

is there a lot of duplicate data? if so, normalise

are you using temp tables? - if so, put temp tables in another back end

are you over indexing? if so, reduce number of indexes to those required for criteria/relationships/sorting. Also remove indexes where the range of fields is very small (e.g. yes/no fields, day of week) since they will be ineffective

consider splitting your tables across several backends

If none of the above works for you, then your only way forward is to upgrade your backend to sql server (express is free) or similar

[NTC]

often file size expansion to 2G is caused by a corruption issue; and so in a copy one should do the compact & repair

as the user interface objects can cause a lot of a file's size, typically more than the data itself - - the advice of Ajax to split the db should be your first step.

this will put the data itself into a separate back end file. If this file is at or approaching 2G then you have outgrown Access and will need to consider moving the SQL Express to hold the data tables.

even though technically under the 2G max one will see performance issues attempting to work with data approaching that size which is to some degree going to be due to the horse power of the PC one is using and so one will want to be using hardware with as much RAM as possible - definitely 8G or more.

[adnancanada]

Do you do
-regular Compact and Repair
-backups??

I don compact and repair.

[adnancanada]

In addition to Orange's comments:

is there a lot of duplicate data? if so, normalise

are you using temp tables? - if so, put temp tables in another back end

are you over indexing? if so, reduce number of indexes to those required for criteria/relationships/sorting. Also remove indexes where the range of fields is very small (e.g. yes/no fields, day of week) since they will be ineffective

consider splitting your tables across several backends

If none of the above works for you, then your only way forward is to upgrade your backend to sql server (express is free) or similar

Actually I m working on link tables which is actually views. Can I do index on views ?