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
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