Network Problem

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

This is a problem only when I use linked tables from the server. When
running these forms using tables that are not linked ...works fine.
I have a form that has many subforms whose RecordSource is set to a query.
When the form is opened I am getting an error "can not open any more
databases". I believe the query is running with every occurance of the
subform. I am using linkMaster and linkchild properties to correctly
populate the subforms.
As an experiment I created a TestTable with records from the query, set the
subforms recordsource proberty to the TestTable...this seems to work, so it
appears that the problem lies with using a query as the recordsource of the
subforms.

Maybe I need to create a recordset or something that is not requeried....any
suggestions?
 
Hi, Jeff.

Error #3048 is usually caused by your database application trying to handle
too many tables at the same time. There are 2048 "table handles," or
TableID's, available for Jet 3.51 and 4.0 (1024 TableID's for Jet 3.0 and
3.5) that Access requires when dealing with each table or linked table.

TableID's are used for the following:

- Tables
- Queries
- Bound forms and reports
- Combo boxes and list boxes whose row source is a table or query
- Data Access Objects (DAO) Recordsets in code
- Domain functions, such as DLookup( ), DCount( ), et cetera.

If this error is caused by the application using too many TableID's, then
ensure that every Recordset used in code is explicitly closed and the
Recordset variable set to Nothing before the variable goes out of scope. If
this isn't enough to prevent the error message from recurring, then the
alternatives are to reduce the number of combo boxes that meet the above
listed criteria, simplify complex queries, or to unlink some of the tables
(linked tables require twice as many TableID's) and place these tables in
the database file.

Reducing the number of any of the items on the list above will help reduce
the number of TableID's in use. The important thing to remember is the
total number of TableID's in use at the same time needs to be prevented from
exceeding the limit.

This error has also been reported to be caused by implicitly referring to
the value of a checkbox in an If...Then block statement. If this is the
case, then the solution is to explictly refer to this value, instead.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Gunny,
Thanks for the reply. I dont think I have any recordsets open. I have
simply set the recordsource of the subforms to the query. Does this create
a recordset that needs closing? If so ....what is the process?
I only have 2 comboboxes on the main form and none in the subforms.

Unlinking the tables might be an option if I make my query a "make table"
query, this would place a table with the query records in my database.
Although I would think there is a better way! these records are for viewing
only, not to be edited in any way.
My wish...to create a recordset from the query one time in the main form,
and then have the recordsource of the subforms set to the recordset.
Possible?

FYI
The form resembles a calendar. 35 instances of the same subform with code
that sets the LinkMaster and LinkChild for each.
 
Hi, Jeff.
I have
simply set the recordsource of the subforms to the query. Does this create
a recordset that needs closing?
Click to expand...

Well, a query contains a RecordSet, but the type of RecordSet that needs to
be handled with an explicit "Close" command is the RecordSet that is created
in a VBA procedure. Programmers are occasionally negligent and allow the
RecordSet variable to go out of scope before the RecordSet is closed and set
to Nothing to release memory and other system resources (including
TableID's) that the RecordSet used within the VBA procedure. If any of your
VBA procedures had unclosed RecordSets, then this is something that could be
addressed to free up some of those TableID's to prevent your application
from maxing out. Looks like this solution doesn't apply to your situation,
though.
My wish...to create a recordset from the query one time in the main form,
and then have the recordsource of the subforms set to the recordset.
Possible?
Click to expand...

Hmm. It seems to me that each of the subforms is going to have its own
separate RecordSet if you use Access's conventional objects. Perhaps
someone else can give you some more ideas along that line.

You could probably create your own user-defined object that contains a
RecordSet Property and apply a single instance of that object to each of
your subforms. The problem with that is if one of the subforms changes the
object's RecordSet, then the RecordSet is changed for _all_ of the subforms.
Do you really want that?
35 instances of the same subform with code
that sets the LinkMaster and LinkChild for each.
Click to expand...

Behind the scenes of bound main forms with bound subforms, Jet is applying a
join between the two (or do you have more?) tables in a SQL query. Since
there are two TableID's for each linked table, two linked tables in 35
queries = 2 * 2 * 35 = 140 TableID's (there's some overhead TableID's added
to that, too). Now add the RecourdSource of each of those 35 subforms bound
to a query. If that query has only one linked table, then 2 * 35 = 70
TableID's, plus the overhead TableID's.

Not counting your (bound?) combo boxes on your main form, I estimate that
you are using a minimum of about 220 TableID's for your main form and 35
subforms. This estimate considers the absolute minimum number of tables per
query, so if your queries are more complex, such as queries that contain
subqueries, or queries based upon other queries that use more than one
table, or queries that contain multiple joins, then you can start
multiplying these numbers to get an estimate of how many TableID's your form
and 35 subforms are actually using.

It's likely that moving just one of those linked tables to your current
database would make a big difference in the number of TableID's that your
application is using.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Gunny Wrote:
You could probably create your own user-defined object that contains a
RecordSet Property and apply a single instance of that object to each of
your subforms. The problem with that is if one of the subforms changes the
object's RecordSet, then the RecordSet is changed for _all_ of the subforms.
Do you really want that?

Jeff RWrote:
I think its OK. there is not going to be any editing from this form. It is
for display only.
Can you give me an example of a user-defined object.

I have 3 queries that come together in a UnionQuery that the subforms are
sourced, so I'll bet the TableID's are extreme. This is likely the problem.
 
Hi, Jeff.
Can you give me an example of a user-defined object.
Click to expand...

Sorry. I don't know of any examples on the Web to refer you to. I've never
seen a Microsoft Access book or Web site that described how to do it, but
I've used the techniques of creating VB 6.0 objects (I learned these
techniques from a beginner's VB book, so they aren't difficult) in Access,
and I can tell you that they work great for customizing applications and for
code reuse. All of the VB Web sites I can think of that might give examples
of how to create an object from scratch only have VB.Net examples at
present. Perhaps someone else can give you some references.
I have 3 queries that come together in a UnionQuery that the subforms are
sourced, so I'll bet the TableID's are extreme. This is likely the
Click to expand...
problem.

Yup.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Hi, Jeff.

I found a few examples of creating and using user-defined objects in
Microsoft Access. Please see Sandra Daigle's Web site (she's an Access MVP)
to download her clsCtlGroups.zip file on this Web page:

http://www.daiglenet.com/MSAccess.htm

I notice that she has error handling in every procedure. While this is an
excellent idea in most VB procedures, this generally isn't the case with
classes. You'll find that an error that was intended to be handled
gracefully just panics or confuses the user when he receives three or four
error messages, due to each layer of the class triggering the next layer's
error handler until it eventually reaches the error handler in the procedure
that uses the class object.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Back
Top