Access Import to Pervasive/Btrieve table

[Manah]

I currently receive text files that need to be manipulated into a suitable format for uploading into a Pervasive/Btrieve datafile. The text file I receive needs a number of steps to clean it up and so I set up an Access database to perform all the steps and would like to link the output directly to the database table rather than exporting to text and manually importing into the pervasive database table.

I have linked the pervasive data table through an ODBC connection, but I now have an issue where the output being imported into the data file is including the trailing blanks. Example is that the Data file field is a zstring 7, the data imported could be 4 or 5 in length, but on examination of the data file, the field shows the data with 2 or 3 trailing blanks on a key field. The database cannot be altered as it is part of a larger software package.

I use the following sql statement inside of the VB code to do this
[code]
DoCmd.RunSQL sqlstr
sqlstr = " INSERT INTO ZAgents ( Code, Agree_No, Distrib_Date, Distrib_Status, Title)
SELECT NewDMS.Code, rtrim(NewDMS.Code), NewDMS.Date1, NewDMS.Status, NewDMS.Title
FROM NewDMS;"
DoCmd.RunSQL sqlstr
[code]

This is only part of a larger insert statement and as you can see, I have tried "TRIM" and "RTRIM" but neither work.

Any help or directions where I can research will greatly appreciated.

[MMcCarthy]

You could try

Left(NewDMS.Code, Len(NewDMS.Code))

Right(NewDMS.Code, Len(NewDMS.Code))

However, I think your problem lies in the parsing of the code and the odbc connection may not be up to the job. As I don't know anything about Pervasive/Btrieve datafiles I can't help further.

I currently receive text files that need to be manipulated into a suitable format for uploading into a Pervasive/Btrieve datafile. The text file I receive needs a number of steps to clean it up and so I set up an Access database to perform all the steps and would like to link the output directly to the database table rather than exporting to text and manually importing into the pervasive database table.

I have linked the pervasive data table through an ODBC connection, but I now have an issue where the output being imported into the data file is including the trailing blanks. Example is that the Data file field is a zstring 7, the data imported could be 4 or 5 in length, but on examination of the data file, the field shows the data with 2 or 3 trailing blanks on a key field. The database cannot be altered as it is part of a larger software package.

I use the following sql statement inside of the VB code to do this
[code]
DoCmd.RunSQL sqlstr
sqlstr = " INSERT INTO ZAgents ( Code, Agree_No, Distrib_Date, Distrib_Status, Title)
SELECT NewDMS.Code, rtrim(NewDMS.Code), NewDMS.Date1, NewDMS.Status, NewDMS.Title
FROM NewDMS;"
DoCmd.RunSQL sqlstr
[code]

This is only part of a larger insert statement and as you can see, I have tried "TRIM" and "RTRIM" but neither work.

Any help or directions where I can research will greatly appreciated.

[NeoPa]

I too feel that you've hit a limitation of the BTrieve ODBC connection.
You could try appending Char(0) to the end of the string.
If it's a C code library interface (Dim distant memory BTrieve was associated with C) then that will probably work for you.
If it does then you should create a function because all strings passed will need to be processed in the same way.