Solved

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

Posted on 2014-10-15
14
341 Views
Last Modified: 2014-10-28
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
0
Comment
Question by:philkryder
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 40383955
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40384220
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?
0
 
LVL 40
ID: 40384348
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40384377
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.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40384867
"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
c:\characte1\characte2\characte3\characte4\characte5\characte6\characte7\characte8\characte9\charact10\\charact11...
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.
Or
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
Or
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.
0
 
LVL 1

Author Comment

by:philkryder
ID: 40385249
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.
0
 
LVL 40
ID: 40385266
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 26

Expert Comment

by:Nick67
ID: 40385402
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40385481
For instance, the names of tables, queries, forms, etc., is limited to 41 characters.  Limits exist, but we seldom encounter them.
0
 
LVL 1

Author Comment

by:philkryder
ID: 40409556
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.
0
 
LVL 1

Author Closing Comment

by:philkryder
ID: 40409561
probably user error
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40409659
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

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40409750
@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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40409817
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now