supports(adIndex) = False

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working with a table that has a key and shows the index as being applied to that unique key. But in my VB program Find and Seek are not available because the index is not supported for any type of recordset that I define. What have I done or not done to set up this situation?
 
Denise:

You have to use for your recordset

adOpenKeyset, Your Lock Options, adCmdTableDirect

to be able to use an index.

You may also have to use a client side cursor
 
I am working with a table that has a key and shows the index as being
applied to that unique key. But in my VB program Find and Seek are
not available because the index is not supported for any type of
recordset that I define.
Click to expand...

You don't say whether you are using ADO or DAO. Since you've had an ADO
response, this is the DAO version!

You can't use .Seek and .Index methods on anything other than local Jet
tables -- in fact they were only ever comforts for dBASE programmers who
took time time to swap over to the relational model. The recommended
mwethods now are

a) .FindFirst and .NoMatch or,

b and better) use as SQL query to select the correct records in the correct
order first, and then you don't have to search the recordset at all!

Hope that helps


Tim F
 
OK, this is helping (I am in a learning mode). "local Jet tables", are what ? Access db tables, yes?

I changed the application from an OLE Access.application to ADO specifically to use the seek method since the program will be look-up intensive. I considered a multi-dimentional array but feared that it would grow too large to be viable. I had hoped to keep this simple, but if I must go to SQL querys then I probably will leave Access and go to the free MySQL

Thanks
 
OK, this is helping (I am in a learning mode). "local Jet tables",
are what ? Access db tables, yes?
Click to expand...

Tables local the the mdb that the code is running in. If you are really
using VB (rather than VBA) then there are no such things. That is why I
mentioned it.
I changed the application from an OLE Access.application to ADO
specifically to use the seek method since the program will be look-up
intensive.
Click to expand...

OLE Automation of the entire Access App is way over the top -- VB is
designed to connect directly to a jet mdb, using one of the access
libraries, ADO/ ADOX or DAO. For my money, there is no reason to use ADO
with Jet because it's limited and incomplete; DAO gives you more
functionality and is much easier to program. ADO is vital for ODBC and SQL
Server etc; but that is not what you suggested. Still, it's horses for
courses and you are better off using a tool you know well than one you
don't.
I had hoped to keep this simple,
but if I must go to SQL querys then I probably will leave Access and
go to the free MySQL.
Click to expand...

Sorry, you just cannot do anything sensible in any modern database
environment without using SQL -- this has been the great advance in desktop
databases over the last twenty years. The advantage of using Access/ Jet is
that it helps you write the SQL so you don't have to know very much. VB
always used to come with a query design tool and I assume it still does.

Think of trying to look up the mileage of one particular car in a dealer's
database. You can either set up a recordset consisting of all 33 fields and
read in all 45,000 records to find it; or you can issue a one-line SQL
command and get back _one_value_. Which one do you think your network
manager would prefer?

Going on, I couldn't even begin to think about a table-recordset-oriented
way of answering something like

"A list of all children who have complained of bullying, and
for each child, the names of all children in the same class
who are bigger than them."

.... but I know how I would start to write the SQL!

Moving from Jet to MySQL will certainly mean ADO.

Hope that helps

Tim F
 
Denise:

1.) What is the backend database that you are using? Are trying to address
a simple Access table or are you linking to some other data table such as
SQL Server, Orable, My SQL?

2.) Is there a unique index on the table? I didn't see that you specified
it as in

rst.Index = "YourIndex"
 
Tim:

While I am sure that Denise can appreciate your passion about using DAO (yes
it is more flexible with Access (jet) tables). However, the operation she
is trying to achieve using ADO really should be quite simple and there's no
reason to push a DAO conversion to do a simple seek operation. In fact in
your previous post you recommended using a DAO find method which is normally
slower than a seek operation when using a primary key index.

The issue Denise is facing has something to do more than likely with the
actual table index or something related to the version of the MDAC library
that is on her machine.

Secondly, since VB 6, VB is designed to connect to any data source using
ADO. It is not Jet centric.

Third, ADO is not vital to ODBC. They are two totally different
technologies for data access to multiple data sources and do not even
interact. ADO (and its underlying OLE DB) happens to be designed to be much
more flexible accessing different types of data sources than ODBC.

