advertisement
Deals | News | Forums

The Forum is sponsored by 
 

AAPL stock: $102.47 ( +2.71 )

*Cached every 60 seconds. For live updating, Click Here

You are currently viewing the Tips and Deals forum
Working around Access' 2GB limit
Date: March 29, 2006 08:25AM
This particular database contains three tables.

The first is a combination of data imported from five CSV files, each representing a quarter-year. So, we're due for a new one, shortly.

The others are the results of two different make-table queries. These tables are used by Excel in two different PivotTables, which is much faster than drawing the information from the first, gigantic, table.

The 2GB limit is reached.

The workaround I've come up with (but don't like) is making those five CSV files into linked tables.

Now, the database is really small, but also really slow. I've forfeited the indexing that was made possible by my original table, so the daily 20-minute data crunching session is now a 2-hour daily data-crunching session.

I guess it's not really a workaround I'm seeking, as I've already got that; set it up to run overnight.

But is my only option for avoiding the 2GB wall to structure the database in such a way that I can no longer use many time-saving features?

Oh, and I can't use FileMaker. :-(

GtDS



---
Use Excel? Can I interest you in saving some time with Excel? Listifier.com!
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Posted by: mattkime
Date: March 29, 2006 09:21AM
you have 2GB of straight data?

PLEASE move to a real database!

even MySQL!





VTPKL it!
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Date: March 29, 2006 09:23AM
mattkime Wrote:
-------------------------------------------------------
> you have 2GB of straight data?
>
> PLEASE move to a real database!
>
> even MySQL!

That might be under the same category as trying to use FileMaker.

G
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Posted by: TheTominator
Date: March 29, 2006 09:25AM
Have you optimized the data types for each field in the big table to reduce the storage requirements? I don't know much about Access but databases like MySQL let you choose what type of integer, string, and so on to constrain the storage size.

Options:  Reply • Quote
Re: Working around Access' 2GB limit
Date: March 29, 2006 09:28AM
TheTominator Wrote:
-------------------------------------------------------
> Have you optimized the data types for each field
> in the big table to reduce the storage
> requirements? I don't know much about Access but
> databases like MySQL let you choose what type of
> integer, string, and so on to constrain the
> storage size.

Yup.

I think it's the indexes that are killing me. Am checking, now...
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Posted by: TheTominator
Date: March 29, 2006 09:33AM
Another technique you can use is to create set of related tables out of a single table by identifying fields that can be excised from the big table and spun off into their own tables.

For example you might have a Notes field. Not every entry has a Note filled in. Notes are of varying lengths. If, on average, the data in the note (or Description) field is longer than an integer (considering storage requirements), then you can create a separate table of Notes with a NoteID field (or Descriptions with a DescriptionID field). Instead of the Note in the original table, make it a NoteID.

This makes the table more work to import but it reduces the net size of your table.

I am assuming that the 2GB size limit is for a table and not a database.



Edited 1 time(s). Last edit at 03/29/2006 09:34AM by TheTominator.
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Posted by: mattkime
Date: March 29, 2006 09:48AM
>>That might be under the same category as trying to use FileMaker.

I think you need to convince the client that they're using the wrong tool for the job - assuming the database is properly optimized.

What is taking up all the space?





VTPKL it!
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Date: March 29, 2006 09:54AM
mattkime Wrote:
-------------------------------------------------------
> >>That might be under the same category as
> trying to use FileMaker.
>
> I think you need to convince the client that
> they're using the wrong tool for the job -
> assuming the database is properly optimized.

Not feasible, in this particular situation.

> What is taking up all the space?

I believe it's the indexes.


Options:  Reply • Quote
Re: Working around Access' 2GB limit
Date: March 29, 2006 09:57AM
TheTominator Wrote:
-------------------------------------------------------
> Another technique you can use is to create set of
> related tables out of a single table by
> identifying fields that can be excised from the
> big table and spun off into their own tables.

Agreed, all around.

> I am assuming that the 2GB size limit is for a
> table and not a database.

Nope; it's the limit for the .mdb file.
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Posted by: mattkime
Date: March 29, 2006 10:23AM
>>Not feasible, in this particular situation.

Then they want what they can't have.

$400 for a Mac Mini with MySQL on it.

How much is your time worth?





VTPKL it!
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Date: March 29, 2006 10:29AM
mattkime Wrote:
-------------------------------------------------------
> >>Not feasible, in this particular
> situation.
>
> Then they want what they can't have.
>
> $400 for a Mac Mini with MySQL on it.
>
> How much is your time worth?

You don't need to convince me there may be better software for this task.

What's the MySQL equivalent of doing a scripted VBA import in Access? For instance, I have a script that loops through every quarter from 2005_01 to current, and imports one file for each quarter. How would that by done with MySQL?
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Posted by: mattkime
Date: March 29, 2006 10:32AM
>>How would that by done with MySQL?

Command line with the mysql client.

OR

You can use MySQL as a data source in access





VTPKL it!
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Posted by: mattkime
Date: March 29, 2006 10:35AM
>>You don't need to convince me there may be better software for this task.

ahh....one of those clients ;)





VTPKL it!
Options:  Reply • Quote
Re: Working around Access' 2GB limit
Posted by: maurycy
Date: March 29, 2006 12:10PM
MS SQL Server 2005 Express edition has database limit of 4GB. This will double your available db space.

Maurycy
Options:  Reply • Quote
Sorry, only registered users may post in this forum.

Click here to login