I currently recieve various text files that have to be massaged into a suitable format to up load into a btrieve table. The process was very manul intensive, so I set up an Access database to do all of the work. However when I connect the Access database to the Btrieve table via an ODBC connection I get trailing blanks on the fields that have been set up longer than the actual data going in. For example, Agree_No is a zstring 7, but the data imported could be either 4 or 5 characters in length.

The sql statement that I am using is wrapped inside of a VB code and its as follows.

DoCmd****nSQL sqlstr

sqlstr = " INSERT INTO ZAgents ( Code, Agree_No, Distrib_Date, Distrib_Status, Title, Name, First_Name, Middle_Name, Sex, Address_Link, Agent_Broker, Region, Agreement_Name, Brk_Rate_VPC, Brk_Rate_NoVPC, EffDate, Service_First,Created, Modified)SELECT NewDMS.Code, rtrim(NewDMS.Code), NewDMS.Date1, NewDMS.Status, NewDMS.Title, NewDMS.Name, NewDMS.First_Name, NewDMS.Middle_Name, NewDMS.Sex, NewDMS.Address_Lnk, NewDMS.Agent_Broker, NewDMS.Region, NewDMS.Agreement_Name, NewDMS.Brk_Rate_VPC, NewDMS.Brk_Rate_NonVPC, NewDMS.Eff_Date, NewDMS.Service_First, Created, Modified FROM NewDMS;"
DoCmd****nSQL sqlstr

I have tried using the trim commands but that doesn't work.

I do have a third party software that I can manually import the data into the table without this issue so there must be a way that I can get access to do the same.

Sorry, I should have mentioned that I am on Pervasive 2000i

I'm not sure I understand. What are you expecting instead of trailing blanks?
I do have a couple of questions:
1. How are the fields defined in the DDF (for example the "Agree_No" field you mention)?
2. What happens if you issue an Import statement through the Pervasive COntrol Center?
3. Is is possible to change the text file into CSV in the record layout you need? If so, you might be able to use the Pervasive Import Wizard.

The Agree_No is a key field in the software package and this field should not contain any trailing blanks, but can contain nulls.

The DDF has this field defined as a Zstring, 7 characters long.

The text file could be converted into a CSV file and manually loaded, but this would defeat the purpose of building a fully automatic process with no manual intervention required.

Are you expecting the field to be binary zero padded rather than space padded?
Can you post a BUTIL -STAT report of the file? ALso, can you post the record layout as the DDFs describes it?

I've seen this type of behavior when the data type in the DDF is a space padded type while the data file is expecting a binary zero padded value.

If you know the record structure and know the structure of the text file, you might consider writing (or having written) a program that uses the Btrieve API (rather than SQL) to insert this data.

Butil -stat and DDF report attached

Okay, your DDFs are wrong. You've got fields defined as String and as ZString. This could cause the exact behavior you are seeing. Specifically, your Btrieve file has String fields for the index but one of the indexes (and fields) in the DDFs defines that same field as a ZString. THis is the "name" field. In the Btrieve file, the "name" field is an index and is defined as a string. In the DDF, it's defined as a ZString. The String field is space padded while the ZString is binary zero padded.
I would suggest rebuilding the DDFs to correct the inaccuracies.

Thank you so much, I have just spent a very frustrating 12mths with the software vendors to try to resolve this issue without any where near the success your advice has given me.

My very first run with changing the DDFs has shown some very positive results, but I will probably need to spend another few months rebuilding and testing all of our DDFs to the max.

Once again, thank you for you help.

You're welcome. Good luck with the rebuilding..
Post if you have any other questions.

Well every thing worked OK on my C drive, but when I transferred this into the Network, I now get the following error-
"You tried to assign the Null value to a variable that is not a Variant data tye. No:3162"

The DDF's and the data files on the network are exactly the same as those on the C drive.

Regards