index searching on Access indexed tables

[jON Rowlan]

I am slowing get my head around the concept of treating tables as
collections of rows as vb.net seems to prefer.

However, I have an indexed table in an Access database that I want to scan.

I want to match a certain field but don't want to issue one select statement
for each row in my master table.

I'd rather connect the table to an object and then use a seek or locate
command to get to the correct row that I want.

I can't use a join because it is really slow and an indexed lookup on a
dbtable would be best.

I can't even work out where to start ...

there don't seem to be any methods to cause a dbtable to locate a record
based upon an index item.

can anyone provide some basic VB that I can research further please?

Many thanks all,

jON

 

[Guest]

I am slowing get my head around the concept of treating tables as
collections of rows as vb.net seems to prefer.

ADO does this too.

However, I have an indexed table in an Access database that I want to
scan.

I want to match a certain field but don't want to issue one select
statement for each row in my master table.

You would use a JOIN.

I can't use a join because it is really slow and an indexed lookup on
a dbtable would be best.

A JOIN should use your index if it has been setup correctly.

I can't even work out where to start ...

there don't seem to be any methods to cause a dbtable to locate a
record based upon an index item.

That would be a join

 

[Cor Ligthert[MVP]]

Jon,

You can use any kind of SQL statement that results to a resultset.

Using that with a datatable will be nothing more then

dim dt as new datatable
dim conn as new oledbconnection(......assuming you know how to do that
dim selectstring as string = "Select blabla from bla where x = y")
dim da as new oledbdataadapter(...........assuming you know how to do that
da.fill(dt)

(using oledbparameters is better, however to show you the sample)

However as you are talking about searching in a datatable, then you have at
least 7 posibilites to find, seek, filter or select what you need and with
much more possibilities then by instance the recordset from the previous
millenium.

Beside that is of course LINQ what is in fact build around this.

The code is completely typed in this message meant as a kind of pseudo code

Cor

 

[jON Rowlan]

Thanks all,

But as I said before, I cannot use a join.

This is because of a quirk of Access that means that adding a simple join to
my already quite complex select statement, the resulting dataset takes ages
and ages to complete. As the MDB I am working with is comes from a third
party I can only read its data I cannot make changes to the way it works,
indexes, table structures or relationships.

A select statement written in Access itself demonstrates the same behaviour
and believe me the select statement is not where the fault lies.

I used to be able to do something like ...

employee.indexname="Primary"
employee.seek "=",112

that is very much cleaner and distinct to me than the 5 lines of code that I
need to execute for each of my master rows.

The above method requires the table to be opened once, closed once, Index
set once and then for each master row I simply need to do a seek and
possibly a NoMatch check (which I would have with whatever method I use).

Am I being advised that this functionality is no longer available?

I don't know what LINQ is I am afraid.

Many thanks all,

jON

 

[Armin Zingler]

I used to be able to do something like ...

employee.indexname="Primary"
employee.seek "=",112

ADO.Net got much worse (slower) in these things. Unfortunatelly nobody cares
because "buy a faster machine" is the answer.


Armin

 

[jON Rowlan]

Hey, I just found out what LINQ is ...

It seems that in developing .NET MS managed to massively overcomplicate even
the simplest of function ...

I mean, where does all this adapter.fill stuff really come from? Whats wrong
with a single recordset you can traverse easily with a few statements ???

So, because it now takes around 20 statements to do what you used to be able
to do with 2, there is a need for a new technology to simplify it all.

And the man for the job is my old friend Anders ...

I'm going back to Delphi - Anders hasn't been there for a while so I still
understand it

seriously though, thanks all, I will use a clunky select statement using
adapters and fill etc etc

jON