INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I have to add my thanks and appreciation for your wonderful site... People who frequent the site are the two best things - nice and smart..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Project and Data Management > Data Management Programming issues > ODBC Forum

Known issues mixing linked tables with imported excel data in Access

Read More Threads Like This One

maxandy (Vendor)

12 Oct 07 12:05

All,

One of my colleagues has just asked me an interesting question.

Using Access, he has linked data from an ODBC compliant database. To this, he has added an imported Excel spreadsheet.

When running a query, he has noticed that it's taking a very long time. Now, the linked tables only contain about 65K rows of data and the imported sheet contains about 35K rows.

It shouldn't take a long time so, my question is - are there any known problems in combining linked ODBC tables and imported Excel sheets in Access?

Cheers

k5tm (Vendor)

12 Oct 07 12:54

The Access SQL engine does not have any cardinality information about the linked table, so it may be doing a very bad job of optimizing (optimising) the query. You very well may be doing 35K full table scans of the 65K row table.

Does the linked data have a unique index? If so, did your colleague specify this index when creating the link? If so, does the join specify the unique index in a way that is useful?

You can get a better understanding of what is happening at the ODBC API level by enabling ODBC tracing.

Access is notorious for this...

Tom Morrison
www.liant.com

genomon (Programmer)

12 Oct 07 12:54

Better to import the Excel data into a local table and index it or create a primary key. Probably slow due to the lack of either.

The early bird may get the worm, but the second mouse gets the cheese in the trap.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To ODBC