seriochka1
asked on
How to change a BTRieve field name in a table...
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?
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?
ASKER
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
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
ASKER
Also just to confirm....is there any way in MS Access to get around this problem and link the tables?
-Steve
-Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
Wayne Freeman
Analytica Business Systems
www.analyticabiz.com
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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