Whenever I create a FoxPro remote VIEW with SQL or Access as my back-end data source, I'm having a problem. Access uses a character field definition of "Text" and SQL has a similar field type called 'varchar'. Both of these field types only store the exact length of text characters in their fields compared to FoxPro which stores the actual text and then pads the remaining length of the field with 'spaces.'

Whenever I edit data in a 'Text' or 'varchar' field using a FoxPro VIEW, the changed data is always passed into the Access or SQL tables with the extra padded spaces. This is causing some of our Access programs to 'choke' as the code expects the data to be 'clean', i.e., no trailing spaces.

I can fix the problem my going into the Access code and adding code that trims the data before using it. Obviously, I don't want to go into all of our many Access programs just to add this extra 'trim' coding.

Also, I have found that problem does not exist when I send data updates via FoxPro's SQL Pass-Through feature. Why is the behavior of passing data different between a VIEW and SQL P-T? I prefer to use VIEWS but the trailing spaces issue causing me a serious problem.

Has has anyone ever run into this problem? Is there a simple solution to prevent VIEWS from "padding" the text data that is sent to either Access or SQL database tables?

I'm using VFP 7.0a, Access 97, SQL 7.0 and the latest ODBC & MDAC.

Thanks,
Robert