VBA Access via ODBC 1

[goolawah]

I have a simple Access application "Paxar to Pastel" which has two tables linked to external data via Pervasive ODBC.

I have set up a Pervasive ODBC Data Source called "Pastel" (Read Only)

It works fine on my system back at the office which I have set up to replicate, as closely as possible, the system on the cliet's site.

On the client's site, if I try to open (to view) the linked table in my application it returns a message
"ODBC---Connection to Pastel failed"
Clicking OK brings up another message
"Paxar to Pastel can't open the table in Datasheet view"
That's it!

However,

if I create a new dummy table using the same data source it opens fine, and I can then open both of the other tables. If I then close and re-open my application, none of the tables are accessible.

I can open the same ODBC data source without problems from EXCEL using Import External Data | New Database Query

I am wondering if there is something further I need to do in the Pervasive ODBC setup, or in my Access application, to overcome this (Permissions or something?)?

Thanks for help with my previous posts on this topic, which have helped me to get this far. Any further suggestions greatly appreciated.

 

[mirtheil]

Are you able to access the original tables through the PCC at the client site?
Is PVSW\BIN in the system path?
Are you installing the proper PSQL engine/client at the client?
Where is the data located in relation to the Access application? Where is the PSQL engine in relation to the Access application?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[goolawah]

Thanks mirtheil for your response.

Yes I can open the tables with PCC and view the contents.

I assume PVSW\BIN is in the system path and the proper PSQL engine/client is in use as the main application (Pastel Accounting) using Pervasive works fine. Otherwise I am not sure how to check this.

We are running
Database Service Manager for Workgroup Engine
8.10.121.055
Pervasive SQL V8 SP1

The data is stored on the server via a mapped drive P:/ on the work-station where both my Access application, and the main (Pastel) application, are running. This machine is also set as the default gateway for Pervasive. This machine has the Pervasive workgroup engine installed and running.

Other than that additional information, the problem remains as described above.

Any further help appreciated.

 

[mirtheil]

At this point, I would suggest turning on ODBC tracing, generate the error, then search the resulting trace file and post the error here.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[goolawah]

Thanks mirtheil

I have done as you suggested and posted the resulting trace file on my web-site. (link below)

Trace File

Your further input would be greatly appreciated.

 

[mirtheil]

That's a MKDE trace. You should turn on ODBC tracing (in the ODBC Administrator not the PCC).
I don't see anything in the MKDE.TRA file that would indicate an error.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[goolawah]

Thanks for your help in discovering these tools.

I visited the client's site again this morning and used the ODBC Tracing. It generated this file - sql.log

I don't have the knowledge to analyse this information and would appreciate help.

Meantime I will repeat the process back in the office (where it works), generate the same file and compare the contents to see what I can learn.

Thanks again

 

[mirtheil]

This seems to be the error:
[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Cannot locate the named database you specified(Btrieve Error 2301)

This is caused by a DSN that's not pointing to the proper Database Name. Make sure the DSN is pointing to the same Database Name as one of the machines that's working..


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[goolawah]

Thanks for that,

I note that the first column in sql.log contains "paxar"
Is this the DSN name?

I have used this DSN name before but in this application I thought I was using another DSN "Pastel", which I have pointed to the correct dabase. If it is using "Paxar" as the DSN the database could be anything at the moment on site.

When I open the table(s) successfully back at the office, with trace on, the sql.log file still shows "Paxar" in the first column but there is (no longer) a dsn of this name because I deleted it some time ago.

This leads me to wonder where it is getting he "Paxar" reference from?

 

[mirtheil]

Paxar" in the first column is the application name. It is not necessarily the DSN/Database Name. It may be but it may not be. You'll need to talk to the vendor of the applicaiton.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[goolawah]

Thanks for clarification. My application is Paxar.mdb.

Back in the office I deleted all related ODBC Data Sources and all related Pervasive named databases. I then re-created a Data Source and Named Database the same as I have on the client's site. Now the error also occurs in the office so I now don't have to go back on-site to test.

In my little Access application I have two tables - Inventory and MultiStoreTrn - linked to data files in the main "host" application. the error occurs if I try to manually open either of them in Data Sheet View.

If I create a duplicate of the table Inventory (either one will do), and link it in the same way as the others, I then have three linked tables and any of them will open in Data Sheet View. (Until I close and re-open my application)

This suggests to me that the Data Source and the Named Database are correct.

I have printed off the trace files after both successful and unsuccessful attempts to open the tables. They are both the same until a section headed -

ENTER SQLDriverConnectW

This section ends differently depending on whether it was successful or not.

The last line of this section is
- if successsful
UWORD 1 <SQL_DRIVER_[Red]COMPLETE[/Red]>


of this section is
- if UNsuccesssful
UWORD 1 <SQL_DRIVER_[Red]NOPROMPT>[/Red]

The ONLY difference is that, in the successful one I have created a duplicate linked table before opening any tables.

Is is possble that there are some settings to do with the ODBC Data Source or the Pervasive Named Database that I am missing?

I am completely at a loss and would appreciate any suggestions.

 

[mirtheil]

Access stores all of the connection string information on Linked Tables. If that information changes, Access does not update it's values but fails.
Make sure your ODBC DSN and Pervasive Database Name are the same as when you linked the tables.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[goolawah]

Ahaah! That explains a bit. Thanks. I wondered about that but it seemed redundant for Access to store those details, and I had no idea where to find the information anyway.

As a result, I have progressed but still short of my objective.

I decided to delete the two tables and re-create them using the current ODBC DSN and Pervasive Database Name. This worked for one table (Inventory) but persistently fails for the other (MultiStoreTrn). When I try to create a new linked table I get an error

I can open both of the files in PCC -

Databases(Engine)
Pastel
Tables

- with no problems so it seems as though it is Access that doesn't like it.

I wonder if there is somewhere else in Access that I should/could edit details about these linked tables?

 

[mirtheil]

Do the tables pass a database consistency check from within the PCC (right click the database name and select "Tasks" then "Check database")?
If not, you'll need to address the failures.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[goolawah]

Many thanks mirtheil for your help. My small add-on application as now working on-site. The consistency check reported plenty of failures which I have still to investigate and resolve, but it is most likely these are to do with the main application over which I have no control. I managed to get it working OK by starting from scratch on my development machine, defining new ODBC DSN and Pervasive Named Database after deleting all the old ones that arose from my development project.

I then went to the client's site and repeated this process and all worked fine.

Thanks again!