Seek Method (migrating from Clipper ..) 
Author Message
 Seek Method (migrating from Clipper ..)

Hi all

I am new to VB6 (although I meddled with DB3 years ago) and
desperately need some help. I try to convert a Clipper application to
VB6 - or better recode the entire project. This application will run
on a stand-alone PC with an Access Database. Although I have Service
Pack 5 installed, I can't access Access 2000 through DB6's Visual Data
Manager but until I have that one sorted out, I just convert it to
Access 97 file format. If someone can come up with a solution for this
one, I would be grateful.

Sorry for this long preamble. Here now my main problem:

I connect to the database via an ADODC control using a connection
string: Provider=Microsoft.Jet.OLEDB.3.51;Persist Security
Info=False;Data Source=C:... etc. The connection works. To access the
table, I use
Dim RS As ADODB.Recordset
Set RS = Adodc1.Recordset

RS.Supports(adindex) returns True so I assume it would support an
index for a table.

Now, HOW do I open my index?  I tried RS.Index = "Rdskey" where Rdskey
is the name of the Access Index for the particular table but get
rewarded with a "Message or data member not found" error. Sure, I've
overlooked something very obvious (at least for the Gurus out there)
but am tearing my last few hairs out.

Once this is sorted out, I would appreciate a short example code for
the seek method. The sample I could find in MSDN refers to DAO 3.51
and doesn't work in my application. The book I mostly refer to
(Mastering Database Programming with Visual Basic 6 by Evangelos
Petroutsos) is very helpful, but not in regard to this problem.

This one got longer than anticipated but hopefully you bear with me.
Thanks for any suggestions.
Klaus



Thu, 22 Dec 2005 23:17:26 GMT  
 Seek Method (migrating from Clipper ..)
Access 2000 uses version 4 of the Jet database engine. When Visual
Data Manager (VDM) was written, the latest version of Jet was
3.something so VDM always uses Jet 3.51 to open an Access database.
This works fine for Access 95 and 97 databases but not for 2000 or
later. I suggest you keep the database in 2000 format and use Access
itself to play with the database as its interface and tools are better
than VDM. If you REALLY want to use VDM it's fairly easy to fix it to
work with Access 2000 instead of 95/97 but I gave up trying to get it
to work with both types. If you want to know the fix let me know and
I'll look it up.

You seem to be fixed on getting the entire table and then manually
looking for the record(s) you want. Perhaps that's what you have to do
in Clipper? With VB and a relational database (Access/SQL
Server/Oracle/Sybase/MySQL etc) it's much better to use SQL to
retrieve only the record(s) you want. You don't use indexes
explicitly. You state what data you want in SQL and the database
engine uses the database's indexes wherever possible to execute your
query more efficiently. As far as indexes are concerned your job is
simply to create the right indexes so the database engine will be able
to get you your data nice and fast.

Another strong suggestion: forget about the data control becase it's
pretty crap. Use SQL to get the data in a recordset and then load the
values into the form's controls yourself. If it's an update form,
validate the new values and execute SQL statements to update the
database.

Learn SQL: it is your friend. It's easy to do simple things in it, and
you can also use its more complex features to achieve some amazing
things. Almost every database engine supports it so it is a very
useful and portable skill. I like the SAMS Teach Youself books for
learning SQL.

Get a good ADO book. This stuff is pretty baffling until somebody
explains it properly and then it suddenly becomes fairly
straightforward. I have read about twenty VB database books and by far
the best is Serious ADO by Rob MacDonald (publ. Apress).

Quote:

> Hi all

> I am new to VB6 (although I meddled with DB3 years ago) and
> desperately need some help. I try to convert a Clipper application to
> VB6 - or better recode the entire project. This application will run
> on a stand-alone PC with an Access Database. Although I have Service
> Pack 5 installed, I can't access Access 2000 through DB6's Visual Data
> Manager but until I have that one sorted out, I just convert it to
> Access 97 file format. If someone can come up with a solution for this
> one, I would be grateful.

> Sorry for this long preamble. Here now my main problem:

> I connect to the database via an ADODC control using a connection
> string: Provider=Microsoft.Jet.OLEDB.3.51;Persist Security
> Info=False;Data Source=C:... etc. The connection works. To access the
> table, I use
> Dim RS As ADODB.Recordset
> Set RS = Adodc1.Recordset

> RS.Supports(adindex) returns True so I assume it would support an
> index for a table.

> Now, HOW do I open my index?  I tried RS.Index = "Rdskey" where Rdskey
> is the name of the Access Index for the particular table but get
> rewarded with a "Message or data member not found" error. Sure, I've
> overlooked something very obvious (at least for the Gurus out there)
> but am tearing my last few hairs out.

