Dlookup (Too many tables open)

[mcgrcoAgain]

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

 

[Guus2005]

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.

 

[mcgrcoAgain]

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.

 

[Guus2005]

So your problem is solved?

 

[mcgrcoAgain]

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

 

[Guus2005]

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