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

I have an MS access 2010 database.
It does passthru queries and then exports a result table to another database.

I have problems when the file name of the output database is greater than about 83 characters.

I'm guessing this is because the string built to do the transfer must be less than 255 bytes.

When the max length is exceeded, no data is transferred and no error is given.
how can I calculate the max length of the output Path and File so that it doesn't break
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
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?
Jacques Bourgeois (James Burger)PresidentCommented:
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.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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.
"When the max length is exceeded, no data is transferred and no error is given.
 how can I calculate the max length of the output Path and File so that it doesn't break "

That's the guts of your question.  You'll need to find it by experimentation with your parameters
Dim exSource As Variant
Dim tblName As Variant
Dim exTblName As Variant
exDestination = "TheFullyQualifiedPathForTheTargetDatabase"
tbName = "SomeValidExportTableName"
exTblName = "TheNameOfTheTableInTheDestinationDatabase"
DoCmd.TransferDatabase acExport, , exDestination, acTable, tblName, exTblName

Open in new window

c:\ is three characters.
db1.mdb is seven more
Create a nested folder structure of
Fill in tblName and exTblName as appropriate
The start with
exDestination = "c:\characte1\db1.mdb" That's 20 characters.  Then
exDestination = "c:\characte1\characte2\db1.mdb" That's 30 characters.
Carry on til you get a bang! or a whimper.
Fine tune the last experiment until you KNOW exactly how many characters you can get away with given your parameters.

Now, having answered what you asked, I would suggest that you think about your folder structure.  I have occasioned poor performance from Access 2003 when the mdb was a long name and it was deep in the file structure that cleared up immediately when the file was renamed in line with 8.3 and put in a short-name folder off the root of the drive.  My engineer likes these insanely deep folder structures -- but backup programs DO NOT like his data.  Sometimes previous versions and copy & paste operations don't either.  There are executables that hate pathnames longer than 63 characters, 127 characters, and 255 characters respectively.

If you absolutely must drive stuff into the filesystem very deeply, there's our old friend the mapped drive to consider.
You can map out a drive letter to some insane path:
Net use z:  \\somecomputer\c$\some\really\stupidly\deep\into\the\file\system\path\that\I\cant\access\by\name\because\its\too\long
and then drive the file to z:\myfile.mdb
You could even do that dynamically in VBA with Shell() and tear out the mapping afterward.
with the path above (\\somecomputer\...) you could share long as long$
You can then either map \\somecomputer\long$ to z: and drive the file in
you can try to drive the file in with the UNC -- but I don't know if the Destination argument of TransferDatabase will take a UNC path as an argument or not -- I've never tried it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
philkryderAuthor Commented:
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.
Jacques Bourgeois (James Burger)PresidentCommented:
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
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.

For instance, the names of tables, queries, forms, etc., is limited to 41 characters.  Limits exist, but we seldom encounter them.
philkryderAuthor Commented:
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.
philkryderAuthor Commented:
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"
    Exit Function
End If

End Function

Open in new window


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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.