Solved

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

Posted on 2014-10-15
14
360 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

808 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