Appending to VFP 7 tables from Access 2003 w/ODBC

[cbrack]

I’ve linked to four free tables in a VFP 7 application. In my VBA code, I’m
pulling data from another Access dB, modifying it in my app, and attempting
to append records to the linked VFP tables. I’m using a System DSN to create
the links, and I’m using ADO 2.8. I’m able to delete and modify record in
the VFP tables, but not append?! The error I get when I attempt to use the
..AddNew method is that I’m trying to add a Null value to a variable not of
the variant type. One problem might be that all of the fields in the linked
tables are set to Required = Yes, which, of course, can’t be modified because
it’s a linked table. I’ve tried using a connection string to link directly
to the VFP tables and create a recordset, using both OLEDB and ODBC, and
although I can connect, I still am not able to append records. If I try
using a disconnected recordset, the error I get says the object must be
connected to perform the function when I try to use .AddNew.

Any thoughts, questions, or suggestions? Anything will be helpful.

 

[Cindy Winegarden]

Hi Charlie,

"... I'm trying to add a Null value ... all of the fields in the linked
tables are set to Required = Yes ...."

If a field is required that means it doesn't accept nulls. You can't add a
row with a null value for that field. What happens if you try to append a
row that has data in every field?

 

[cbrack]

The problem is, I'm only appending data to certain fields -- the fields that
are used in the application storing the data. Also, the fields in that app
are not required fields.

One thing I don't understand is why the linked fields in Access are all
required
fields.

Thanks for the response.

 

[Cindy Winegarden]

There's no way to know for sure unless you know the FoxPro table structure -
i.e. whether or not each field accepts nulls. The Fox app could be inserting
some sort of default value behind the scenes.

I created a test Fox table with one field that accepts null values and one
field that must not be null. I linked it to an Access MDB and then viewed
the table structure in Access. The field that would accept nulls had
Required = No and the field that was defined to not accept null values was
Required = Yes.

When I run the following queries, data is updated successfully:Insert Into TestNull (CanBeNull, NotNull) Values ("Hello", "World")
Insert Into TestNull (NotNull) Values ("World")
<<

When I run the following I get a rule violation error:Insert Into TestNull (CanBeNull) Values ("Hello")
<<