Link object is not supported on free connection us

Question

I'm developing a program using vb.net under VS2017 and ADS v11.1 on Visual Foxpro free tables with adsDataAdapter.

I want to copy records from a current table into a history table:

INSERT INTO
  c:\data\hinv.dbf
SELECT
  *
FROM c:\data\cinv.dbf WHERE [balance] = 0.00

I get the error:

The requested object was not found. c:\data\hinv Link object is not supported on free connection. Table name: dbf

The connection string includes

Data Source: c:\data\; TableType=VFP; LockMode=COMPATIBLE; ServerType=LOCAL

The connection works perfectly for SELECT, INSERT, DELETE and UPDATE commands not using a subquery, for example

INSERT INTO
  c:\data\hinv.dbf
(
    [field1]
  , [field2]
)
VALUES
(
    value1
  , value2
)

will work with no problem. I've tried the SAP community but received no response, and I've searched the net every way I can think of. I'm stumped.

Answer 1

ADS is confused by the dot notation of the table you are selecting from.

Here is a similar post:

http://devzone.advantagedatabase.com/forum/questions/3294/sql-selection-from-dbf

ADS thinks you are selection from a table named dbf on a server link named c:\data\hinv.

You probably have to set the table type from VFP to CDX for ADS to recognize the filename as a table name.