solution for database larger than 2 G

[Souris]

I know that MS Access has size limitation for 2 G.

I would like to know are there any solution for size bigger than 2 G.

Split the database in 2?
Does it mean that i export the old data to a database and change the query
to union 2 tables from different database?

Change to SQL server?
Does SQL server have any limitation for the size?


Archived databse?
Does it mean that I can have myDatabase2008, MyDatabase2007?
It will have issue that generate report for a date range between 2007 and
2008?

any other solutions or product to do this?

Your information is great appreciated,

 

[Maurice]

SQL Server Express edition has a limit of 4Gb. After that SQL versions are
scalable up to terrabytes but the price is no longer free and you'd have to
consider licensing.

I think that if your Access db exceeds 2 G's Access no longer should
function as a backend. Splitting in different mdb's can be an option but SQL
should be the next logical step.

hth

 

[Souris]

Thanks for helping,

Arer there any technical to plit mdb database?

Can I just export old data to another mdb and the forms and reports use
query to get data which include 2 mdb files?

If it is, are there ajny performance issues to split 2 mdb?

Thanks again,

 

[Dale Fye]

Personally, I would recommend moving to SQL Server (Express version is free
and has a limit of 4GB) if your db is more than 2G.

However, you could just create several backend databases and split your
tables up between these backends. Then, link the tables from the multiple
backend data files to the front end.

The problem with this large of an Access database is the processing time and
load on the network (if used in a multi-user environment). Remember, with
Access, all processing is done on the local machine, so if you have a query
that will filter 1 record out of a million, Access brings 1,000,000 records
back across the network then discards the 999,999 that you don't need. With
SQL Server, if done properly, the processing is handled on the server, so you
send a request to the server, it selects the one record you want and sends it
back across the network (significantly quicker).

HTH
Dale

 

[Albert D. Kallal]

Thanks for helping,

Arer there any technical to plit mdb database?

Can I just export old data to another mdb and the forms and reports use
query to get data which include 2 mdb files?

I'm not really sure that you should attempt to split this data out anyway.
If you reached the limits of the size of database, then your database must
be dealing with probably 50 to 80 million records anyway, and your be on the
limits of MS access anyway.

I can imagine that you really know are tied reached such a large record size
in MS access.

If you split your database, and do a compact and repair..what is the size of
your back end database, and how many records do you have? at 2gb, you going
to be likely in the 80 million + range in terms of records. It is entirely
possible that you've done something really crazy like storing documents or
graphics pictures inside of the database, but then that's just a really
rotten desing, and something that you should advoid with any database.


I explain the whys and how's of splitting a database here:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

It's not clear if the size of your database is the result of your forms, or
applications side, or data side. A typical in access application with about
160 forms, 100 reoports, and say 30,000 lines of VBA code is going to be
less then 10 megs in size. That is rather tiny.

It's not clear if you're surpassing the limits of the 2GB data by your code,
or just an absolutely insane number of records. If your database is not full
of huge numbers of records, then I would suggest that you look into why your
database size is so large....

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)

 

[Souris]

Thanks millions for helping,

I just started a new project and would like to know if it happens to reach
maximum limit Access are there any solutions.

The database only stores numeric information and a few foreign tables with
text information.

Yes, you are right for this kind database. I do not need worry too much.

If there is any issue then I should convert my application to SQL server.

Thanks again for helping,