Link to Pervasive database

Posted on 2009-04-28
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
    i have never used this pervasive thingy... is there a specifi odbc driver for it that you need to install?
    LVL 28

    Expert Comment

    by:Bill Bach
    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.
    LVL 2

    Expert Comment

    What is the error that you get?

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

    Author Comment

    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 28

    Accepted Solution

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now