Accessing Btrieve using ODBCDirect

[jbrowne]

Hi,
I am trying to access a Btrieve database using ODBCDirect. I have used the folllowing code to do this;

Set wrkODBC = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)

Set sysdb = wrkODBC.OpenConnection("Connect1", dbDriverNoPrompt,False,"ODBC;DSN=ODSYSTEM;&quot

Set sImmsg = sysdb.OpenRecordset ("Immsg", dbOpenDynamic)

Immsg is the table that I want to open for read/write access.
My problem is that no matter what I do I can only open it as read only. I am using a Pervasive ODBC Engine Interface Driver.
Can anyone tell me what I’m doing wrong
Thanks in advance

John B
[sig][/sig]

 

[MichaelRed]

John,

Short answer to the question. Not I.

Next: What is the EXACT name of the Pervasive Driver? How is it instantiated? What is the date/time stamp on the file? [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[jbrowne]

Hi Michael,
The exact name is a &quot;Pervasive ODBC Engine Interface&quot; driver. The file name is w3odbcei.dll dated 24/04/00.
As far as instantiation is concerned, I am delaring my
'wrkODBC', 'sysdb ' and 'sImmsg' in a declaration area of the main module in the project.
This project is a convertion from VB3 to VB6. It used to access the Btrieve database thru an ISAM driver in VB3 but there is no ISAM driver for Btrieve with VB6. So I am trying to use the Pervasive (company who own Btrieve) driver and access thru ODBCDirect.
If you can't see a solution using ODBCDirect perhaps you may know of an alternative way. I am referencing DAO 3.51 at the moment.
Thanks for your interest and help

John B [sig][/sig]

 

[MichaelRed]

John,

As previously stated, &quot;Not I&quot;.

I am working on a similar &quot;problem&quot;, moving from Vb 1.0 (DOS!!!!!!!) to VB6. My 'original' interface was (and still mostly is) the direct API calls to the BTRieve engine. A large part of my problem is that the newer versions of BTRieve do not support &quot;overlapping&quot; indicies - which I blessd with (A LOT). In discussions w/ pervasive, it appears that almost anything I want to do requires:

[tab]updating the tables to ver 6.x (or PervasiveSQL)
[tab]redoing the &quot;table&quot; structures to remove the overlapping indicies
[tab]creating the ddf files for the new tables
[tab]writting custom functions to copy the old data to the new tables
[tab]connecting the new tables to an Ms Access database (as linked tables)
[tab]using the Ms Access database (links) to do actual data manuipulation

After this (trivial?) persuit, I MAY be able to get around to just doing the normal upgrades from DOS to Windows.

I know this doesn't really help, it just says where I am. The expectation - here - is that the Ms Access Links to the BTRieve tables will support using SQL/queries in Ms Access to accomplish the data operations on the BTRieve tables w/o to much overhead.

One last ?thought? the only reason I will 'stay' w/ the BTRieve files is to maintain compatabilty w/ a partner company which refuses to abandon BTRieve for their own internal reasons. Otherwise, there are to many other choices which are easier to deal w/ and many of them appear to be 'cheaper' than Pervasive.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[jbrowne]

Michael,
Its nice to know that I'm not the only one banging my head of a wall with this stuff !! I've got a little further since we spoke last. I have managed to get into editmode with the recordset from the Btrieve database still using ODBCDirect using the folllowing code;

Set wrkODBC = CreateWorkspace(&quot;ODBCWorkspace&quot;, &quot;&quot;, _
&quot;&quot;, dbUseODBC)

wrkODBC.DefaultCursorDriver = dbUseClientBatchCursor ' need to use this setting to gain update access !!

Set sysdb = wrkODBC.OpenConnection(&quot;Connect1&quot;, _
dbDriverNoPrompt, False, &quot;ODBC;DSN=ODSYSTEM;&quot 'Opens a connection to the 'ODSYSTEM' database

Set sImmsg = sysdb.OpenRecordset(&quot;Immsg&quot;, dbOpenDynaset, 0, dbOptimisticBatch) 'Opens Immsg table as a dynaset recordset

wrkODBC.BeginTrans ' Even used BeginTrans and CommitTrans in an effort to force the update

With sImmsg
.MoveFirst
message = !text '&quot;text&quot; and &quot;message_no&quot; are the 2 field names
message1 = !message_no
.Edit
!text = &quot;Need 6 chars&quot; 'editing the text field
.Update 'update the changes
End With

wrkODBC.CommitTrans 'force the changes onto the database

sImmsg.Close
sysdb.Close

This code is compiling and running without error. If I watch the variable 'Message', I see that it does change to the text - &quot;Need 6 chars&quot; and it does update but it only seems to update in cache memory. When I look at the database the update has not happened. The update and committrans methods run with no errors suggesting that the database has been updated but it actually hasn't - only cache updating has occured.
I don't know what else I can do to force the update. Again I have to ask the question - Is the Pervasive ODBC Engine Interface driver limiting update access to the database or is the Btrieve database itself actually updatable in this manner. The only other explanation is that my coding is wrong but I can't see where it is.

If you have any suggestions please let me know

Thanks,
John B
[sig][/sig]

 

[MichaelRed]

John B,

No specific help available here (at least not yet).

As a general rule, I follow the KISS ([red]K[/red]eep [red]I[/red]t [red]S[/red]imple - [red]S[/red]tupid) approach. In this case, this would mean - for me to remove EVERTYTHING not necessary (perhaps - as an example - the transaction statements).

The other thing would be to review the BTrieve tables for constraints (unique value fields, autonumber fields ...). In my never ending quest to make this work, I have even created NEW BTrieve tables - partally so I can be sure of what these constraints are!

You could &quot;try&quot; to get the pervasive/btrieve people to provide some help with the problem.

If you can access the BTRieve table(s), can you set up form(s) to display the contents - and verify they are actually reading the tables? If so, try placing the data in some databound controls and just (Manually) changing the values. Then &quot;scroll&quot; back and forth to see if they &quot;stick&quot;. This could provide you with some insight as to the actual connection. The &quot;MoveFirst&quot; command - suggests - that you already have some data in the db? You do not have any check for the &quot;empty data table (BOF = EOF = True), which could also tell you something about the table status.

Can you step through the code in debug mode and check other aspects of the recordset? RecordCount? Other field Values?

Can you get simple SQL statements to return records? information about the table?

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[corinaci]

Jb, I am interested if you solved this problem since I have it too ... smashed my brains thinking of it. Thank you for your answer !

Corina

 

[capnemo]

Corina
It's amazing hoe tough is to handle Btrieve!!. Actually I'm not a programmer but I'm stubborn and determined. Most probably you've already considered these issues but just to make sure,here it goes.
1.- Actual 32bit ODBC drivers only support Btrieve 6.15 and up.
2.- Previous 16 bit drivers support Btrieve 6.10 down.
3.- Seems that no 16-bit driver can work with 32-bit applications.
4.- Same happens with btrieve requesters as WBTRCALL.DLL.

So easy &quot;support&quot; answers are rather &quot;convert your previous version btrieve to Pervasive SQL 7.0&quot; or &quot;get back to your Windows 3.x system&quot; bottom line &quot;spend money!!!&quot;

Since I'm really stubborn I'trying to make an Excel 97 macro (32-bit) to accept an external call to a 16-bit requester (wbtrcall.dll).
Any ideas?
capnemo

 

[corinaci]

Hi, capnemo ! And thanks for being so quick in giving hints...

>It's amazing hoe tough is to handle Btrieve!!. Actually
Yeah, tell me about it ! I began reading about it 2 weeks ago and still didn't find a solution for writing data in a Btrieve db.

>1.- Actual 32bit ODBC drivers only support Btrieve 6.15 and up.
I knew about 16 and 32-bit drivers but my Btrieve version is 6.15. Maybe I didn't try hard enough.

>4.- Same happens with btrieve requesters as WBTRCALL.DLL.
Same version... 6.15... should I begin worrying for my IQ ?

>Since I'm really stubborn I'trying to make an Excel 97 macro (32-bit) to accept an external call to a 16-bit requester (wbtrcall.dll).
Any ideas?
Sorry, no. If it wasn't for this project, I wouldn't have had any interest in Btrieve. Maybe if this will develop, I will learn more and support you back... But till then good luck ! And keep being stubborn !

Corina

 

[MichaelRed]

John,

I MAY have at least a clue for your problem of updating the BTRieve tables. I have (finally) wended my way to the creation of the new BTR/MKD files and have actually populated them. It is somewhat curioous, but doing a static &quot;link&quot; to the files, I can &quot;Append&quot; records to the new BTR (Actually MKD - but they are the same thing!). However, I cannot 'Update') the files! In reviewing the Pervasive Docs, I note that they insist that the DSN link MUST be via a &quot;MACHINE DATA SOURCE&quot;. It states quite clearly &quot;Choosing a data source from the MACHINE data source ... is very important. ...&quot;

It is possible that your code

Set sysdb = wrkODBC.OpenConnection(&quot;Connect1&quot;, _
dbDriverNoPrompt, False, &quot;ODBC;DSN=ODSYSTEM;&quot 'Opens a connection to the 'ODSYSTEM' database

in refering to ODSYSTEM is causing your problem here.




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[vbajock]

Hae any of you tried the Smithware OCX's? Both 32 bit and 16 bit OCX's are included with their data access kit. They make Btrieve a breeze. Their stuff is at

http://www.classicsoftware.com

Kirk

 

[mirtheil]

Actually Smithware got bought by Pervasive and the &quot;Smithware&quot; OCXs are now part of the Pervasve.SQL SDK. Classic Software makes another set of OCXs but I wouldn't confuse the two..
mirtheil@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician