Solved

How to change a BTRieve field name in a table...

Posted on 2006-10-20
10
1,029 Views
Last Modified: 2013-12-25
Pervasive SQL 8.6 (using standard DDF files)
Protech Vision AMS

Gentlemen,

I am trying to link tables from this AMS database into an Access database. Mostly for the purpose of enhanced reporting and data view capability.

However, some of the tables have field names with periods, # signs or other characters in them. When trying to link the tables in MS Access, I get the message that:

" 'Reason for Term. ' Is not a valid name. Make sure that it does not include invalid characters or punctuation or is too long".

I'm guessing there's no magic fix for this in Access, so my question is.... exactly how can I change the field names in question, what would the SQL syntax look like for this and are there any other issues like rebuilding the DDF files or indexes I'd need to do?
0
Comment
Question by:seriochka1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 17778794
Fields with "invalid" characters can be used by enclosing the field name in double quotes.  
Have you tried using Crystal Reports rather than Access for your reporting?  
I would also suggest contacting the vendor to get "ODBC Compliant" DDFs.  
I don't know if V8 has the functionality but PSQL V9 offers a RENAME function in the ALTER TABLE:
ALTER TABLE table RENAME COLUMN column-name TO new-column-name

0
 

Author Comment

by:seriochka1
ID: 17780116
We use crystal reports for a lot of our reporting functionality, but it's difficult sometimes to see exactly what data & criteria are being used in some of these reports. Our firm has been completely dependant for years on our vendor for every single upgrade we do and adding functionality (or even doing basic mass updates to field data) is not possible in-house. Although I'd probably use a true SQL-based tool instead of access, I want to enable the ODBC functionality so we can do more of this ourselves.

Question: Version 8 does have an ALTER TABLE command, but I didn't see a specific reference for the RENAME command. If I upgrade to Ver. 9, do you know if it has the "6.X" backward file definition compatibility? Or if taking my DDF's and running the command on a separate system against them, then bringing them back into version 8 will work?

-Steve

0
 

Author Comment

by:seriochka1
ID: 17780130
Also just to confirm....is there any way in MS Access to get around this problem and link the tables?

-Steve
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Accepted Solution

by:
mirtheil earned 250 total points
ID: 17780142
As long as you don't rebuild the data files while your using V9, you'll be able to revert to V8.  I would suggest using the "IN DICTIONARY" clause as well when using V9 just to be sure that the ALTER TABLE only affects the DDF.  

V9 does support 6.x files.  You can even read (but not write to) v5.x files with V9.  

If you application doesn't support V9, I would suggest using a separate system to run the ALTER TABLE command.
0
 
LVL 18

Expert Comment

by:mirtheil
ID: 17780161
I just tried and since you're trying to link, it does not work.  You might be able to use a "SQL Passthrough" query but you won't be able to Link.  THis is a limitation of Access not of PSQL.  
0
 
LVL 1

Expert Comment

by:wayne_freeman
ID: 17785580
Try turning the query into a stored procedure, using the AS clause for each column in the Select to rename the nonstandard columns, then using Access to call the stored procedure.

Wayne Freeman
Analytica Business Systems
www.analyticabiz.com 
0
 
LVL 28

Assisted Solution

by:Bill Bach
Bill Bach earned 250 total points
ID: 17788118
There are companies, such as my own, that specialize in Pervasive database work.  Changing the DDF's to remove the special characters can be done quickly and easily with the right tools.  Our older FSAVE and FLOAD tools may be able to do this for you, but they use special commands that were discontinued after PSQL7, so using them on PSQLV8 is not possible.  However, if the DDF's are in an older 6.x or 7.x format, then it should be possible -- if you have access to a PSQL7 engine.

If not, it shouldn't take too much effort to process DDF's to remove special characters -- probably not much more than an hour of development time to build the right tool.

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Table where row act as column 11 82
user database (login sql or login windows) 3 51
Need quicker response from an Execption table 11 43
Creating a View from a CTE 15 41
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question