Fourth, you can do most stuff in SQL Server using DAO pass through queries
if you want to use DAO, because you can run processes on the server that way
rather than locally speeding things up dramatically by reducing network
traffic. Its just if you want to do things like Seeking on record set etc,
that ADO would shine with SQL Server vs. DAO, because seek is not supported
in DAO with link SQL tables.

Fifth, going the MySQL route would NOT require using ADO. It WOULD require
using ODBC or JDBC (if you are into Java). AFAIK, there are no ADO
providers for MySQL.

I would suggest that you learn more about the subjects that you respond on
before you so passionately state incorrect information.

The one point that I agree with you on, and it is a good one, is that it is
normally much more efficient to use a simple SQL select to obtain the
information one needs rather than using a table seek, with the one exception
of reading against a unique (in the case of SQL server, a clustered) index,
where either would normally be about as fast.
 
Tim:

While I am sure that Denise can appreciate your passion about using DAO (yes
it is more flexible with Access (jet) tables). However, the operation she
is trying to achieve using ADO really should be quite simple and there's no
reason to push a DAO conversion to do a simple seek operation. In fact in
your previous post you recommended using a DAO find method which is normally
slower than a seek operation when using a primary key index.

The issue Denise is facing has something to do more than likely with the
actual table index or something related to the version of the MDAC library
that is on her machine.

Secondly, since VB 6, VB is designed to connect to any data source using
ADO. It is not Jet centric.

Third, ADO is not vital to ODBC. They are two totally different
technologies for data access to multiple data sources and do not even
interact. ADO (and its underlying OLE DB) happens to be designed to be much
more flexible accessing different types of data sources than ODBC.

Fourth, you can do most stuff in SQL Server using DAO pass through queries
if you want to use DAO, because you can run processes on the server that way
rather than locally speeding things up dramatically by reducing network
traffic. Its just if you want to do things like Seeking on record set etc,
that ADO would shine with SQL Server vs. DAO, because seek is not supported
in DAO with link SQL tables.

Fifth, going the MySQL route would NOT require using ADO. It WOULD require
using ODBC or JDBC (if you are into Java). AFAIK, there are no ADO
providers for MySQL.

I would suggest that you learn more about the subjects that you respond on
before you so passionately state incorrect information.

The one point that I agree with you on, and it is a good one, is that it is
normally much more efficient to use a simple SQL select to obtain the
information one needs rather than using a table seek, with the one exception
of reading against a unique (in the case of SQL server, a clustered) index,
where either would normally be about as fast.
 
Denise:

You don't say what version of Access you are using. If you are using
Access 2000 by any chance, then you might also want to make a few changes in
Access' configuration that may impact whether your code works or not.

1.) Access 2000 by default, references the Microsoft Active Data Objects 2.1
library. There were a number of bugs in that library and it has since been
updated through 2.5, 2.6, 2.7 and now on 2.8.

2.) To change the ADO library that is referenced, open any module and from
the Tools menu item, choose References. You'll see your ADO reference
checked at the top. Scan the list and see what the latest ADO library is.
Check the highest version available and uncheck 2.1.....
See if that helps.
 
While I am sure that Denise can appreciate your passion about using
DAO (yes it is more flexible with Access (jet) tables). However, the
operation she is trying to achieve using ADO really should be quite
simple and there's no reason to push a DAO conversion to do a simple
seek operation.
Click to expand...

I wasn't really trying to proselytise for DAO -- but I _was_ trying to
steer her away from using Index and Seek as a method of database access.
In fact in your previous post you recommended using
a DAO find method which is normally slower than a seek operation when
using a primary key index.
Click to expand...

In the previous post I said that FindFirst would work, but recommended a
proper SQL query. I have not used Seek since Access 1.1, and although I
have used FindFirst I can't remember the last time.
I would suggest that you learn more about the subjects that you
respond on before you so passionately state incorrect information.
Click to expand...

I bow to your knowledge of more recent VB versions -- my last trip was VB5
which was indeed oriented heavily toward DAO and Jet. I was not intending
to sound passionate, except perhaps to push late 20th century database
methods rather than .Seek and its cousins.
The one point that I agree with you on, and it is a good one, is that
it is normally much more efficient to use a simple SQL select to
obtain the information one needs
Click to expand...

I think we are violently agreeing on this one!

All the best


Tim F
 
Back
Top