I am using Access to link to Pegasus Opera, which is a FoxPro application.

There are an enormous number of database files and I only need to access a
few. For each company, there are 194 dbf files which are prefixed with a
company code (e.g. s_sname.dbf relates to company 'S', o_sname relates to
company 'O' etc).

My application needs to be able to switch between several companies. I
cannot simply link to all the files and then use different tabledefs for two
reasons:

1) If a new company is created in Opera, a new set of dbf files is created.
They may not yet exist.
2) There are simply too many files!

I have acheived what I want through ODBC. I use pass-through queries and
change the table names used according the the required company. However I
had to use the Visual FoxPro driver to acheive this which is very hard to
install on the client machines. It then failed on the client's system which
held the files on an NT server due to some obscure permissions problem which
I do not yet understand.

The standard Microsoft FoxPro driver supplied with Access is an ISAM driver
and does not appear to support pass-through queries (correct me if I am
wrong).

The answer is to either
1) Create new linked tabledefs to the FoxPro files required
2) Redefine a limited set of linked tabledefs to change the SourceTable.

How do I do either of these?

If I use

Set tdf = dbs.CreateTableDef("TEST", , "seqco", "FoxPro
2.0;HDR=NO;IMEX=2;DATABASE=C:\OPERAW\SYSTEM")

and then attempt to append the tabledef to the Tabledefs collection , I get

    Error 3265 - No field defined - cannot append tabledef of index

In other words it is expecting the fields for the tabledef to be defined,
but I do not know the field definitions utill I have  linked the table! This
a chicken-and-egg, I do not know the table structure unitll I have linked to
it.

If I attempt to change the SourceTable property and then apply RefreshLink
of an existing linked table I get:

    Error 3268 - Can't change this property once the object is part of a
collection

Am I missing the point here?

Best Regards
Neil Sargent

Neil,
Please see the sub below i posted i while ago....

Sub sLinkTable()
Dim dbsJet As Database
Dim FoxTable As TableDef

Set dbsJet = CurrentDb
Set FoxTable = dbsJet.CreateTableDef("LinkedFoxProTable")

FoxTable.Connect = "Foxpro 3.0;DATABASE=C:\PROGRAM
FILES\MADC\LABS\LAB02"

FoxTable.SourceTableName = "categories"

dbsJet.TableDefs.Append FoxTable

MsgBox "table linked"
End Sub

hope this helps
James

Thank you James

I was using dbs.CreateTabledef( "AccessTable", , "FoxProTable", "FoxPro
2.0;C:\FoxPro\Data")

The lack of the optional Attributes parameter was the problem. If I set it
to dbAttachedTable, I got an argument error (dbAttachedTable is read-only).
No other values were appropriate, so I left the (optional) parameter out.

I then discovered a bit of MS knowledge base code which used 0 for the
attributes and this now works fine.

Best Regards
Neil Sargent