MS ACCESS 2010 output path name too long in DOCMD.TRANSFERDATABASE

This happens even with windows during deleting files, when the file is nested in long folder names.
The solution is to shorten the directory names that hold the file.

You may do the same with this export, and once done copy it to another convenient place.
Or you can check the length of the file path during its building procedure.

is the output database an attached table?
Are you doing this data movement in one step or does the data get staged in your intermediary database?
What mechanism are you using to output the data?

This is a limitation of Windows, not a limitation of Access.

As suggested, limit the lenght of the folder name and their depth. Note that the name of the file is also included in the path name, so you can also shorten the filename. Do you really need 83 characters in a filename?

What does your filenames look like, We might be able to suggest a way to name them so that it stays significative. For instance, no reason to have 2014-10-15 in a filename. 20141015 might not me as easily readable, but it gives the information anyway. Database_name can be shortened to DatabaseName.

several of the data export methods are deeply rooted in the Win32 world.  Even though they are 2007+ applications, some of them behave like they were 2003 (or earlier).

The first time I encountered this was a colleague who exported Access2007 data to an Excel2007 workbook.  The results were truncated at 64k rows, which is the row limit in Excel2003.

again the length of the  PATH and FILE combined is much less than 255 -

So, that shouldn't be an issue.

Something else in ACCESS is limiting the length to about 83.

Again, how can I calculate the length allowed in access.

Can you show the code that defines the path? There are ways for a path to become longer than the String you use to specify it. For instance, when you use relative paths (..\..\YourPath), the system reconstruct the whole path, so what appears to be short can end up being a lot longer than it appears. By seeing your code, we might be able to pinpoint that type of thing.

again the length of the  PATH and FILE combined is much less than 255 -
So, that shouldn't be an issue.
Something else in ACCESS is limiting the length to about 83.
Again, how can I calculate the length allowed in access.

I have never come across specific information about the size limitations (if any) for the DatabaseName parameter of DoCmd.TransferDatabase.  It is not something MS has documented and put out there.  Hence my suggestion that you need to find it by experimentation -- you seem to have found 83 as the upper limit.  I wouldn't fall out of my chair if it turned out that the path is limited to 63 characters and the filename to 31. 1,3,7,15,31,63,127,255 -- many things were spec'ed as certain number of bits and because backward compatibility is something to be cherished, sometimes those limits remain.
There's a list of published spec's here
http://webcheatsheet.com/sql/access_specification.php
but they do not mention the one you are interested in.

So, that shouldn't be an issue.

It clearly is an issue for you.
Now, I did my own experiment.
I took an mdb from my desktop, and I also created an ungodly set of folders.
I can issue
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\characte1\characte2\characte3\characte4\characte5\characte6\characte7\characte8\characte9\charact10\charact11\charact12\charact13\charact14\charact15\charact16\charact17\charact18\charact19\charact20\charact21\charact22\charact23\charact24\db1.mdb", acTable, "sumReceiptsByPeriod", "sumReceiptsByPeriod"

and it works.
I CANNOT create another subfolder.  Windows 7 tells me  "the file name(s) woud be too long for the destination folder..."  The total string length of the present path in the command is 250.
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\characte1\characte2\characte3\characte4\characte5\characte6\characte7\characte8\characte9\charact10\charact11\charact12\charact13\charact14\charact15\charact16\charact17\charact18\charact19\charact20\charact21\charact22\charact23\charact24\123456789.mdb", acTable, "sumReceiptsByPeriod", "sumReceiptsByPeriod" would be 256 characters -- and this fails with an error message saying the object cannot be found
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\characte1\characte2\characte3\characte4\characte5\characte6\characte7\characte8\characte9\charact10\charact11\charact12\charact13\charact14\charact15\charact16\charact17\charact18\charact19\charact20\charact21\charact22\charact23\charact24\12345678.mdb", acTable, "sumReceiptsByPeriod", "sumReceiptsByPeriod" which is 255 characters works.

So, definitively, by experimentation, the upper limit on the size of DatabaseName is 255 characters.  You will get an error message beyond that.

Yours fails silently at 83 characters.  You have some other problem.  Posting a sample and more details will be required to explore your issue further.

Nick67

For instance, the names of tables, queries, forms, etc., is limited to 41 characters.  Limits exist, but we seldom encounter them.

thanks all - I have not been able to find the cause - but, I feel I must somewhere mis-form the name of the output MDB.

probably user error

You could test and trap a non-existent path:

If Dir(TheFullPathOfTheOutputMDB) = "" then
    Msgbox "You have hose-bagged the output filename and the routine will fall down as a result"
    exit sub
End If

However -- it silently fails.
Would you mind to post the entirety of the routine?
One thing that I know will silently fail in VBA is if you are using the FileSystemObject to create a folder, and the string to create it has an illegal character.  The string used for FileSystemObject.BuildPath(pathspec) CANNOT contain an illegal character or the VBA just silently exits.

I test for it

Public Function IllegalUsed(PossibleNewPath As String) As Boolean

If Nz(PossibleNewPath, "") = "" Then Exit Function


Select Case True
    Case PossibleNewPath Like "*\*"
        IllegalUsed = True
    Case PossibleNewPathLike "*/*"
        IllegalUsed = True
    Case PossibleNewPath Like "*:*"
        IllegalUsed = True
    Case PossibleNewPath Like "*" & Chr(13) & "*"
        IllegalUsed = True
    Case PossibleNewPath Like "*" & Chr(34) & "*"
        IllegalUsed = True
    Case PossibleNewPath Like "*>*"
        IllegalUsed = True
    Case PossibleNewPath Like "*<*"
        IllegalUsed = True
    Case Else
        IllegalUsed = False
        
End Select
       
If IllegalUsed = True Then
    MsgBox "You have used an illegal character (\/:*?" & Chr(34) & "<>) in your new file path." & vbCrLf & "This will cause the routine to silently FAIL" &  vbCrLf & "Edit the name to avoid the illegal keystroke.", vbCritical + vbOKOnly + vbMsgBoxSetForeground, "bad file name"
Else
    Exit Function
End If


End Function

Select allOpen in new window

@Nick

I don't think your code does what you want it to do.  If a path contains the drive letter, then a colon is expected/required and not illegal.

Similarly, any multi-level path should be separated by a backslash characters and, therefore,  not illegal.

My checking code looks more at what the user is inputing as I use it.  If  I allow the user to input a string that will then be concatenated in code to create a full path, it plays merry hell with things if they decide to enter '2 3/8" crossover'.  Things go bang  -- or silently die -- because the slash drives things farther into the filesystem than anticipated.  The quotes break the concatenation, etc.  The testing for what will make for an unhappy filespec for BuildPath will depend very much on how you've structured the user input.  In the case for the code I posted,  I supply the drive and main folder through code.  The filename comes from  a FileDialog box.  The rest comes from controls on the form -- and those values cannot have any of the characters noted or the routine falls down.  A filespec can only have ONE colon, and I supply that.  I am not going through the hassle of escaping out slashes, backslashes and quotes all along the line.  The users get made aware that such things will cause MY app grief.  The point is that the filepath of the MDB in transferdatabase can be tested for if that's the suspected grief.