Linking a dBase III file with the 'wrong' extension

[Guest]

I have a 3rd party app that creates dBase III files with the extension CS
instead of DBF. I'm hoping to link these files in Access 2003 and read them
live which means I would like to avoid making copies of the files with the
correct extension.

Using the following code where sFile is the full path I can import the files
if I rename the extension to dbf just fine. However, I get Run-time error
'3011' if I import that same file with the CS extension.

DoCmd.TransferDatabase acLink, "dBase III",
fso.GetParentFolderName(sFile), acTable, fso.GetFileName(sFile),
fso.GetBaseName(sFile)

I've seen people reference http://support.microsoft.com/kb/306144 but that
seems specific to text files, not dBase files.

Any thoughts on how to get Access 2003 to link to these files without having
to rename the file or copy it?

Thanks,
Bryan

 

[John Nurick]

Hi Bryan,

As you've probably found, there's nothing in the Jet registry settings
for dBASE files that corresponds to the DisabledExtensions key for
text files. And the Microsoft ODBC driver for dBASE also seems to
accept only the .DBF extension.

I can only think of two things to do. One is to look for another ODBC
driver for dBASE (I've no experience in this area and don't know if
one exists).

The other - assuming your hard drive(s) use NTFS or similar - is to
create a hard link to the .CS file, using a .DBF extension. So you
could have two entries in the same directory
XXX.CS
and
XXX.DBF
both pointing to the same file. I don't see why this shouldn't work.
The standard Windows XP tool for creating hard links is fstutil.exe:
see e.g.
http://www.microsoft.com/resources/.../proddocs/en-us/fsutil_hardlink.mspx?mfr=true
but there are also third-party GUI tools.

On Fri, 9 Nov 2007 08:51:02 -0800, Bryan Dam <Bryan

 

[Guest]

Thanks for the reply John. The data is on a network drive so I can't create
a hardlink. ... at least not easily However, I had luck (I think) with
playing with the ODBC drivers.

What I'm up against now is running the INSERT statement on the connection
object to import the records. I'm getting a "Data type mismatch on criteria
expression" which is most likely because of trying to entering empty data
into numeric or date fields. I would simply make each field a text field in
Access but when I try and edit the table it tells me I have too many fields
even though there are only 253. I tried a compact and repair but that didn't
too anything. Any thoughts on how to alter the INSERT statement to deal with
empty fields?

Thanks again
Bryan

 

[John Nurick]

Hi Bryan,

1) Make sure that the Access fields are set to allow Nulls
(Required=False) and where relevant, zero-length strings.

2) Don't forget you can do type conversion and handle empty fields or
other special cases by using calculated fields in the INSERT
statement.

On Mon, 12 Nov 2007 07:27:01 -0800, Bryan Dam <Bryan