Slow Database, Need Indexes? 1

[JOEYB99]

I'm using Access 2007 and have developed a database where almost all tables are linked.

The performance is very slow for the main report I generate in this database. I think I need to create indexes. How do I do this?

Or, if anyone has any other suggestions that will help the performance I am 'all ears'?

 

[jedraw]

Try this

http://www.techonthenet.com/access/tables/indexes2007.php

 

[JOEYB99]

Thanks jedraw for the quick response.

However MS Access does not allow the changing of table properties for linked tables. That is why I mention the fact that my database has several linked tables.

 

[dhookom]

You simply open the back-end database that contains the table and add the indexes.

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

Thanks Duane, but I have a single database here with all the objects in it.

Should I start the database splitting wizard to create a front-end and back-end database?

Following this would I be able to create indexed fields on the linked tables in the back-end database?

 

[PHV]

Are you sure you can't add a key to an linked table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thanks PHV but I just tried that and got the message "...can't save property changes to linked tables".

 

[dhookom]

JOEYB99, you stated "almost all tables are linked" but then you state "I have a single database here with all the objects in it". These statements can't both be true. Please provide accurate information about your setup. What is you definition of a linked table?

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

Okay Duane, I guess I am mis-using or misunderstanding the terminology. Sorry for any confusion.

There are a total of five tables. One is created by the user through an entry form. Another table is imported from a CSV file. The other three tables are CSV files that the database is linked to. They are stored/archived on a network drive which I have linked through to the database.

All of the tables, forms, queries, reports and one macro are in this single database.

 

[dhookom]

You don't typically create a table through an entry form. You can add/edit/delete records in a table from a form. The table can be an Access table or a table linked from some other source.

If you imported CSV table is truly imported into the Access database, you should be able to create indexes on fields. The other three linked CSV tables can't have indexes created. You could possibly append the linked CSV tables into Access tables where they could be indexed.

Duane
Hook'D on Access
MS Access MVP