connect a dbase table with non-standard extension

[Al Blake]

I need to connect to a base table that has the extension '.D' in stead of
the standard .DBF
This is legacy app so I cant change the file extensions or it will break

Unfortunately the ODBC drivers for dbase will *ONLY* open a table if it has
a .DBF extension.
I thought I had found an answer with some old info on the MSDN site that
stated DAO would
open fielnames with other extensions if you separated the exsention with a #
sign:

"The name of the table -- the .DBF filename without the extension, or the
complete filename with the extension but substituting a pound sign (#) for
the dot (.) that precedes the filename extension"

This was in the "Accessing External Data with Access 2.0" at
http://msdn.microsoft.com/archive/d...ccessingexternaldatawithmicrosoftaccess20.asp

However I have spent hours trying to code an equivalent and cant get it to
work. Has this functionality been dropped or does anyone know another way to
reproduce it.
Here's my code that fails:

Sub linktable()
Dim myCurrentdb As Database
Dim myTabledef As Tabledef

Set myCurrentdb = Currentdb()
Set myTabledef = Currentdb.CreateTableDef("test")
myTabledef.Connect = "dbase III;DATABASE=C:\temp"
myTabledef.SourceTableName = "LBORROW#D"

myCurrentdb.TableDefs.Append myTabledef <== fails here indicating it
cant find the table.

End Sub

Al Blake, Canberra, Australia

 

[John Spencer (MVP)]

Can you copy the file and give it the proper extension? You can do the copy and
rename in VBA.

 

[Al Blake]

I could.....but this is a LIVE legacy app.....
and renaming the file (if the legacy app has to use it) is fraught with
danger.
I've been chasing this for 6 months now and find it unbeleivable that there
isnt some way to open the dbase table as it is

Thaks for the feedback.
Al.

 

[John Nurick]

Hi Al,

The analogous situation with text files can be fixed by altering a
registry entry, so this may be possible with dBase too: searching the MS
knowledgebase may find something.

Otherwise, if the .D file is on an NTFS volume you should be able to
sidestep the problem by creating a hard link to the file and giving it a
..DBF extension. Hard links are fraught with danger until you know what
you're doing, so try this out on a test setup before taking any chances
with your production database. IIRC you need the ln.exe utility from the
Windows 2000 (or XP or whatever) Resource Kit.

 

[Al Blake]

Wow
I didnt even know there were hard links in windows - I use them in unix all
the time.
I'll take a look at the ln utility.
Thanks for the tip.
Al.

 

[John Spencer (MVP)]

No, what I was suggesting was that you make a COPY of the datafile and when you
do rename the COPY with the dbf extension. OF course, if it is a LIVE
application you could get corruption in the copy, so that very well may not work
for you.