Link dropping on DBF Files

[Guest]

Hello,
I am attempting to import data into Access from a .DBF file. Directly
importing the table gives me the error "Record too large", so I decided to
programmatically link to the table, and perform a make-table query to get
just the data I want. This works fine the first time, but if I close Access
and re-open it, the link is broken and cannot be re-established even by
running the Linked Table manager: it says that it cannot find the file. To
make it work, I have to delete the source file, delete the linked table, and
restart Access and do the link manually. This is not a solution because the
Access database has to be distributed and has to run automatically. Any help
would be greatly appreciated.

 

[John Nurick]

Hi Martin,

I've not come across this before, but here are a couple of suggestions:

1) Are you using long or short names? There are problems working with
DBF files that have long names, or that have long folder names in their
paths. If you haven't already done so, use DOS-style "8.3" names and
keep the total length of the path below 64 characters. (This isn't as
much of a limitation as one might think: Windows has short aliases for
all long names. Windows XP can display these in a command window if you
use DIR /X.)

2) Rather than programmatically linking the table, you could have your
code create and execute the SQL statement for the make table query,
getting data directly from the DBF rather than via a linked table. The
syntax is in Help; I think you need to go to Jet SQL Reference, then
SELECT statement, and from there to the IN clause.


On Mon, 15 May 2006 09:41:02 -0700, Martin Conrad <Martin

 

[Cindy Winegarden]

Hi Martin,

Like John I don't have any experience with this, but you haven't said
whether you're trying to work with the DBF via Jet or via the FoxPro and
Visual FoxPro ODBC driver, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates/odbc. Another way to access Fox
tables of all versions is the OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates.

I've noticed that Fox tables with Memo fields don't import the Memo field
data correctly when the tables are imported natively, but the data comes
across properly when accessed via ODBC.

 

[Guest]

Hi,

Thanks for your replies. Let me start from the beginning. We are working on
a report module for a dbase-based application. Most of the tables in this app
I can import into Access, but there are a few that give me the error "The
record is too large". These I link to, then perform a make-table query to get
the info I need. The problem is that when Access is closed then reopened, the
link to the external table is gone and I cannot re-establish it unless I
delete the link, delete the external DBF file, replace the external DBF file,
then recreate the link.

In response to John's note (which I appreciate), I tried a make-table query
to the external database, but it gives me the error "Unrecognized database
format". Any other suggestions would be greatly appreciated.

Thanks.

 

[Guest]

Hi again All,

Actually, never mind. I ended up writing code that uses Excel behind the
scenes to open the DBF file, delete the column that was causing the "Record
too large" error, and save it. It then imports into Access just fine.

Thanks again for the help,

Martin