Looking for index of imported linked FoxPro Table (Access 2007)

[Wehbyj]

Hi every body,

This's my first post here, as I thought it's better to be a member of your support forum.

I've created a new database, and needed to create a link for Foxpro table from another database over network.
It worked fine, but I read that access will ask for a link for the index of the linked table, which did NOT happen.

Now I can read from the linked table, but my problem is that the query (SELECT Query) needs a while to reply !!

The imported table contains about 200,000+ records, of 12 fields.
How can I bypass this delay (about 50 sec.) ?

Thanks for you support.

 

[NauticalGent]

Too many variables to drill this down. 200k records is not too big a file so it could be anything from table design to network speed/performance. Is converting from Foxpro to native Access out of the question?

 

[The_Doc_Man]

The key phrase is "over the network." I'm with Gent... can you directly copy the DB and then do a non-network import? How often does the table change?

Even only as an experiment, eliminating the network will tell you whether the problem is cause by the network or caused by the translation.

Does the SELECT query have a complex WHERE clause? Translating from FoxPro AND doing filtration at the same time would require a lot of network operations.

 

[Wehbyj]

First, thanks for you all for your response.

Second,
W.r.t. my problem, the network speed is very normal , and transferring the table to local access will sure solve the speed problem, but this's out of this post as the linked table is appended continuously from another (not opened) FoxPro database platform.

Third,
If I transfer the table to Access table and keep it over network , also this will solve the speed issue, which means its not a network speed issue.

The query is very simple, and if I applied it without any criteria it will open instantly, but If I tried then to go to the last record within the query the delay returns , or if I use any simple criteria also the delay appears and no response till about 1 min. !

I think its about the need for the index file (.cdx) of the table which its already found beside the DB table file, and I supposed Access to ask for it. In addition that when I see the table design from Access I don't see any primary key symbol beside any field !

So I think its the need of table index, the thing that I didn't know how or where to apply it to the linked table!

Here's an old post seems very like my problem :
... forums/showthread.php?t=179246
(I couldn't refer the full url as there's not enough posts)

Again thank you .

 

[Wehbyj]

Hi again

This to tell that I've solved my problem very well.
I used the ODBC data source.
I'll give more details later and explain the solution.
Thanks all.

 

[Wehbyj]

First I read that I've to have the ODBC driver installed
Which is not supported anymore.
I found it here : topol.eu/files/download/other/VFPODBC.msi

Then I use the following steps from : tek-tips.com/viewthread.cfm?qid=262341

BlindPete (Programmer) 30 Apr 2002.

[Setting up link in Access to DBF]

This assumes the DBF is free table and not part of a DBC

1) In your MDB select File menu, Get External Data, Link Tables

2) Files of Type, Select ODBC databases and a new dialog box will appear.

3) Switch to Machine Data Source Tab

4)Select Visual FoxPro Tables and click OK

5) Type in the path or navigate to it with the browse button. Keep the Free Table Selection, then select OK.

6) A new dialog will list all the DBF tables in that directory. Select all the ones you want to import and click OK.

7) Next you will be prompted to select a unique key field. Foxpro does not require this, and very likely your table does not have one. So w/o making any selection click OK for each table.

8) Now your done. You can query the DBFs just like any other one.

yes CDX are compound index files and may contain many indexes.

yes FPT are memos, and they are translated perfectly by the procedure described above.


If you don't need to maintain the DBF you can import it using the same procedure, just choose import data instead of link in step 1.
-Pete

I then got "Reserved error (-7778)" from access..
Then I solved it Here : basis.com/kb00509

Briefly here :

This error occurs when the File Data Source is selected from Access. If the data source is selected from Access' Machine Data Sources tab, then the error will not occur.

I then linked the table with the option to choose the Key fields.
It works very fine really.

 

[The_Doc_Man]

Excellent, and thanks for not only detailing the problem but ALSO detailing the solution. Our frequent - and casual - users will appreciate this insight!