(1) Can someone tell me the max file size of Access file?

(2) Also, when I open a 2GB Access file, does it uses 2 GB of memory?
The reason I asked is that when I opened such file, I see the
available memory in the Tasks Manager window went down to zero
gradually.

(3) I saw some posts that suggested using table link to deal with the
size issue, but would the performance (speed) go down when running
queries using tables from two different Access file?

- Grasshopper -

"Grasshopper" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> (1) Can someone tell me the max file size of Access file?

2 GB

> (2) Also, when I open a 2GB Access file, does it uses 2 GB of memory?
> The reason I asked is that when I opened such file, I see the
> available memory in the Tasks Manager window went down to zero
> gradually.

It will eventually use at least 2 GB of memory, but not necessarily RAM.
Windows allocates memory from a pagefile (usually equal to 1.5 times the RAM
on the machine)

> (3) I saw some posts that suggested using table link to deal with the
> size issue, but would the performance (speed) go down when running
> queries using tables from two different Access file?

I've never noticed a performance hit. The biggest database that I've ever
seen was a 30 GB file in Access 2.0 which had a 1 GB limit. There were about
78 linked tables from different databases all linked to a single front-end.
The tables contained OLE fields with images in them. It had good
performance.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thank you so much Arvin. Your answer to Q3 was particularly useful.

Do you know if i run quries by linking 4 tables (and assuming each
table has exactly 1 GB of data), what it the maximum memory that it
will use during the run time?

Also, when I designed the database, the size issue was not there, so
the design of the table was not very efficient. For example, a max of
2 characters field would take 50 characters space (as by the
default). Given that I have two table with 4 millions records, this
should "eat up" a lot of space. Is there a way to calculate the size
of the database based on the field size allocated and the number of
records?

- Grasshopper -

On 26 Mar 2007 20:22:22 -0700, "Grasshopper" <(E-Mail Removed)> wrote:

>Also, when I designed the database, the size issue was not there, so
>the design of the table was not very efficient. For example, a max of
>2 characters field would take 50 characters space (as by the
>default).

Ummm... No. Access does NOT store trailing blanks. A Text field is really a
"varchar" - if there are two bytes used, that's all that's stored on disk. The
50 bytes is an upper limit.

>Given that I have two table with 4 millions records, this
>should "eat up" a lot of space. Is there a way to calculate the size
>of the database based on the field size allocated and the number of
>records?

Not easily, given that trailing blanks aren't stored; you need to factor in
some overhead for each record, system tables, the size of indexes, etc. etc.

Your concern about memory is probably not relevant, actually. Windows memory
management is pretty sophisticated; you will certainly have a paging file,
which will be used as an extension to installed RAM. A 512Mbyte machine can
easily run a program using over a gig of "memory" by swapping memory out to
the page file - it will, of course, be slower as a result, but it *will* run.

John W. Vinson [MVP]

Hi John,

Thank you for your answer. Yes, you are correct on the trailing
blanks. Before I read your reply I changed the field size on the
table and compacted it, the file size stays the same.

Regarding the memory issue, I opened a 2GB file and the 2BG memory got
consumed slowly until the available physical memory become zero.
Despite that there are still cache 1GB of memory, the query cannot be
ran any more.

- Grasshopper -

By the way, if anyone know the answer to the folloing question, please
tell me. Thanks.

====================================================
Do you know if i run quries by linking 4 tables (and assuming each
table has exactly 1 GB of data), what it the maximum memory that it
will use during the run time?
====================================================

- Grasshopper -

Grasshopper, the algorithms Access uses are proprietry (not published.)

Even if they were published, there would not be a simple answer, since it
depends on your version of Access (JET), your version of Windows, and your
configuration (swapfiles, location of data, Unicode compression and language
settings, etc.) It is not even practical to determine the physical size of a
table.

If you really have a 2GB database, get your information out of Access and
into another database quick before you lose it. You won't be able to
red-line for very long before it blows up.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Grasshopper" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> By the way, if anyone know the answer to the folloing question, please
> tell me. Thanks.
>
> ====================================================
> Do you know if i run quries by linking 4 tables (and assuming each
> table has exactly 1 GB of data), what it the maximum memory that it
> will use during the run time?
> ====================================================
>
> - Grasshopper -

Thank you Allen. I will be careful with the file. Last time, I tried
to deleted two 2GB files on my computer and unexpectedly (and still
unknown) instead of these two got deleted, the whole folder got
deleted. It cannot be undo and the folder cannot be found in the
recycle bin. This has nothing to do with the fact that they are Access
files, but probably merely on memory issue (I do not know for sure).
My laptop has 1.5GB of memory.

Now my querstion is, if any body know. Can a file with file size
larger than the REM on the PC be deleted and then restored?

I know this has nothing to do with Access, but it is a spin off
question. :-)

- Grasshopper -

Arvin

you've never noticed a performance hit from partitioning data into
multiple databases?

I've had awful performance / stability with 25mb of data

anything even CLOSE to this size should be an Access Data Project
2007-- which has a 4gb limit in SQL 2005 Express




On Mar 26, 7:43 pm, "Arvin Meyer [MVP]" <a...@m.com> wrote:
> "Grasshopper" <excelmodel...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > (1) Can someone tell me the max file size of Access file?
>
> 2 GB
>
> > (2) Also, when I open a 2GB Access file, does it uses 2 GB of memory?
> > The reason I asked is that when I opened such file, I see the
> > available memory in the Tasks Manager window went down to zero
> > gradually.
>
> It will eventually use at least 2 GB of memory, but not necessarily RAM.
> Windows allocates memory from a pagefile (usually equal to 1.5 times the RAM
> on the machine)
>
> > (3) I saw some posts that suggested using table link to deal with the
> > size issue, but would the performance (speed) go down when running
> > queries using tables from two different Access file?
>
> I've never noticed a performance hit. The biggest database that I've ever
> seen was a 30 GB file in Access 2.0 which had a 1 GB limit. There were about
> 78 linked tables from different databases all linked to a single front-end.
> The tables contained OLE fields with images in them. It had good
> performance.
> --
> Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

"Grasshopper" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> Regarding the memory issue, I opened a 2GB file and the 2BG memory
> got consumed slowly until the available physical memory become
> zero. Despite that there are still cache 1GB of memory, the query
> cannot be ran any more.

That's probably caused by an Access error condition. The whole data
file should not be loaded into RAM, because that simply isn't the
way Access works. It only loads what is needed, as needed. Since you
can't look at 2GBs of data onscreen (nor process it all at once),
there would be no utility in loading the whole thing.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/