Importing huge files blocks Access

[dskysmine]

Hello everyone,


I am using Access for a short time now but I noticed that when I am importing large amounts of data (csv files with over 2 million records... around 200Mb) access blocks and never unfreezes again.
The funniest thing is that if I see the importing "loading" bar going and wait 1 or 2 minutes after it is finished, if I terminate Access with Task Manager and I open the Database again, the file was successfully imported. I am glad that the data is imported... but I am not going to be the only person using the database in the future and I think it is quite dumb for me to put in the process documentation that whenever they want to import the data dump they have to terminate the process...
Has anyone experienced this? Does anybody have any good insights into why this is happening?
The database right now is about 2GB. It has 3 Tables with over 2Million Records.
then it creates reports over filters that are used into the Datadump.
I am using Access 2007 by the way, in a XP Windows (if that helps).
Thanks!

[JoeM]

2 GB is the maximum size of an Access 2007 database (http://office.microsoft.com/en-us/ac...010030739.aspx), so you may be bumping into the upper limits. Did you try Compacting and Repairing your database to see if it is bloated at all, and it reduces the size some? It is something you should do fairly regularly.

If it looks like even after compacting and repairing, the 2 GB limit is going to be a factor, you may need to look into storing your data somewhere else, such as a SQL database. You can still use Access as a front-end for data stored in a SQL database. I have also seen some people get a little creative, and split there data into multiple back-ends, where they store different data tables in different places, and then just have a front-end database that links to all those tables.