I have never some across this before but my database keeps throwing a "Too many tables open error" which has taken me a day to get to the bottom of. I believe that the use of a dlookup in one of the queries not cleaning up after itself.

The issue I have now is that even importing the tables into a new database causes this problem as i guess I'm inheriting the same table id issue.

Does anyone know how to clear the table IDs that the dlookup left open ?

Secondly, could anyone suggest a more robust method of looking up one value from another table. Basically I need to look up the USD to GBP rate (one value) for every record in an other table. I cant use a join (trade ccy->fxrate ccy)as there is properties to link on. Ive tried the elookup function I found on the net but performance is still an issue.

Any help is appreciated

You have to join the tables.
I don't know what you mean by
I cant use a join (trade ccy->fxrate ccy)as there is properties to link on.
Because that's the solution.
A DLookup shouldn't be the problem. Unless you are using it in a query. Then performance is probably also an issue.

All of the records on the deatiled table does not have "USD" to allow me to link. I would have to add this in an update query and then link through. Just wondering of there is a better way to do it.

BTW i managed to clear the database of the tool many tables issue by converting it to access to 2000. compact and repair didnt seem to work but this did.

So your problem is solved?

If you cannot join the two tables, the DLookup() cannot work either. The DLookup() needs a unique identifier to find the record with the price that matches the current record.

If you cleared up the too many tables error, what did you do?

The only way I could get over the "too many tables open" was to convert the database from 2003 to 2000 (or vice versa). This process seem to reset the table ids and I haven't had the problem since. I'm still struggling to understand the best way to Look up the reporting currency (USD) for each record . All of the trade amounts are in sterling I need to convert them to USD. The only way I can think to do it with a join is to add an extra field to each records for the iso currency code "USD" and then join this to the fx tables. There must be a better way of doing this?

Thanks

To simply convert the currency from sterling to USD
Fixed:

select sterling, sterling * 1.5 as USD from sometable

Dynamic from param table using a dlookup

SELECT sterling, [sterling]*DLookUp("Value","Param","Mnemonic = 'USD'") AS USD
FROM [sometable];
Or create a query on the fly:

strSql = SELECT sterling, [sterling]* "& DLookUp("Value","Param","Mnemonic = 'USD'") & "AS USD FROM [sometable]
The first one is the fastest the last one has the advantage that the dlookup is executed only once.

HTH:D