error 3048

[ipk*]

lost once more ...

i get the above error (can not open anymore databases) when i try to open a
form containing 30 subforms ("subquestion" about this in the end) ...

funnly, the ol search machine tells me the one and most important thing to
do is to split my database in front and backend. now the thing is, i have
already done so, and the error goes away if i delete the linked tables and
import them (rather than linking). so that is not it. there is also no db =,
set db, or openrecordset code involved.

nevertheless, it must be some problem with too many open connections to the
jet engine or so. if i reduce the number of subforms to 24, the error also
goes away with keeping the tables linked in, rather than imported.

all of the 30 subforms have basically the same underlying query, with two
parameters different.

why do i have to do this (and there goes my "subquestion") ? i want to
display a 2 dimensional matrix with 4 rows and 6 columns. each element of the
matrix contains 6 rows that i pull with the underlying query. like having a
chessboard and i want to show how many grains of rice, corn, wheat and barley
are on each square (and, of course their size and how dark they are roasted
etc ...

the underlaying tables have the data for a few 100 different chessboards, i
decide for one chessboard, pull up the form with the 30 subforms, for each
subform a generate (via vba) a query as recordsource that contains the
specific row and column for that specific square ...

oh my ... can anybody follow ?

if so ... can you help me ???

thanks, again ...

ingo

 

[Klatuu]

Assuming Jet would allow enough connections, your design would probably take
about a week to load them all. A bit of an exaggeration, but I once designed
a form with 7 list boxes and it took the form 2 minutes to load, so that
should give you an idea of what the impact would be.

Unless there is an absolute need for the user to have more than one of these
subforms visible at the same time and that you say the record sets for the
subforms differ only in the filtering, I would suggest you use one subform
and allow the user the capability to make selections that would filter the
subfrom.

 

[Duane Hookom]

If "all of the 30 subforms have basically the same underlying query, with two
parameters different." I would think you could use the Link Master/Child
from the main form to the subform to change the records returned in a single
subform.

You could also consider code to change the Source Object property of a
subform control. I can't imagine you would ever need to see more than a few
subforms at a time.

 

[ipk*]

Assuming Jet would allow enough connections, your design would probably take
about a week to load them all.

it does take about 15 seconds for the 30 subforms. slow, but fast is anyway
nothing i am even thinking about with msaccess. in this case it is
acceptable, since i expect the subform to get called 2-3 times a day per user.

A bit of an exaggeration, but I once designed
a form with 7 list boxes and it took the form 2 minutes to load, so that
should give you an idea of what the impact would be.

Unless there is an absolute need for the user to have more than one of these
subforms visible at the same time

they all need to be visible at the same time.

and that you say the record sets for the
subforms differ only in the filtering, I would suggest you use one subform
and allow the user the capability to make selections that would filter the
subfrom.

good idea, the filtering. but i still need the 30 subforms. but i think we
are on to something.

 

[ipk*]

hello duane,

i think dave and you are suggesting the same thing, but i an not sure yet
how to implement this.

this is the query for the subforms, in this case for row 1 column 1 of the
chessboard.

SELECT query_wellcompositions_via_table.*,
query_wellcompositions_via_table.WellPositionRow,
query_wellcompositions_via_table.WellPositionColumn, * FROM
query_wellcompositions_via_table WHERE
(((query_wellcompositions_via_table.WellPositionRow)=1) AND
((query_wellcompositions_via_table.WellPositionColumn)=1));

ehem ... i think the following should do the same and better. easy to fix.

SELECT query_wellcompositions_via_table.*, *
FROM query_wellcompositions_via_table
WHERE (((query_wellcompositions_via_table.WellPositionRow)=1) AND
((query_wellcompositions_via_table.WellPositionColumn)=1));

but anyways ...

each query does not return a single record, but it returns between 1 and 6
records, depending whether there is just rice, or rice, wheat, and barley etc
on the chessboard. plus their amount and roast so it is nothing i can get
to easily with a text box, i think. that's why i did this with subforms. so
what would you suggest as datasource for the subforms then, that does not
contain a query and what would be the datasource for the mainform. i think i
have queriers block ...

 

[ipk*]

the above query even shorter ...

strSql = " SELECT *"
strSql = strSql & " FROM query_wellcompositions_via_table"
strSql = strSql & " WHERE ((
query_wellcompositions_via_table.WellPositionRow =" & ThisRow & ")"
strSql = strSql & " AND (
query_wellcompositions_via_table.WellPositionColumn =" & ThisColumn & "));"

no ... but the real question that just occured to me is:

so ... if the underlying issue are the numer of connections to the jet
engine ...

is there a difference in connecting to the jet engine if i have the tables
local or just linked in. i would have thought that in either case a query
connects to the jet engine, and the jet engine then looks whether the tables
are local or remote. not sure. i am self tought with just a little knowledge
of databases and network. so then why to i get error 3048 if the tables are
linked and do not get it, if they are local. and b.t.w., if they are local,
the form with 30 subforms opens in 1 second, and if they are linked in it
takes some 15 seconds. is that a network problem that would be fixable ?

and a workaround that comes to mind could be to generate 30 local temporary
tables with an append or insert query or the like and then use those as
datasource for the subforms. not so elegant. but then, all is fair in love
and war. once the forms close i can discard them.

ingo

 

[Duane Hookom]

I'm thinking you could use a crosstab query but I really don't understand
your full specifications. Do you need for the results to be editable or just
read-only.

 

[ipk*]

read-only would be fine.

I'm thinking you could use a crosstab query but I really don't understand
your full specifications. Do you need for the results to be editable or just
read-only.

 

[Duane Hookom]

Again: I'm thinking you could use a crosstab query but I really don't
understand your full specifications.

 

[ipk*]

hi duane,

i found an example for what i want to do:

http://ginsberg.med.virginia.edu/grid.html

if you hit the "submit" button on this page, you will see pretty darn close
what i want to get.
except in this case the components are given and limited and specified to 5
(across, down, buffer, additive, water). in my case it could be anything
between 1 and infinity (+ water).
don't worry about the calculation. that's already taken care of. it is just
the display of the final grid.

does that help to understand my problem ?

ingo

 

[Duane Hookom]

"does that help to understand my problem ?" not at all.

I generally like to start with your table structures and sample records and
then desired output/display. Maybe someone else can get their head around
this but I can't.

 

[ipk*]

hi duane,

that's fine. nobody else is supposed to do my work. our exchange of messages
gave me a few ideas and i guess i will later simply go forward with a
temporary table. rather than 30 queries to queries (sic !) to some 5 linked
tables i will then have 30 much simpler queries to a short temporary local
table. i am sure that will do and be faster, too.

thanks for your time. your help is greatly appreciated !!!

ingo