By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,136 Members | 1,949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,136 IT Pros & Developers. It's quick & easy.

Access Import to Pervasive/Btrieve table

P: 1
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.
Dec 5 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You could try

Expand|Select|Wrap|Line Numbers
  1. Left(NewDMS.Code, Len(NewDMS.Code))
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 5 '06 #2

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 5 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.