Two field Index

[Mike P]

I have a table that has two fields, CusID (text) & PurchaseDate (date), I
would like to use for searching. I am getting security errors on the Seek,
so I seem to be forced to use Find… which can only handle a single field not
two. Also, I am getting a type mismatch on trying to build the index since I
am attempting to combine a text and date field.

I realize I can do a SQL on the CusID, then a Find!PurchaseDate on the date.
This is just taking much longer and is more combursome than if I could just
do a Seek CusID & PurchaseDate.

Can I create a two field index on them and then do a seek command??? If so,
can you please provide me the syntax.

Thx,
Mike P.

 

[Dirk Goldgar]

I have a table that has two fields, CusID (text) & PurchaseDate (date), I
would like to use for searching. I am getting security errors on the
Seek,
so I seem to be forced to use Find… which can only handle a single field
not
two. Also, I am getting a type mismatch on trying to build the index
since I
am attempting to combine a text and date field.

I realize I can do a SQL on the CusID, then a Find!PurchaseDate on the
date.
This is just taking much longer and is more combursome than if I could
just
do a Seek CusID & PurchaseDate.

Can I create a two field index on them and then do a seek command??? If
so,
can you please provide me the syntax.

What sort of errors are you getting on the Seek? I can't think why you'd
get a "security error". What does your code look like? Be aware that you
can only use the DAO Seek method on a table-type recordset, and the ADO seek
method on a server-side cursor.

Are you using DAO or ADO?

If you use the ADO Find method, you can only search on a single field, but
if you're using the DAO Find... methods, they are not so limited.

Frequently it's quicker just to open a recordset directly on a query (or
inline SQL statement) that specifies all the field criteria you want to
apply. Did you try that?

 

[Mike P]

I am using ADO. I thought this was prefer after doing some research prior to
programming. Is DOA better to use? The program I am writting will get large
tables, and has some complex algorithims.

Yes, I am building the SQL statement to handle the searching by returning
the record set that meets the multiple field criteria. I thought that
opening and closing a recordset with each criteria search I wanted would be
much slower than having an open set and performing multiple seeks (or finds)
on it.

I am looking for the cleanest way to process 100s of records in a timely
fashion that is scalable as this will get up to 100,000 of rows.

Let me know what you think and thanks for the help!
Mike P.

 

[Dirk Goldgar]

I am using ADO. I thought this was prefer after doing some research prior
to
programming. Is DOA better to use? The program I am writting will get
large
tables, and has some complex algorithims.

This is a much-debated, often hotly-debated, topic. If you are using a Jet
database (.mdb or .accdb file), and expect to continue doing so, then DAO
will be more efficient and more flexible. If you expect to transition to a
SQL Server (or other client/server) back-end, then the picture is murkier.
Note that your mention of scaling up to 100,000 rows doesn't imply that you
will even come close to the limits of Jet. What is most important is that
you have the search fields indexed in the table, and ideally, a multi-field
index on the combination of search fields you expect to search by.

How large are your "large tables" anticipated to be?

There are lots of considerations as to whether an Access-based application
should use the default Jet back-end, or one or another version of SQL Server
instead, but *if* you're planning to use Jet now and in the future, then DAO
is a better choice.

Yes, I am building the SQL statement to handle the searching by returning
the record set that meets the multiple field criteria. I thought that
opening and closing a recordset with each criteria search I wanted would
be
much slower than having an open set and performing multiple seeks (or
finds)
on it.

I'm inclined to think not, if these searches are performed as a result of
user action; for example, if the user types in some key values, then you do
the lookup, then the user types in new key values, and you do another
lookup. On the other hand, if you are doing frequent searches inside a
tight processing loop, it may be that using seeks on a single open recordset
will be more efficient. That's a rather unusual circumstance, though.

Is this to be a networked application? If so, in opening a large recordset
containing many (probably mostly) records you aren't interested in, you'll
be bringing lots more data across the network. That would probably offset
any savings you might gain by not closing/opening the recordset between
searches.

I am looking for the cleanest way to process 100s of records in a timely
fashion that is scalable as this will get up to 100,000 of rows.

I see that you've given the matter considerable thought. I don't know
enough about your application to advise you in more than generalities. With
more information, I might be able to be more specific.