Linked Table

[drewcp]

I have several very large link tables in my database. they are linked to excel spreadsheets. whenever i am performing any action that requires the use of these tables (like creating a form, creating a query, running a query, etc.) it takes a ridiculously long amount of time (sometimes up to 30 minutes to run).

is there anyway that i can speed up this process? i have already tried relinking them but to no avail. i also tried to index them, but it states that indexing is not allowed on link tables.

here is the information on my excel tables

*every field is saved as text without any formatting
*each has roughly 275,000 rows and about 60 columns

Thank you in advance for anyone who can help me with this.

(i am still trying to build the database if the status changes the solution)

 

[Darrylles]

Hi drew,

My advice is: don't try to use spreadsheets as database tables - 'cos they're not!

I have the problem of always being presented with data in spreadsheets, but, I link to them only temporarily and import them into internal tables, unless it's an ad-hoc / one-off task.

Here are 2 options, but I would always opt for the latter:

1) Duplicate the spreadsheet structures in internal Access tables. Then, periodically link to the spreadsheets - compare records and import only the new rows into the internal tables.
This can be automated relatively easily.
2) Import the spreadsheets into internal tables, and have users update data into the Access tables - not the spreadsheets, via Access forms.

Regards,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.