Dlookup is slow. other ideas to pull DB data in?

[techexpressinc]

I have to pull a few fields from another DB (DB2) on to some screens where i am updating the data pulled for the display of data on a screen.

I do it with a query joining the data from the other DB (DB2) with the table from the DB i am updating (DB1), the update process will not work.

I only need the data from the other DB (DB2) for display purposes. It works with using the DLookup process to get the fields. But is dog slow! So slow that doing a search on a DLookup field is next to impossible with response of 5 minutes. The DB-s are not large < 10k records in the tables.

Any great speed-up ideas would be appreciated.

Thx
Russ

 

[jzwp22]

You could link the pertinent table(s) from DB2 into DB1, then you can use the linked table as any other table.

 

[techexpressinc]

Re: tried that and if fail to let me update tbl DB1

The form asks for a data source. If I use a query with the a table in DB1 linked to a table in DB2 as the source, then I am unable to update the fields in the table in DB1. Access blocks the updates.
Russ

 

[boblarson]

Check this page out by Access MVP Allen Browne on DLookups.

 

[jzwp22]

What version of Access are you using?

Is the second database an Access database?

The form asks for a data source.

To what form are you referring?

To link a table in another Access database to your current Access database, go into Tables-->New-->Link Table

If I use a query with the a table in DB1 linked to a table in DB2 as the source, then I am unable to update the fields in the table in DB1

Can you provide the SQL text of the query you refer to and the structure of the two tables involved?

 

[vbaInet]

You could use a hidden multi-column listbox. Change it's row source on the fly (using the criteria you're currently using on your DLoookup), perform a list count to see if a record exists then select the first row's record.

A hidden subform could do too.

 

[techexpressinc]

Access 2003. The form as the source of a query with one table going into it. There are a few fields from another DB being displayed at the top of the form. I am using DLookup to get them. You can see the DLookup pulls in the data very slowly.

I am thinking changing to an elookup based on Bob L.'s hyperlink..
Russ