Hi

I used a procedure from MS KB to import data from some FoxPro tables
***
Private Sub Command3_Click()
On Local Error GoTo ImportError

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "tblBatchImport", con, adOpenForwardOnly, adLockOptimistic
DoCmd.Hourglass True

rst.MoveFirst
Do Until rst.EOF
DoCmd.TransferDatabase acImport, rst("TableType"), _
rst("SourceDir"), acTable, rst("SourceDB"), _
rst("ImportName"), False
rst.MoveNext
Loop
rst.Close

Set rst = Nothing
Set con = Nothing

ImportEnd:
DoCmd.Hourglass False
Exit Sub

ImportError:
MsgBox Err.Description
Resume ImportEnd

End Sub
***

It works, but:
1) It returns error (... can not open file ... It is already opened exclusively ...) whenever the Foxpro table is in use. But those tables are in use most of time;
2) It doesn't overwrite old table, imported earlier, but greates a new table every time. I.e. I have imported dable MyTable, and after running the procedure I have MyTable and MyTable1, and have to rename MyTable1 to MyTable;
3) One of source tables has some memo fields (in FoxPro, memo field contains link to special memo table). The procedure returns an error every time when querying it;
4) The procedure above does import complete tables. I don't need most of fields in my application at all, and from some tables I want only data, for which certain conditions are met.

I can easily create an ODBC query in Excel (using ODBC driver for VisualFox Tables), which is doing much better. I tried to make the same thing in Access VBA, but I get only confused. And all examples I did find, were about importing from another mdb, or the code was in VB.

I hope for some good advice here. And maybe QueryTable properties for my Excel query are useful:

Worksheets(“Sheet1”).QueryTables(1).Connection="OD BC;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=t:\SourceFolder;SourceTy pe=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Ma chine;Null=Yes;Deleted=Yes;"

Worksheets(“Sheet1”).QueryTables(1).Name="Query from Visual FoxPro Tables"

Worksheets(“Sheet1”).QueryTables(1).Sql= "SELECT a.f1, a.f2, a.f3 FROM tbl1 a, tbl2 b
WHERE b.key= a.key AND ((MONTH(b.datef)=2) AND (YEAR(b.datef)=2004)


Thanks in advance for any help
Arvi Laanemets