Index a field

  • Thread starter Thread starter Guest
  • Start date Start date
Pretty sure NOT, and why would you want to do that? If you've set a unique
index that you need to periodically "relax", then you should rethink your
keys/indexes.

Setting a [LastName] field to a unique index to prevent duplicates is not
the way to handle that. Rather, index on [Lastname]+[FirstName], then use
some code to alert users to possible duplicates during data entry.

The BeforeUpdate event can be used to trigger a DLookup statement, that if
true, would display a MsgBox.
-Ed
 
Thanks for the response, Ed.
This is what I am trying to do and please tell me if there is a better way:
Each morning I import 2 ODBC tables using a macro. Once the tables are
imported I open the tables and choose a primary key field on one table and a
primary key field plus an 'Index-Duplicates OK' on another field in the
second table. The tables import with no indexes at all and the database
response is very slow. Once the primary keys and index field is set the
database response is great. It would be helpful if I could create a command
button to import the tables and set the primary keys and indexes with one
click of a button. Suggestions, please. Thanks.
--
Kat3n


Ed Robichaud said:
Pretty sure NOT, and why would you want to do that? If you've set a unique
index that you need to periodically "relax", then you should rethink your
keys/indexes.

Setting a [LastName] field to a unique index to prevent duplicates is not
the way to handle that. Rather, index on [Lastname]+[FirstName], then use
some code to alert users to possible duplicates during data entry.

The BeforeUpdate event can be used to trigger a DLookup statement, that if
true, would display a MsgBox.
-Ed


Kat3n said:
Can a field be set to 'index-duplicates ok' by using a macro?
Thanks,Kat3n
Click to expand...
Click to expand...
 
What you seek to do is beyond macro capability. You can however use DAO
code statements to import data and create an index/key. See the Access Help
for:
CreateIndex Method (DAO)

Have you considered linking to that outside data, not importing?
Additionally, ODBC response is considerably slower than formats that Jet can
handle natively (Access, Paradox, dBase/Foxpro). You could also have an
index set in your outside data before importing/linking.
-Ed


Kat3n said:
Thanks for the response, Ed.
This is what I am trying to do and please tell me if there is a better
way:
Each morning I import 2 ODBC tables using a macro. Once the tables are
imported I open the tables and choose a primary key field on one table and
a
primary key field plus an 'Index-Duplicates OK' on another field in the
second table. The tables import with no indexes at all and the database
response is very slow. Once the primary keys and index field is set the
database response is great. It would be helpful if I could create a
command
button to import the tables and set the primary keys and indexes with one
click of a button. Suggestions, please. Thanks.
--
Kat3n


Ed Robichaud said:
Pretty sure NOT, and why would you want to do that? If you've set a
unique
index that you need to periodically "relax", then you should rethink your
keys/indexes.

Setting a [LastName] field to a unique index to prevent duplicates is not
the way to handle that. Rather, index on [Lastname]+[FirstName], then
use
some code to alert users to possible duplicates during data entry.

The BeforeUpdate event can be used to trigger a DLookup statement, that
if
true, would display a MsgBox.
-Ed


Kat3n said:
Can a field be set to 'index-duplicates ok' by using a macro?
Thanks,Kat3n
Click to expand...
Click to expand...
Click to expand...
 
Back
Top