access doesn't like directory names that have a "." followed by sp

[marty]

If you try to move a table from one database to another using the following
SQL command it will fail with an "Invalid bracketing" error. The problem
appears to be that the directory name contains a period followed by a space
character. When you remove the period or the space it works.

SELECT Table1.* INTO Table1 IN 'C:\US. Air\db2.mdb' FROM Table1;

Is there some way I can get around this problem so that I don't have to
worry about what directory name the user chooses?

- marty

 

[David]

This is likely to be just the beginning of the problems that you could
experience in this scenario. Access REALLY dislikes directory names that have
have a period ANYWHERE is the path (other than the file extension).

Even if you are able to resolve this specific issue when exporting to
external tables, if you try to open the database file you specify below,
you'll probably run into the issue where the "security" feature of Access
refuses to open a database with a period in the path prior to the file
extension.

See the MSKB Article:
You receive an error message when you try to open an Access 2002 database
from a Web Server
http://support.microsoft.com/kb/810582/en-us

My recommendation would be to programmatically prevent the user from
exporting to a database with a period prior to the extension.

David

David

 

[Guest]

Use the Windows ShortName API to lookup the
8.3 equivalent name, and use that instead.

(david)

 

[david epsom dot com dot au]

Private Declare Function GetShortPathName Lib "kernel32" _
Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long


Function GetShortName(ByVal sLongFileName As String) As String
'2005/04/15 dlg from MS KB 891176
Dim lRetVal As Long, sShortPathName As String, iLen As Integer
'Set up a buffer area for the API function call return.
sShortPathName = Space(255)
iLen = Len(sShortPathName)

'Call the function.
lRetVal = GetShortPathName(sLongFileName, sShortPathName, iLen)
'Remove unwanted characters.
GetShortName = Left(sShortPathName, lRetVal)
End Function

(david)