"can't open any more databases" error

[vwhite]

I have a report that calls a number of subreports. I also have totals at the end of each grouping and at the end of the report that use code to get totals for a particular record. The code uses DAO to get the totals (as they came from different tables and it became too complex to total from the subreports). However when I run the report I get an error message - "Can't open anymore databases"

the code goes something like this -

Function GetTotal(id) as double

Dim dbs as Database
Dim rst as Recordset

set rst = dbs.openrecordset(sdtrSQL, dbopensnap)
....
....
rst.close
dbs.close
set rst = nothing
set dbs = nothing

end function

the tables are in a linked database. but this code get called quite a bit when running the report. Do I need to do this in another way? Can anyone help with this?

cheers
vic.

 

[thornmastr]

linked tables ????

Normally means it cannot find the original database that a table is linked to.

it's often a matter of reducing the number of databases/
recordsets that are open at one time.

1. Reduce the number of combos/list boxes (cut RowSources)

2. Drop the domain aggregate functions in queries. Each row of
the query opens a "table", so it's easy to run out.

3. In code, explicitly close and dereference your objects. While
Access mostly succeeds in doing this, it's not perfect, so
you can lose resources.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[vwhite]

yep linked tables...

it appears only to happen if I preview the report then try and output to snaphot viewer or pdf or something as it must run through all the queries and functions again

1. there are no combo boxes or list boxes - just about 10 subreports all with a seperate record source (linked to the parent record on the main report - most only return one row)

2. Not sure what "domain aggregate" functions mean?

3. The code explicitly closes and dereferences the objects. (Then I thought maybe I shouldn't use CurrentDB?).

Anyway I took out all the totalling functions to test it and I got the same problem. 10 subreports is not excessive is it?

 

[thornmastr]

Before we start disdcussing 10 subreports, let's stick with the linked tables aspect.

Relink all your linked tables. This might solve the problem immediately. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[vwhite]

The tables are relinked every time I open the database.....

 

[thornmastr]

Two things I would check.

1) Are you absolutely sure the code used to link your tables is passing the correct database path?

2). In your function to return your total value you might want to build an error routine that simply did a debug print of the error number and description. I am thinking that possibly Access cannot reference the table you want in strSQL.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[vwhite]

1. I'm sure the tables are linked correctly, I use the attach tables code all the time and can open and query all the tables with no trouble.

2. I took the total functions out completely to test the report and I still get the same error.

The only way I can print the report is if I don't preview it first.