2GB Limit

[Pharaoh]

Hi,

I am having some trouble with the 2GB database limit. I am working on some data from 2005-2010, which is the smallest time period I can have. I am importing the data from a text files which is only 1.06GB, however when I import this file into access it scales up to be over 2GB and so some of my data is not imported.

I suppose the first question is why the size of the data almost doubles when it is imported into access?

The second question is, is there a way around it? For example; is there a way I can compress the data in the database?

Regards

Pharaoh

 

[DCrake]

Are you importing into a new table or an existing one? I think the latter. If so, you need to set up a table first with the correct field definitions and field sizes. Access tends to allow 255 chars for all text fields even if they only hold 1 char.

Also what type of text file are you importing? Fixed,Delimited,csv?

 

[Pharaoh]

Hi,

Thanks for that DCrake. Actually I have tried to import to both a new table and an existing one and have the same problem with both (the existing table only has the field headings).

The file is Delimited and is separated by "|" (that's the bar next to the shift key).

From what you have said, if i limit each field to show only 10 characters (which is the maximum number of characters in any of the fields) then it should help to keep the size of the database closer to the text file size?

Regards

Pharaoh

 

[gemma-the-husky]

for one reason, a disk file is just a file

an access table comes with indexes and other overhead. so the size isnt always directly comparable


Why not split your import file into 3 or 4 smaller ones, and bring them in a bit at a time

access will be more effcient, as it will not store empty strings etc

that way you can delete the temp files, after you have processed each one

============
if its that big, maybe you need SQL Server, not Jet

 

[Pharaoh]

Hi Dave,

Thanks for that. I was thinking of splitting it but the data will need to be viewd and accessed by users who are not familiar with MS Access and so I do not want to confuse them with linking tables together. The trouble is the reports that are run with this data may need to be run by these users for the full 5 years of data.

We are currently preparing to move to SQL but it didn't seem urgent as this is only one file of about 20 that this happens with. I never expected the files size to mirror the database size but i didn't think it would double.

I will try to split the file and set up some queries where users will only have to select say a date range and will not have to fiddle with the query itself. Hopefully this will resolve the issue.

Thanks for your help.

Regards

Pharaoh

 

[DCrake]

What you need to do is to create 5 back ends one for each year. Then import the data in to the respective mdb's.

Then in you front end you link these tables and suffix/prefix them with the year. Then finally create a union query to join all the tables together.

The uion query could be create on the fly if you manage that technically.

The advantage of this is that each mdb will be small in size restrospectively

 

[Pharaoh]

Hi David,

That's a brilliant idea. I'll give it a go and let you know how it goes.

Thanks

Pharaoh

 

[SOS]

Access tends to allow 255 chars for all text fields even if they only hold 1 char.

It doesn't matter about that because Access doesn't PAD fields. In other words, even if you set up a field to allow 255 characters and it only uses 1, there is no space lost or used for the 254 it didn't use.

 

[Pharaoh]

Hi SOS,

Thanks for that clarification

Regards

Pharaoh

 

[AC5FF]

Have you tried a compact/repair after your import?

 

[datAdrenaline]

The idea of separating each year then prefixing and union queries may not be the best thing. How many tables are in your destination? ... if more than one, then have a database for each one, then in the Access FE, link to each back end database for each table. That way your limitation is 2GB per table.

If that is not viable, then split your table into more pieces, but have the primary key in each resultant table so you can join the data.

For example:

Your text file fields:

PrimaryKey
Field1
Field2
Field3
Field4
Field5
Field6
Field7
Field8
Field9

Then ... in database one, import into a table with a structure like this:

PrimaryKey
Field1
Field2
Field3
Field4

then in another database import into a table structure like this:

PrimaryKey
Field5
Field6
Field7
Field8
Field9

Then in your FE, you can link to both tables and create a query that looks like this:

SELECT Table_PartA.*, Table_PartB.*
FROM Table_PartA
INNER JOIN Table_PartB ON Table_PartA.PrimaryKey = Table_PartB.PrimaryKey

....

Hope that helps! ..

 

[datAdrenaline]

Also, you MAY want to not even import the data! ... you can create a query (or a linked table) the reads directly from the CSV.

The SQL statement for a query object would look like this:

SELECT *
FROM [Text;FMT=Delimited;HDR=YES;CharacterSet=437;DATABASE=C:\FullPath].[FileName.CSV] As MyTextFile

But ... be carefule because Access will modify the way the statement looks (the change depends on the version of Access) when you open it up again, but if you don't change it --- there is not a problem since the query that will run is the optimized version that is saved) but as soon as you change any part of the command, you have to change it back to the syntax above.

If the direct query against the CSV is not going to work (which is may not since your delimitor is a (|) instead of a (,) you might want to simply create a linked table that points to your CSV ...

With either of these options, you don't have to import your data. All you data comes right from the CSV in order to create your reports that are based on this data.

 

[gemma-the-husky]

i didnt mean 5 different tables

what i meant was that access may not be able to store the whole thing as a temporary table

but if you split it into 4 or 5 tables, brought them in one at a time, and integrated them one at a time into your database, you may be able to do this.

maybe you couldnt import the whole thing that way - but that was the idea.

-============
the considertaion underlying this, of course, is that the single file you are importing may not be nomalised - so once it is in access, you may need to bring some parts into 1 table, and others into another table.

 

[Pharaoh]

Hi, Thanks for your responses everyone.

At the moment the way it's set up is that I have around 20 tables which sit in around 6 databases. Then I have a "Master Data" database that links all the others into it so all the data can be accessed in one database. Obviously some of my tables are small enough so that I have 5+ tables in 2 of the databases.

However the table in question is 1.2GB itself in file form (Which becomes more than 2GB when imported).

I think the best way forward for me is to split the data into 2 seperate files (2005-2007 & 2008-2010), then do what DatAdreneline suggested which is to split it further and keep the primary key. The reason is because fresh data extracts are done monthly to update the database with new data / changes to old data and so the databases can become over GB very quickly.

Thanks everyone again for your help with this.

Regards

Pharaoh

 

[datAdrenaline]

So ... now the real question ... with so much data, why not simply install SQL Server Express? Its free and can handle 4GB, and since you are already using Linked tables, your FE should not have to change too much. If anything it would become simpler since you would not need the queries to JOIN everything.

 

[Pharaoh]

The honest answer to that is I have only just learned access. My collegues and I are planning to move to SQL Server in the near furture.

Thanks

Pharaoh

 

[gemma-the-husky]

so in that case .... how do you get 2GB of data

how are you managing it at present?

 

[Pharaoh]

At present, the computer program we use stores data and changes to data in seperate files according to area of work. We download extracts from the server where the data is stored. The downloads are divided up into sections according to area of work (as they are stored).

From there we use Ultraedit to save the file as a .txt file. Some of the data, for example the Audit files can already be almost 2GB in size. Therefore we decided to only extract the last 3 years of data (as any older data could be obtained from the older extracts). However even then the audit .txt files can be 1GB+ which, when imported into access, can exceed the 2GB limit as is the case with the file discussed in this thread.

Anyway, we create seperate databases to hold certain types of extract, so for example; all the payment files for certain areas of work can fit into one database. We usually end up with 25 tables spread across 10 databases, which 5 of those databases having only one table as the files are so big.

Moving to SQL Server is clearly the best option to solve the size issues, the only thing preventing that currently is that my collegues and I are new to the job and so we are still learning access and have been asked to master this before moving on to SQL Server.

Regards

Pharaoh