2 Gig max

[gmbrant]

I have read that Access can only support up to about 2 gig. After that it
begins to act up. My question, based on this information, is ...

If all of your tables are linked to excel files, is the data in the linked
excel files eating up part of that 2 gig as if they were tables within Access?

 

[gmbrant]

Thanks - Chris ...

I have just started a new position and am already encountering the
following...

I have an .mdb that is acting up, but only shows about 1 1/2 gig. At times
the tables just disappear and when you close and re-open, there they are
again.

Or I am unable to re-open the database after having just worked in it and
closing it.

I am able to re-open it only ... if I open Access first and then click on
open and browse for the .mdb file. However, clicking straight on the .mdb
file will not open it. The .ldb file won't go away after the .mdb has been
closed and I can't delete it either. I have been researching and have found
that maybe it is being caused by permissions to the folder on the network
server.

My concern is that the .mdb will (or is becoming) become corrupted.

I will check on a few of the other suggestions tomorrow when I get back into
the office and let you know if any of them were the right area to look in.

Again, thank you for your response.

 

[a a r o n . k e m p f]

Access doesn't work well once you start getting a half dozen users or
about 50mb of data.

anything larger than that means that you should move to SQL Server.
Because most people cannot forecast the growth in a system-- I claim
that it is always best to 'do it nice or do it twice'

 

[a a r o n . k e m p f]

yah.. Access always corrupts with that much data

go and look for chinese characters in _ANY_ of your tables.. if you
see them then you need to move to SQL Server

honestly.

Thanks - Chris ...

I have just started a new position and am already encountering the
following...

I have an .mdb that is acting up, but only shows about 1 1/2 gig.  At times
the tables just disappear and when you close and re-open, there they are
again.  

Or I am unable to re-open the database after having just worked in it and
closing it.  

I am able to re-open it only ...  if I open Access first and then clickon
open and browse for the .mdb file.  However, clicking straight on the .mdb
file will not open it.  The .ldb file won't go away after the .mdb has been
closed and I can't delete it either.  I have been researching and have found
that maybe it is being caused by permissions to the folder on the network
server.  

My concern is that the .mdb will (or is becoming) become corrupted.

I will check on a few of the other suggestions tomorrow when I get back into
the office and let you know if any of them were the right area to look in..

Again, thank you for your response.

Access doesn't begin to act up at 2 GB.  It's full at 2 GB.

The data in linked tables doesn't count toward the 2 GB in the mdb file..
It's just a little bit of space in the mdb for each linked table to
coordinate with the linked file.

Look at the size of your mdb file in explorer before and after adding a
linked table.  Not much bigger.

Chris
Microsoft MVP

 

[a a r o n . k e m p f]

and by the way, the freeware SQL Server 2005 Express-- the default
limit for that is now 4gb.. and with several other versions of their
free databases-- there is -NO- limit on size

Thanks - Chris ...

I have just started a new position and am already encountering the
following...

I have an .mdb that is acting up, but only shows about 1 1/2 gig.  At times
the tables just disappear and when you close and re-open, there they are
again.  

Or I am unable to re-open the database after having just worked in it and
closing it.  

I am able to re-open it only ...  if I open Access first and then clickon
open and browse for the .mdb file.  However, clicking straight on the .mdb
file will not open it.  The .ldb file won't go away after the .mdb has been
closed and I can't delete it either.  I have been researching and have found
that maybe it is being caused by permissions to the folder on the network
server.  

My concern is that the .mdb will (or is becoming) become corrupted.

I will check on a few of the other suggestions tomorrow when I get back into
the office and let you know if any of them were the right area to look in..

Again, thank you for your response.

Access doesn't begin to act up at 2 GB.  It's full at 2 GB.

The data in linked tables doesn't count toward the 2 GB in the mdb file..
It's just a little bit of space in the mdb for each linked table to
coordinate with the linked file.

Look at the size of your mdb file in explorer before and after adding a
linked table.  Not much bigger.

Chris
Microsoft MVP

 

[BruceM]

Again, you are wrong. If your design is poor you may have problems no
matter the file size, but especially as the file gets larger. It's sort of
like with vehicles. If you are a poor driver an SUV may keep you safer than
a smaller car when your lack of skills get you into trouble, but it doesn't
compensate for your inept driving. If you are having problems with a 50MB
Access split database with 6 concurrent users the problem is most likely
with your design.