> Once this is sorted out, I would appreciate a short example code for
> the seek method. The sample I could find in MSDN refers to DAO 3.51
> and doesn't work in my application. The book I mostly refer to
> (Mastering Database Programming with Visual Basic 6 by Evangelos
> Petroutsos) is very helpful, but not in regard to this problem.

> This one got longer than anticipated but hopefully you bear with me.
> Thanks for any suggestions.
> Klaus



Sat, 24 Dec 2005 16:17:04 GMT  
 Seek Method (migrating from Clipper ..)
Thanks for your very good advice David, also your recommendations for
literature - much appreciated. I think you helped me getting on the
right track before I got too involved in doing the wrong things. Will
defenitely giving your suggestions a try and get involved in SQL.

Regards
Klaus


Quote:

>Access 2000 uses version 4 of the Jet database engine. When Visual
>Data Manager (VDM) was written, the latest version of Jet was
>3.something so VDM always uses Jet 3.51 to open an Access database.
>This works fine for Access 95 and 97 databases but not for 2000 or
>later. I suggest you keep the database in 2000 format and use Access
>itself to play with the database as its interface and tools are better
>than VDM. If you REALLY want to use VDM it's fairly easy to fix it to
>work with Access 2000 instead of 95/97 but I gave up trying to get it
>to work with both types. If you want to know the fix let me know and
>I'll look it up.

>You seem to be fixed on getting the entire table and then manually
>looking for the record(s) you want. Perhaps that's what you have to do
>in Clipper? With VB and a relational database (Access/SQL
>Server/Oracle/Sybase/MySQL etc) it's much better to use SQL to
>retrieve only the record(s) you want. You don't use indexes
>explicitly. You state what data you want in SQL and the database
>engine uses the database's indexes wherever possible to execute your
>query more efficiently. As far as indexes are concerned your job is
>simply to create the right indexes so the database engine will be able
>to get you your data nice and fast.

>Another strong suggestion: forget about the data control becase it's
>pretty crap. Use SQL to get the data in a recordset and then load the
>values into the form's controls yourself. If it's an update form,
>validate the new values and execute SQL statements to update the
>database.

>Learn SQL: it is your friend. It's easy to do simple things in it, and
>you can also use its more complex features to achieve some amazing
>things. Almost every database engine supports it so it is a very
>useful and portable skill. I like the SAMS Teach Youself books for
>learning SQL.

>Get a good ADO book. This stuff is pretty baffling until somebody
>explains it properly and then it suddenly becomes fairly
>straightforward. I have read about twenty VB database books and by far
>the best is Serious ADO by Rob MacDonald (publ. Apress).


>> Hi all

>> I am new to VB6 (although I meddled with DB3 years ago) and
>> desperately need some help. I try to convert a Clipper application to
>> VB6 - or better recode the entire project. This application will run
>> on a stand-alone PC with an Access Database. Although I have Service
>> Pack 5 installed, I can't access Access 2000 through DB6's Visual Data
>> Manager but until I have that one sorted out, I just convert it to
>> Access 97 file format. If someone can come up with a solution for this
>> one, I would be grateful.

>> Sorry for this long preamble. Here now my main problem:

>> I connect to the database via an ADODC control using a connection
>> string: Provider=Microsoft.Jet.OLEDB.3.51;Persist Security
>> Info=False;Data Source=C:... etc. The connection works. To access the
>> table, I use
>> Dim RS As ADODB.Recordset
>> Set RS = Adodc1.Recordset

>> RS.Supports(adindex) returns True so I assume it would support an
>> index for a table.

>> Now, HOW do I open my index?  I tried RS.Index = "Rdskey" where Rdskey
>> is the name of the Access Index for the particular table but get
>> rewarded with a "Message or data member not found" error. Sure, I've
>> overlooked something very obvious (at least for the Gurus out there)
>> but am tearing my last few hairs out.

>> Once this is sorted out, I would appreciate a short example code for
>> the seek method. The sample I could find in MSDN refers to DAO 3.51
>> and doesn't work in my application. The book I mostly refer to
>> (Mastering Database Programming with Visual Basic 6 by Evangelos
>> Petroutsos) is very helpful, but not in regard to this problem.

>> This one got longer than anticipated but hopefully you bear with me.
>> Thanks for any suggestions.
>> Klaus



Sun, 25 Dec 2005 22:26:05 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Help migrating from CLIPPER to VISUAL BASIC

2. SEEK Method/FindFirst Methods

3. Seek & find methods

4. Seek Method isn't working...

5. SEEK method w/truncated criteria

6. seek method

7. Field Name in Seek Method

8. Linked tables and the seek method

9. Problems with the methods Index and Seek

10. case sensitivity with seek method

11. Using Seek Method in VB6

12. Understanding the SEEK Method

 

 
Powered by phpBB® Forum Software