Solved

Pervasive SQL v11 - ODBC setup

Posted on 2015-01-26
13
429 Views
Last Modified: 2015-01-30
Hello - where can I find step-by-step instructions for setting up a Pervasive SQL v11 ODBC DSN on Windows 7 ?

(the Pervasive SQL setup itself came along with the install package of an accounting package called CYMA (version 12), and I am trying to access this database via ODBC with Microsoft Access 2003)

Thanks
0
Comment
Question by:mlagrange
  • 7
  • 5
13 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 40570426
Check out http://docs.pervasive.com/products/database/psqlv11/wwhelp/wwhimpl/js/html/wwhelp.htm#href=uguide/using.02.5.html.  This is the documentation for the PSQL engine and shows the various ways an ODBC DSN can be created.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40570538
An alternative (with screenshots) can be found at: http://www.goldstarsoftware.com/papers/CreatingAnODBCDataSourceForPSQLv11.pdf.

Note that for either, you MUST have data dictionary files for your CYMA environment, preferably in the same location as your database files.  If you don't have DDF's, then you may need to contact CYMA to get them.
0
 

Author Comment

by:mlagrange
ID: 40570796
Thanks for your response.
I'm having trouble right off the bat; can you tell me which installation I should select:

Server 32-bit
Server 64-bit
Workgroup
Client

I am a Microsoft Access developer, linking to a few tables in this Pervasive SQL database.
I am running Access 2003, on Windows 7 (32-bit)
I don't need the CYMA application on my workstation; I just need to link to the PSQL tables

Thanks again
0
 

Author Comment

by:mlagrange
ID: 40570865
ok, I've tried installing as "Server (32-bit)" and "Workgroup"

With the Server 32 install, when I get through the ODBC dsn setup and click the "Test" button, I get a Btrieve 35 error (Btrieve file directory is invalid)

With the Workgroup installation, when I get through the ODBC dsn setup and click the "Test" button, I get a Btrieve 11 error (specified filename is invalid)

(between these attempts, I remove the databases through the Pervasive Control Center, then run the Windows Uninstall, then CCleaner)
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40570875
Assuming that the engine is on another computer, then you would install the PSQL Client onto your workstation, then create the ODBC DSN as a Client DSN that points to the engine.
0
 

Author Comment

by:mlagrange
ID: 40571085
ok, I did the "Client" install, but to make an ODBC connection after that, I have to point to the database, and clicking either the "Create" or "Get List" buttons just gets me a pop-up saying "cannot connect", even though (it looks to me like) the process is active.
ODBC-cannot-connect.jpg
Process-properties.jpg
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
ID: 40571175
I think you may be getting a bit confused.  Here is how this setup should be working:
Your Application (Access) -> ODBC Drivers -> PSQL Client -> PSQL Engine -> DDF's -> DataFiles

The screenshot in your last message indicated that the PSQL Client Cache Engine is running.  The CCE is a client-side component that caches data, but it is not a "true" engine that allows for database access.  

There are two possible ways to configure things:
1) From your initial request, I assumed that you had an installation of CYMA on an existing computer on the network.  This computer will be called the "server" in this case, and it MUST have a database engine running on it.  In this case, your Access system needs only the PSQL Client installed.  You would FIRST configure the database on the server (using the Pervasive Control Center).  This is the first page of the PDF I posted above.  Once this is configured, you can THEN create the ODBC DSN on the Client, which points to the one on the server.
2) From your most recent posts, it sounds like you are trying to access data on the local HDD instead, if you found the CCE.  For this configuration, because the data is local to the computer, you DO need an engine installed.  You can use either the Server Engine or the Workgroup Engine, but either will need to be licensed beyond the 30-day trial.  In this case, treat the local computer as the "server" (because this is where the engine is located) and follow the same directions.

You may find it easier to start with the Pervasive Control Center.  The PCC allows you to create the database (and DSN's), and you can use that tool to validate that everything is working before you bring ODBC into the mix.  Simply right-click the computer which has the engine (you may need to add it manually first) and then select New/Database.  Enter the database name and select the database (DDF) location, and click OK.  Then, try to access the data from here.  If this fails, then we can troubleshoot the PSQL side of things, and at least know that the issue is not related (yet) to ODBC itself.
0
 

Author Comment

by:mlagrange
ID: 40571420
Thanks, Bill - I am trying to get a license key from the guys in the IT group.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40571427
You may not need the license right away, *IF* the 30-day trial is still active.  Depending on when you embarked on this journey, the trial may still be active.  Check the Pervasive License Administrator (Uncheck the "Hide Expired Licenses" button) and see if there is one that has not yet expired.  That would at least give you a workaround for now.
0
 

Author Comment

by:mlagrange
ID: 40571484
ok, thanks again for your help.
I installed the entire accounting package, and I still can't connect.
(I'm going to have to get the IT guys to finish off the installation; when I start up the accounting app, I'm being prompted for user id, etc, and my network ID does not work)

I am done here for the day; I'll be back on this tomorrow.

Thanks again
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40571517
OK.  If you have the entire accounting package, then it likely came with the Workgroup Engine.  However, I'm not a CYMA expert, and I don't know for sure.  If that is the case, then YOUR machine will need the engine (and license), and YOUR machine will be considered the "server" in this case.  

An easy way to check -- go into the PCC and open up the local machine name.  You should find "Databases" under that.  DemoData is one pre-defined database, and you should be able to open this from within the PCC.  Once this works, you should ALSO be able to open DemoData from MSAccess as a test, too.  This might be another way to determine where things have gone awry.
0
 

Author Comment

by:mlagrange
ID: 40571532
Great - thanks very much; I'll try that out.
0
 

Author Closing Comment

by:mlagrange
ID: 40580063
Thanks for all your help!

Sorry I took so long to get back; this client got hacked the next day, and I got tied up between that, and finishing what I was working on at the time.  

I was definitely confused about PSQL Client v. Engine; I thought it was an either/or thing.

Thanks again!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

743 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

10 Experts available now in Live!

Get 1:1 Help Now