VBA function performance problem

[Debralous]

I have a local Access DB where I have a variety of fields; some I enter
in the DB directly and some I need to update programmatically with data
from other sources. The "other sources" are in three distinct
databases all on different servers. The person who set this up handled
this by simply updating one row at a time which means the function
makes and closes 4 separate db connections FOR EACH ROW....resulting in
the obvious performance problems. With only about 1500 records it's
taking over 30 minutes to update!

Before I start working on making this more efficient, I'm hoping you
all might have some suggestions as to the best method of attack. Can I
join the tables even though they have difference connections? I've
sketched out the configuration below:

Source 1 (3 fields)
Source 2 (4 fields)
Source 3 (1 fields)
Source 4 (3 fields)

Access DB )Containing fields from source 1 through 4 + data from
Form)

TIA

 

[Graham Mandeno]

Hi Debra (?)

When you say "join the tables", do you mean "link the tables"?

If so, then yes, you can link tables from as many different back-end sources
as you like. Then you can perform the updates as though the tables were
local and you should find a great performance improvement.

It might be further improved by opening one recordset on each of the backend
sources before you start the updates and then closing them all again when
you've finished.

 

[Albert D. Kallal]

You can write querys, and join on different datasouces (linked tables).

the 1# thing you can do to fix the performance loop is to NOT create a
reocrdset inside of a loop.....

And, of course as you mention, don't close any of those tables during this
process....

You should be able to get this process down to 10-20 seconds by just doing
the above.....

joins from differnt tables are also quite slow, but if this lets you advoied
opening/closing a recordset as a result, then it is a good trade off...

However, if you can avoid the joins from differnt tables...do so....