import longfilename.DBF

  • Thread starter Thread starter Kim.Baker
  • Start date Start date
K

Kim.Baker

Hi Guys,
I have recently reinstalled access after a computer crash and I can no
longer import .DBF files that I have been using for years.

I have found that renaming them into the 8.3 format allows me to
import them however, there is critical information in the long file
name that I manipulate through VBA.

Could anyone tell me if there is an addin or update that allows long
filename support i.e. 12.3

I must have had this installed before but I cannot remember doing it.

Thanks
Kim

PS Access 2002/XP is what I am using.

Remove undies to reply
 
Hi Kim,

This VBA function takes an ordinary (long) filespec and returns the
short path to the same file. It seems to work properly but hasn't been
exhaustively tested.


Function GetShortPath(ByVal FileSpec As String, _
Optional FailOnError As Boolean = False)
Dim fso As Object
Dim oFile As Object
Dim oFolder As Object
Dim S As String
Dim Buf As String

On Error GoTo ErrHandler:

Set fso = CreateObject("Scripting.FileSystemObject")
'Get a handle on the file
Set oFile = fso.GetFile(FileSpec)
Do
FileSpec = fso.GetParentFolderName(FileSpec)
If Len(FileSpec) = 0 Then Exit Do
Set oFolder = fso.GetFolder(FileSpec)
Buf = oFolder.ShortName & "\" & Buf
Loop

GetShortPath = oFile.Drive & Buf & oFile.ShortName
Exit Function

ErrHandler:
If FailOnError Then
Err.Raise Err.Number, "GetShortPath()", _
Err.Description & vbCrLf & "[In GetShortPath() function]", _
Err.HelpFile, Err.HelpContext
Else
Err.Clear
GetShortPath = ""
End If
End Function
 
Hi Kim,

Is it possible that you were importing the files via ODBC rather than the
native Dbase format? The FoxPro and Visual FoxPro ODBC driver is
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates/odbc. Post
back if you have trouble setting up an ODBC data source.
 
Thanks for your reply John. Unfortunately this routine strips vital
info from the filename but your effort is appreciated.
Cheers,
Kim

Hi Kim,

This VBA function takes an ordinary (long) filespec and returns the
short path to the same file. It seems to work properly but hasn't been
exhaustively tested.


Function GetShortPath(ByVal FileSpec As String, _
Optional FailOnError As Boolean = False)
Dim fso As Object
Dim oFile As Object
Dim oFolder As Object
Dim S As String
Dim Buf As String

On Error GoTo ErrHandler:

Set fso = CreateObject("Scripting.FileSystemObject")
'Get a handle on the file
Set oFile = fso.GetFile(FileSpec)
Do
FileSpec = fso.GetParentFolderName(FileSpec)
If Len(FileSpec) = 0 Then Exit Do
Set oFolder = fso.GetFolder(FileSpec)
Buf = oFolder.ShortName & "\" & Buf
Loop

GetShortPath = oFile.Drive & Buf & oFile.ShortName
Exit Function

ErrHandler:
If FailOnError Then
Err.Raise Err.Number, "GetShortPath()", _
Err.Description & vbCrLf & "[In GetShortPath() function]", _
Err.HelpFile, Err.HelpContext
Else
Err.Clear
GetShortPath = ""
End If
End Function


Hi Guys,
I have recently reinstalled access after a computer crash and I can no
longer import .DBF files that I have been using for years.

I have found that renaming them into the 8.3 format allows me to
import them however, there is critical information in the long file
name that I manipulate through VBA.

Could anyone tell me if there is an addin or update that allows long
filename support i.e. 12.3

I must have had this installed before but I cannot remember doing it.

Thanks
Kim

PS Access 2002/XP is what I am using.

Remove undies to reply
Click to expand...
Click to expand...

Remove undies to reply
 
Hi Cindy,
I don't think I was doing this but even clicking on a .dbf file with a
long filename would open Access and display the table and its contents
until recently.
But after this reinstall of Access, clicking on a long filename .dbf
file within explorer opens Access but throws up an error 3011, can't
find object.
This is what made me consider that perhaps some years ago when I was
using office 97, I might have added an update to the jet database
engine or something similar that carried over when I updated to office
XP but was lost in the crash and is not a part of the office XP Pro
that I have reinstalled.
Thanks for your suggestion,
Kim



Hi Kim,

Is it possible that you were importing the files via ODBC rather than the
native Dbase format? The FoxPro and Visual FoxPro ODBC driver is
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates/odbc. Post
back if you have trouble setting up an ODBC data source.
Click to expand...

Remove undies to reply
 
The point of this routine is that it doesn't touch the name of the
actual file. All it does is return the "short" equivalent of the path,
which you can pass to TransferDatabase without your having to rename the
file.

