Link to Pervasive database

Posted on 2009-04-28
Medium Priority
Last Modified: 2012-08-14
I am trying to link a table in PervasiveSQL9.5  from access2003 through ODBC connection. Everything looks ok I can see tables then when I pick up one and click button to link I got error.

I think that error is related to pervasive table filed name or particular filed data type.

Is it possible that access ODBC connection can not link pervasive table or some data type filed can not be pulled in access.

I dont believe that pervasive table filed name allow some naming convention that access database not support.

Such  is  putting in table field name ( ?/., or similar characters.

If I go through pass-through query and pull just one or two filed from same table that have char data type my query run ok.

What it could be that causing this problem?

 I am able to link all tables from demo database in Pervasive 10. without  any problem.

Question by:Taras
LVL 15

Expert Comment

by:Simon Ball
ID: 24261484
i have never used this pervasive thingy... is there a specifi odbc driver for it that you need to install?

LVL 29

Expert Comment

by:Bill Bach
ID: 24262183
Microsoft Access is, surprisingly enough, NOT an ODBC-compliant application!  It processes data through its own database engine (JET), and this engine is vastly inferior to most other databases out there.  Also, many databases are much more flexible than JET, and it is indeed possible that table or field names in the Pervasive database will be unsupported in Access.

As you indicated, the pass-through query works when querying the table.  Howeverr, you never indicated the error you were receiving when you link normally.  Not having the reported error message is like asking a mechanic to fix your car without telling him what's wrong.  He might be able to fix it, but it might take a long time.  I am going to assume that you are getting a "#Deleted" or "#Name" error in the table itself.  If this is not correct, please post the real error and we'll try again.

If you see #deleted or #name in an Access/JET database, then your database may be using data types that are incompatible with JET.  This is commonly the case with COBOL data types like NUMERIC or DECIMAL fields.  (You can try the various tables in the DEMODATA database and see if any of these also fail with the same error, which might help narrow down which data type is being used.)  If that's the case, then using an ODBC Pass-through query may be your only option -- unless you can switch to an ODBC-compliant data access tool.

Expert Comment

ID: 24341568
What is the error that you get?

Why not just use Pervasive.SQL v10 if your testing show that it works.

Author Comment

ID: 24378536
Error is :Owner Apt./Suite is not a valid name. Make sure that it does not include invalid character or punctuation and that it is not to long.

Here I am dealing wiht rectricted characters as ./ in field's name.

I assume that this is Ms Access ODBC driver issue, as I am using ODBC through DSN to connect to database.
As I mentioned I can use pass through query to reach these fields in table.
However I need to link whole table.
LVL 29

Accepted Solution

Bill Bach earned 1500 total points
ID: 24378820
Might be a limitation of MSAccess itself that cannot be overcome without renaming the column in Pervasive.  With PSQLv10, you can possibly work around it with an ALTER TABLE statement, but as you indicated, you do run the risk of impacting the source application.

The best solution is likely to be to create a COPY of the data dictionary files (*.DDF) in an empty directory, link those DDF's to the original source data, and then rename the field in the DDF copy.  This will basically create a new database that has the "right" field names so that MSACcess can see it as it wants to.

The drawback with this is that whenever the application developer updates his DDF's, you'll need to re-copy and re-change the second set of DDF's, too.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

864 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