Foxpro ODBC bugs with Access2002 vs Access97

R

Rick Biggs

I'm converting a number of Access97 applications to
Access2002. Very few problems except for isseus with
FoxPro! We used to use the Jet ISAM driver for FoxPro to
import Telco invoicing data each month from Foxpro tables
xxx.dbf into MS Access to perform audits and analysis.
Worked well, and the VBA code below could readily re-link
the tables and point to a different source directory each
month.

With Access2000 and beyond, the ISAM driver is gone and
replaced by an ODBC driver. I've figured out how to make
this work mostly, but run into several bugs and/or design
issues.

1) When you open a Foxpro table thru an ODBC link, the
underlying xxx.dbf file is opened for read, but is never
closed. You must exit Access for the file system lock on
the table to be released. This means that Access
application can not delete or rename the file that was
just imported. Even deleting the linked table defintion
does not close the file and allow it to be renamed. This
is a major nuisance!

2) The FoxPro ODBC connect string is not documented
anywhere that I've been able to find. Parameters such as
SourceDB and SourceType appear to be outputs derived from
the ODBC DSN source. You can not override the Path to
the folder containing .dbf files at run time as you could
with the ISAM driver. It appears that you need to create
a new ODBC DSN. This is tedious to do once, let along
every month to repoint to a new monthly invoice archive.
This sounds like a design or documentation problem. I
could work around this issue by renaming files to make
them appear temporarily to be in a fixed location for the
15 minutes that it takes to import (INSERT INTO tblAccess
SELECT * FROM tblFoxPro)upto 100MB BUT, bug number 1
precludes this!

Any suggestions on existing patches or work arounds would
be greatly appreaciated.

Thanks

Rick
Sprint Canada
 
C

Cindy Winegarden

In [email protected],
Rick Biggs said:
I'm converting a number of Access97 applications to
Access2002. Very few problems except for isseus with
FoxPro! We used to use the Jet ISAM driver for FoxPro ....
With Access2000 and beyond, the ISAM driver is gone and
replaced by an ODBC driver.

You can download and install MDAC 2.5 (alongside whatever version you have
now). 2.5 was the last version to contain the JET ISAM drivers.
1) When you open a Foxpro table thru an ODBC link, the
underlying xxx.dbf file is opened for read, but is never
closed. You must exit Access for the file system lock on
the table to be released. This means that Access
application can not delete or rename the file that was
just imported. Even deleting the linked table defintion
does not close the file and allow it to be renamed. This
is a major nuisance!

Sorry I can't help on this one.
2) The FoxPro ODBC connect string is not documented
anywhere .....

Try
http://msdn.microsoft.com/library/en-us/odbc/htm/usgvodbc_7.asp?frame=true
and http://www.able-consulting.com/ADO_Conn.htm
 
D

david epsom dot com dot au

just imported. Even deleting the linked table defintion
does not close the file and allow it to be renamed. This

No good suggestions, just:

ODBC links are cached, but they should time out after about
10 minutes??? ????What happens if you CreateObject a separate
dbEngine.36 object to do the transfer??? Of course if
you want to rename it, why not rename it BEFORE the transfer
instead of After? (and delete or rename last months file
just before renaming this months file)

(david)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top