Access maximum file path length

[CJ]

Hi Groupies

I have just spent about 9 hours trying to figure out why my 2003 database
was "corrupt".
It turns out that it isn't corrupt but the file path was approaching a
length that access might have trouble with.
I put the database on the desktop and VOILA, absolutely no more problems!

I have seen all kinds of literature about windows allowing approximately 250
characters in the path
length. My experience is that the limit for an access database is about 150.

Is this limit due to the added length of SQL statements and calculations
behind the scenes?

 

[Tony Toews [MVP]]

I have just spent about 9 hours trying to figure out why my 2003 database
was "corrupt".
It turns out that it isn't corrupt but the file path was approaching a
length that access might have trouble with.
I put the database on the desktop and VOILA, absolutely no more problems!

I have seen all kinds of literature about windows allowing approximately 250
characters in the path
length. My experience is that the limit for an access database is about 150.

Is this limit due to the added length of SQL statements and calculations
behind the scenes?

Now that is interesting. it would more likely be a problem due to
the combined length of the path and MDB names as well as object name
and the field name.

I just tested this with the FE and BE in four folders and subfolders
named
012345678901234567890123456789012345678901234567890
(Ah, that's 51 characters each) and another subfolder and MDBs
pushing the limits of the 255 character path.

I was able to create longish tables in the one BE. I then created
long query and forms names in the FE and was able to successfully view
data in the BE MDB.

So I can't duplicate your problems. And yes that is A2003 SP3 with a
hotfix.

Tony

 

[Tony Toews [MVP]]

I have just spent about 9 hours trying to figure out why my 2003 database
was "corrupt".
It turns out that it isn't corrupt but the file path was approaching a
length that access might have trouble with.
I put the database on the desktop and VOILA, absolutely no more problems!

Do you have AutoCorrect turned on?

Tony

 

[CJ]

Heavens No!! I checked that Devil's Spawn right away!! <grin>

The reason why I thought of moving the database in the first place .... I
use an Access based program for a sporting event. While I was evaluating the
software I received an error message every time I tried to print a report,
any report. Another person tested the program on a different computer and
received similar errors. We thought it was a corruption with the reports or
an incompatibility with printer drivers or something. When we moved the FE
to a higher folder or the desktop, the problems ceased and we were able to
print. This happened on both computers and is now well documented with the
software which is used across Canada.

I am not receiving exactly the same error but the result is similar; it
either freezes or says that there is "Insufficient Memory to perform the
task". I agree with you Tony, in that I think it is the combined file path,
object names etc that cause the problem. I am also using 2003 sp3 in this
instance, the previous example was also 2003 based.

I have created many databases that have been buried under excessive layers
and this is only the 2nd time I have run into this problem.

Perhaps it can be summed up as an "Issue".

--
Thanks for taking the time!

CJ

 

[Tony Toews [MVP]]

Heavens No!! I checked that Devil's Spawn right away!! <grin>

Sorry, had to ask.

The reason why I thought of moving the database in the first place .... I
use an Access based program for a sporting event. While I was evaluating the
software I received an error message every time I tried to print a report,
any report. Another person tested the program on a different computer and
received similar errors. We thought it was a corruption with the reports or
an incompatibility with printer drivers or something. When we moved the FE
to a higher folder or the desktop, the problems ceased and we were able to
print. This happened on both computers and is now well documented with the
software which is used across Canada.

I am not receiving exactly the same error but the result is similar; it
either freezes or says that there is "Insufficient Memory to perform the
task". I agree with you Tony, in that I think it is the combined file path,
object names etc that cause the problem. I am also using 2003 sp3 in this
instance, the previous example was also 2003 based.

I have created many databases that have been buried under excessive layers
and this is only the 2nd time I have run into this problem.

Perhaps it can be summed up as an "Issue".

Agreed. But can you create a minimal MDB file with just one query and
one form that causes this problem? And email it to me. Email address
is on my website.

Tony

 

[David W. Fenton]

Is this limit due to the added length of SQL statements and
calculations behind the scenes?

It has long been a well-known performance tweak to make sure your
back-end data file is stored in a share with the shortest path
possible, for example:

\\Server\Database\MyDB.mdb
....is going to be faster than:

\\Server\Data\Databases\Access\Project1\DataFiles\MyDB.mdb

While this is not the same problem, it suggests that there is
something about Access/Jet/ACE that is more sensitive to path length
than the hardwired path limitations would suggest.

Were there spaces in the path, too? I never uses spaces in filenames
or folder names, and have a few places where database are buried
pretty deeply in my user profile folder, but I've never encountered
anything like what you describe. But just checking, none of my paths
is over 100 characters.

 

[CJ]

Hi Dave

Thanks for the feedback.

No blanks anywhere in anything I develop ever.

I didn't move the back end, just the front.
No worries though. Just one to hold in the back of your mind.

 

[CJ]

Hi Tony

I'm not sure if I will be able to recreate a minimal example.

This is a complex database (aren't they all) and it only happens with one
form and one report. Neither have graphics or excessive subforms or
subreports. Code is clean.......I think...

I will see what I can do.......won't be before next week though.
--
Thanks for taking the time!

CJ

 

[Tony Toews [MVP]]

I'm not sure if I will be able to recreate a minimal example.

This is a complex database (aren't they all) and it only happens with one
form and one report. Neither have graphics or excessive subforms or
subreports. Code is clean.......I think...

Only happens with one form and one report? Now things are getting
even more interesting. But they do have subforms and subreports? Yet
others do to which don't have problems.

Hmm mm

Tony