message
Access doesn't work well once you start getting a half dozen users or
about 50mb of data.

anything larger than that means that you should move to SQL Server.
Because most people cannot forecast the growth in a system-- I claim
that it is always best to 'do it nice or do it twice'

 

[gmbrant]

Hi Chris,

Please just ignore Aaron, I am.

I did, by habit, create a backup. I also ran a compact when I started
noticing things. I just started this position last week. The database I am
working on has already been created ... tables, queries, forms, reports, etc.
Perhaps there is some poor design ... (I am not perfect... but this one
isn't my work) ... I will be going over everthing. I just need to understand
what causes these kinds of things to happen and what the solutions are.
Although upgrading to SQL has been part of their disucssion, I haven't been
here long enough to understand their data and their needs.

If the db is at 1 1/2 gig, when you run queries that have nested queries -
possibly 4-5 levels - could that be causing the database to grow too big
temporaily. Would creating Make Tables (temp tables) and deleting them when
finished using them, help keep things under control?

A person's time is valuable, it should be used wisely and not wasted ... I
appreciate your giving me some of yours.

Thanks!

Back up the db first and compact it. What size is it now?

You can't delete an ldb file once the db is closed if you don't have delete
permission on the folder or if the file is locked. Fix it.

You can't reopen the db when the ldb file is there without opening Access
first because the folder is on the network and that drive or folder isn't
trusted by your pc. Fix that too.

Fix these things and your subtle signs of corruption might go away. Make
backups anyway.

Chris
Microsoft MVP

Thanks - Chris ...

I have just started a new position and am already encountering the
following...

I have an .mdb that is acting up, but only shows about 1 1/2 gig. At times
the tables just disappear and when you close and re-open, there they are
again.

Or I am unable to re-open the database after having just worked in it and
closing it.

I am able to re-open it only ... if I open Access first and then click on
open and browse for the .mdb file. However, clicking straight on the .mdb
file will not open it. The .ldb file won't go away after the .mdb has been
closed and I can't delete it either. I have been researching and have found
that maybe it is being caused by permissions to the folder on the network
server.

My concern is that the .mdb will (or is becoming) become corrupted.

I will check on a few of the other suggestions tomorrow when I get back into
the office and let you know if any of them were the right area to look in.

Again, thank you for your response.

Access doesn't begin to act up at 2 GB. It's full at 2 GB.

[quoted text clipped - 13 lines]

If all of your tables are linked to excel files, is the data in the linked
excel files eating up part of that 2 gig as if they were tables within Access?

 

[Bill Mosca]

If the db is at 1 1/2 gig, when you run queries that have nested queries -

possibly 4-5 levels - could that be causing the database to grow too big
temporaily. Would creating Make Tables (temp tables) and deleting them
when
finished using them, help keep things under control?

I think you are right about the database growing too big. When any
editing/inserting/deleting is done, Access puts the original records in a
buffer in case the transaction needs to be rolled back. The buffer adds size
to the file.

I suggest breaking the objects out into smaller files if possible. And if
the database is not already split, now would be a good time to do so. Then
you can decompile the database front end to see if that saves you space. See
Tony Toews' page for /decompile at
http://www.granite.ab.ca/access/decompile.htm

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Hi Chris,

Please just ignore Aaron, I am.

I did, by habit, create a backup. I also ran a compact when I started
noticing things. I just started this position last week. The database I
am
working on has already been created ... tables, queries, forms, reports,
etc.
Perhaps there is some poor design ... (I am not perfect... but this one
isn't my work) ... I will be going over everthing. I just need to
understand
what causes these kinds of things to happen and what the solutions are.
Although upgrading to SQL has been part of their disucssion, I haven't
been
here long enough to understand their data and their needs.

If the db is at 1 1/2 gig, when you run queries that have nested queries -
possibly 4-5 levels - could that be causing the database to grow too big
temporaily. Would creating Make Tables (temp tables) and deleting them
when
finished using them, help keep things under control?

A person's time is valuable, it should be used wisely and not wasted ... I
appreciate your giving me some of yours.

Thanks!

Back up the db first and compact it. What size is it now?

You can't delete an ldb file once the db is closed if you don't have
delete
permission on the folder or if the file is locked. Fix it.

You can't reopen the db when the ldb file is there without opening Access
first because the folder is on the network and that drive or folder isn't
trusted by your pc. Fix that too.

Fix these things and your subtle signs of corruption might go away. Make
backups anyway.

Chris
Microsoft MVP

Thanks - Chris ...

I have just started a new position and am already encountering the
following...

I have an .mdb that is acting up, but only shows about 1 1/2 gig. At
times
the tables just disappear and when you close and re-open, there they are
again.

Or I am unable to re-open the database after having just worked in it
and
closing it.

I am able to re-open it only ... if I open Access first and then click
on
open and browse for the .mdb file. However, clicking straight on the
.mdb
file will not open it. The .ldb file won't go away after the .mdb has
been
closed and I can't delete it either. I have been researching and have
found
that maybe it is being caused by permissions to the folder on the
network
server.

My concern is that the .mdb will (or is becoming) become corrupted.

I will check on a few of the other suggestions tomorrow when I get back
into
the office and let you know if any of them were the right area to look
in.

Again, thank you for your response.

Access doesn't begin to act up at 2 GB. It's full at 2 GB.

[quoted text clipped - 13 lines]
If all of your tables are linked to excel files, is the data in the
linked
excel files eating up part of that 2 gig as if they were tables
within Access?

 

[Bill Mosca]

Do you realize you are now becoming very annoying? Try cutting down to one
post per aaron-sighting. You'll get the same effect. And maybe in time
you'll get over this obsession.

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals




"Aaron Kempf knows nothing"

 

[John W. Vinson/MVP]

Hi Chris,

Please just ignore Aaron, I am.

I did, by habit, create a backup. I also ran a compact when I started
noticing things. I just started this position last week. The database I am
working on has already been created ... tables, queries, forms, reports, etc.
Perhaps there is some poor design ... (I am not perfect... but this one
isn't my work) ... I will be going over everthing. I just need to understand
what causes these kinds of things to happen and what the solutions are.
Although upgrading to SQL has been part of their disucssion, I haven't been
here long enough to understand their data and their needs.

If the db is at 1 1/2 gig, when you run queries that have nested queries -
possibly 4-5 levels - could that be causing the database to grow too big
temporaily. Would creating Make Tables (temp tables) and deleting them when
finished using them, help keep things under control?

Temp tables will only help if they are in a DIFFERENT DATABASE,
linked.

This is one case where I'll agree with Aaron (in substance if not in
style). If you're working with well over a gigabyte of actual data
*and* doing complex nested queries with them, SQL/Server is a more
suitable platform, just because of the 2GB limit.

 

[gmbrant]

Thank you John, something to think about.

Temp tables will only help if they are in a DIFFERENT DATABASE,
linked.

This is one case where I'll agree with Aaron (in substance if not in
style). If you're working with well over a gigabyte of actual data
*and* doing complex nested queries with them, SQL/Server is a more
suitable platform, just because of the 2GB limit.

 

[gmbrant]

Bill, Thanks for your input... I will continue to work on resolving this
issue. I had suggested to split the database .. not front end and back end
... just create another db and do some of the work in the new. I don't
believe it's a good thing to have everything crammed into one db.

If the db is at 1 1/2 gig, when you run queries that have nested queries -
possibly 4-5 levels - could that be causing the database to grow too big
temporaily. Would creating Make Tables (temp tables) and deleting them
when
finished using them, help keep things under control?

I think you are right about the database growing too big. When any
editing/inserting/deleting is done, Access puts the original records in a
buffer in case the transaction needs to be rolled back. The buffer adds size
to the file.

I suggest breaking the objects out into smaller files if possible. And if
the database is not already split, now would be a good time to do so. Then
you can decompile the database front end to see if that saves you space. See
Tony Toews' page for /decompile at
http://www.granite.ab.ca/access/decompile.htm

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Hi Chris,

Please just ignore Aaron, I am.

I did, by habit, create a backup. I also ran a compact when I started
noticing things. I just started this position last week. The database I
am
working on has already been created ... tables, queries, forms, reports,
etc.
Perhaps there is some poor design ... (I am not perfect... but this one
isn't my work) ... I will be going over everthing. I just need to
understand
what causes these kinds of things to happen and what the solutions are.
Although upgrading to SQL has been part of their disucssion, I haven't
been
here long enough to understand their data and their needs.

If the db is at 1 1/2 gig, when you run queries that have nested queries -
possibly 4-5 levels - could that be causing the database to grow too big
temporaily. Would creating Make Tables (temp tables) and deleting them
when
finished using them, help keep things under control?

A person's time is valuable, it should be used wisely and not wasted ... I
appreciate your giving me some of yours.

Thanks!

Back up the db first and compact it. What size is it now?

You can't delete an ldb file once the db is closed if you don't have
delete
permission on the folder or if the file is locked. Fix it.

You can't reopen the db when the ldb file is there without opening Access
first because the folder is on the network and that drive or folder isn't
trusted by your pc. Fix that too.

Fix these things and your subtle signs of corruption might go away. Make
backups anyway.

Chris
Microsoft MVP


gmbrant wrote:
Thanks - Chris ...

I have just started a new position and am already encountering the
following...

I have an .mdb that is acting up, but only shows about 1 1/2 gig. At
times
the tables just disappear and when you close and re-open, there they are
again.

Or I am unable to re-open the database after having just worked in it
and
closing it.

I am able to re-open it only ... if I open Access first and then click
on
open and browse for the .mdb file. However, clicking straight on the
.mdb
file will not open it. The .ldb file won't go away after the .mdb has
been
closed and I can't delete it either. I have been researching and have
found
that maybe it is being caused by permissions to the folder on the
network
server.

My concern is that the .mdb will (or is becoming) become corrupted.

I will check on a few of the other suggestions tomorrow when I get back
into
the office and let you know if any of them were the right area to look
in.

Again, thank you for your response.

Access doesn't begin to act up at 2 GB. It's full at 2 GB.

[quoted text clipped - 13 lines]
If all of your tables are linked to excel files, is the data in the
linked
excel files eating up part of that 2 gig as if they were tables
within Access?

 

[John W. Vinson/MVP]

Bill, Thanks for your input... I will continue to work on resolving this
issue. I had suggested to split the database .. not front end and back end
.. just create another db and do some of the work in the new. I don't
believe it's a good thing to have everything crammed into one db.

Take a look at
http://www.granite.ab.ca/access/temptables.htm

for one workable suggestion for handling temp tables without bloat.

 

[a a r o n . k e m p f]

3 tier jet database?

what a joke
you guys really.. I mean.. if you want a temp object you just need to
do this

Select *
Into #Employees
Where ManagerID = 113


There you go-- it took me ONE CHARACTER to build a temp table.. using
SQL Server

-Aaron

 

[Aaron Kempf knows nothing]

and by the way,

 

[Aaron Kempf knows nothing]

go and look for chinese characters in _ANY_ of your tables..

 

[Aaron Kempf knows nothing]

"a a r o n . k e m p f @ g m a i l . c o"

 

[Aaron Kempf knows nothing]

a a r o n . k e m p f @ g m a i l . c o

 

[a a r o n . k e m p f]

Chris

I am not a troll-- nice try.

I never got obnoxious-- I did get attacked in the real world by a
bunch of lying MVPs.
Yes-- while everyone says that _I_ am the one that has no self
control-- the real problem is that the other MVPs couldn't handle THE
TRUTH (that SQL Server is the most popular database in the world) and
had to act out ILLEGALLY to silence me.

Sounds to me like I am not the problem-- but the dipshits bringing
their attacks into the real world-- are the problem.

I just had a couple of MVPs that followed me around-- and wrote
letters to my employers-- complaining about my postings.
Making webpages, keeping track of _MY_PERSONAL_INFORMATION_.

I've asked plenty of times for others to not attack me-- Just because
I'm passionate-- just because 90% of the people around here are Jet
idiots-- does that mean that SQL Server is not right for everyone?

What they don't understand is that I did these postings FROM HOME.
BINGO-- Apparently some of those letters might have been _ILLEGAL_.. I
mean wow..

I'm not sure that the MVPs understand their legal liability.

It is a travesty that some dipshit actually listened to those letters.

And AFTER this I had to defend myself-- because I had no choice in the
matter.
THE MVPs BROUGHT THEIR BULLYING INTO THE REAL WORLD AND I DEFENDED
MYSELF.

Some day- they will taste the other end of the law, I guarantee it.
They did not act legally.

Someday things will be set right. I guarantee it.

-Aaron

 

[a a r o n . k e m p f]

Running queries 4-5 levels deep DOES NOT FUCKING WORK RELIABLY.

I spit on your database