Using Access 2003 to Link to Paradox Data

[Noah Kaufman]

First off - let me say that this whole process is way more difficult than it
should be. Microsoft really blew it when setting up the whole idea of linked
tables.

I have a piece of software from a third-party that uses Paradox 4.x to store
it's data. I have set up an ODBC data source, and I can use Microsoft Word
to open the Paradox tables individually and get data. (Like for a mail
merge.)

So I know my ODBC connection set up works.

I now need to combine information in two of the Paradox tables (also as part
of a mail merge process). It would seem that Access would be perfect for
this, since I should be able to simply create a new database, and then link
the tables I need from the existing Paradox data.

It doesn't work, and that is my problem.

After reading lots of posts, I realized that I simply can't do the intuitive
thing -- which would be to use "File" -> "Get External Data" -> "Link tables"
to get the data out of Paradox and into Access. Attempting to do this gets
me the dreaded "You can't use ODBC to import external Microsoft Jet or ISAM
databases" error message.

So looking at some other posts, the recommended method around this seems to
be to write some VBA code to link the tables. Following a reference from
another poster, I read the Microsoft white paper on this, and wrote the
following code:

Public Sub AddLinkedTable1()
' Open the Microsoft Access database named Test.mdb.
Set CurrentDatabase =
DBEngine.Workspaces(0).OpenDatabase("C:\ClientTest.mdb")
' Create the TableDef object.
Set MyTableDef = CurrentDatabase.CreateTableDef("Attached Paradox Table")
' Set the connection information.
MyTableDef.Connect = "Paradox 4.X;DATABASE=T:\Timeslips data\"
MyTableDef.SourceTableName = "NAME"
' Append the TableDef object to create the link.
CurrentDatabase.TableDefs.Append MyTableDef
' Display a confirmation message.
MsgBox "Finished attaching " & MyTableDef.SourceTableName & ".", 0
End Sub

The problem is that although this does create a linked table, it still
doesn't work because I am now getting another error message:
"Unexpected error from external database driver (11270)."

This error occurs both when I run my VBA code, and also when I attempt to
click directly on the linked table entry from the "Tables" listing for my
test Access database.

Can someone please shed some light on what I'm doing wrong here, and how I
can fix this "unexpected error" thing? Thanks much, in advance.

Sincerely,

Noah Kaufman

 

[G. Vaught]

Don't use the ODBC choice, try using the Paradox choice when linking the
tables?

 

[Noah Kaufman]

Mr. Vaught -

If you look closely at the VBA code that I wrote, you will see that I don't
use ODBC, but rather the Microsoft native Paradox driver. This doesn't work
either, and was also suggested by Microsoft support when I contacted them.

They are currently working on it, and my guess is that this will get
escalated.

Sincerely,

Noah Kaufman

 

[Noah Kaufman]

After much difficulty, my partners and I figured out what was wrong and how
to fix it. As a service to the community at large, we're posting our results
here.

Once again - the fact that you can't do this via ODBC in Access seems really
dumb to us, but it turned out that this was a peripherial problem to what the
real difficulty was.

Turns out that the error code 11270 is a Paradox-specific error code
indicating that an exclusive lock was in place on the table we were trying to
work with.

We had a couple of people working on this, and they were each working on
several computers simultaneously. Turns out that when Microsoft Word opens a
Paradox file via ODBC it does so for exclusive access. This prevented all of
us from being able to do anything with the Paradox data in Access until the
lock was released.

We had stored the Paradox database files on one of our Windows 2003 servers
so that we could all work with it easily. Once we figured out what the error
message meant (which was no easy feat - we had to make an educated guess
based on some very outdated documentation on the Corel web site), we then
used the openfiles utility from the Windows 2003 Resource Kit to track down
the computer that was holding the lock.

And it turned out that this machine was the one that still had Word running
with our initial attempts to use the Paradox data for a Mail Merge via ODBC
still running on it. Once we shut down Word, the lock was released and
Access would then allow us to link to the Paradox tables using the native
Microsoft Paradox driver.