Thanks for your reply John. Unfortunately this routine strips vital
info from the filename but your effort is appreciated.
Cheers,
Kim

Hi Kim,

This VBA function takes an ordinary (long) filespec and returns the
short path to the same file. It seems to work properly but hasn't been
exhaustively tested.


Function GetShortPath(ByVal FileSpec As String, _
Optional FailOnError As Boolean = False)
Dim fso As Object
Dim oFile As Object
Dim oFolder As Object
Dim S As String
Dim Buf As String

On Error GoTo ErrHandler:

Set fso = CreateObject("Scripting.FileSystemObject")
'Get a handle on the file
Set oFile = fso.GetFile(FileSpec)
Do
FileSpec = fso.GetParentFolderName(FileSpec)
If Len(FileSpec) = 0 Then Exit Do
Set oFolder = fso.GetFolder(FileSpec)
Buf = oFolder.ShortName & "\" & Buf
Loop

GetShortPath = oFile.Drive & Buf & oFile.ShortName
Exit Function

ErrHandler:
If FailOnError Then
Err.Raise Err.Number, "GetShortPath()", _
Err.Description & vbCrLf & "[In GetShortPath() function]", _
Err.HelpFile, Err.HelpContext
Else
Err.Clear
GetShortPath = ""
End If
End Function


Hi Guys,
I have recently reinstalled access after a computer crash and I can no
longer import .DBF files that I have been using for years.

I have found that renaming them into the 8.3 format allows me to
import them however, there is critical information in the long file
name that I manipulate through VBA.

Could anyone tell me if there is an addin or update that allows long
filename support i.e. 12.3

I must have had this installed before but I cannot remember doing it.

Thanks
Kim

PS Access 2002/XP is what I am using.

Remove undies to reply
Click to expand...
Click to expand...

Remove undies to reply
Click to expand...
 
Hi Kim,

On your old installation if you were in Windows Explorer and could open a
DBF using Access by double clicking then must have associated the DBF's Open
With to Access, as opposed to FoxPro if it was installed, or Excel.

You can change the Open With property using Explorer's Tools > Folder
Options > File Types dialog.
 
Hi Cindy,
Thanks for the reply.
Associations are correct. i.e. Clicking/double clicking a .dbf file
does open Access but where at one time the .dbf file would appear in
Access as a table, now, after Access opens I get the error 3011
'object not found, check spelling etc'.

Kim

Hi Kim,

On your old installation if you were in Windows Explorer and could open a
DBF using Access by double clicking then must have associated the DBF's Open
With to Access, as opposed to FoxPro if it was installed, or Excel.

You can change the Open With property using Explorer's Tools > Folder
Options > File Types dialog.
Click to expand...

Remove undies to reply
 
Always been a bit of a problem, but since I don't use DBF, can't
help with solution. Also, number one problem was using old dbase
formats that just did not support long file names, not your problem
here. There was some suggestion that there was a system option
for the IISAM drivers to control use of long file names. But new
ODBC drivers should not have that problem?
Most access 97 settings would NOT carry over to XP pro, because
1) different database engine is used (jet 4), and
2) ODBC drivers used instead of IISAM drivers.
However, dbase/foxpro/paradox/bde settings would remain on
the system if you had used those programs.

Also, not sure if you are describing an ODBC problem, or
an Access problem. Check ODBC by trying to create and test
an ODBC dsn for the dbf file.

Check Access by double clicking on an MDB. If same problem with
MDB, you have a problem with Access.Application registration
(should have "" quotes marks around the %1, as "%1")

(david)
 
many thanks for all the replies. Have isolated the problem to the
transferdatabase algorithm. Can't fix it but have managed to code a
function to strip the info I need from the .dbf filename and then
rename the 12.3 filename to temp.dbf and import that. Inelegant but it
works.
Cheers
Kim

Always been a bit of a problem, but since I don't use DBF, can't
help with solution. Also, number one problem was using old dbase
formats that just did not support long file names, not your problem
here. There was some suggestion that there was a system option
for the IISAM drivers to control use of long file names. But new
ODBC drivers should not have that problem?
Most access 97 settings would NOT carry over to XP pro, because
1) different database engine is used (jet 4), and
2) ODBC drivers used instead of IISAM drivers.
However, dbase/foxpro/paradox/bde settings would remain on
the system if you had used those programs.

Also, not sure if you are describing an ODBC problem, or
an Access problem. Check ODBC by trying to create and test
an ODBC dsn for the dbf file.

Check Access by double clicking on an MDB. If same problem with
MDB, you have a problem with Access.Application registration
(should have "" quotes marks around the %1, as "%1")

(david)
Click to expand...

Remove undies to reply
 
Back
Top