Question Run query & form from B/E of Split Database

[JohnB]

I have a complex union Query which works exceedingly well in an unsplit database. It is based on around 20 other queries which are in turn based on a number of tables and queries. This feeds a form which sumarises the status of every aspect of the db in one page.

Problem is when I split the db so 2 or more users can use it, the form (and query) fail, something to do with too many links. (I used the database splitter tool)

Is there a way to split the db and retain the query and form on the B/E so that a user can access it without causing too many links? I realise that this would restrict the user access to the form, but that is a compromise I am happy to make.

This has been doing my head in for a month now and any help would be much appreciated.

 

[lark_tilly]

I'm confused as to what you would be splitting if you're keeping the queries and form
with the tables. Am I midundertanding?

 

[rommelgenlight]

your queries are not supposed to be affected by the splitting of your database because only the tables are splitted to other database unless the table names are changed? so I think it is!

 

[WIS]

I have a complex union Query which works exceedingly well in an unsplit database. It is based on around 20 other queries which are in turn based on a number of tables and queries. This feeds a form which sumarises the status of every aspect of the db in one page.

Problem is when I split the db so 2 or more users can use it, the form (and query) fail, something to do with too many links. (I used the database splitter tool)

Is there a way to split the db and retain the query and form on the B/E so that a user can access it without causing too many links? I realise that this would restrict the user access to the form, but that is a compromise I am happy to make.

This has been doing my head in for a month now and any help would be much appreciated.

I can't see how splitting the db would cause that problem. Does everything else work apart from that frm and qry. You can use more that 1 fe per be.

Why don't you create a new fe with only the frm and qry(s) in it and link that to the be.

I rarely use the db splitter. I create 2 new dbs (be and fe) and import all the tbls into the be and the rest of the objects into the fe (making sure I set the references, ie libraries, as in the original db). I then compact and repair the new fe and compile it.

 

[JohnB]

My issue is that the query I am running and the form it feeds is pretty heavy duty (Unfortunately it can not be slimmed down). When I run it in an un-split db it works well, but a split db seems to use additional links (I believe this is something to do with "Jet" but I am not sufficiently versed in db writing to understand this.)
I thought that as this query and associated form work so well when they are not split, it might be possible to leave them with the back end when I split the rest of the db avoiding the issue with this one component of what is a pretty substantial project.
The form which sumarises pretty much the whole db is used by only one person to check that things are not being missed so there are likely to be very few conflicts with the other users, but I do want to be able to call it from the front end.
If this is possible I think it would leave me with 1 form and its associated queries in the back end and maybe 100+ other forms, queries and reports in the front end.
If anyone knows how to do this, or how to avoid the jet issue I would be very grateful.

 

[WIS]

My issue is that the query I am running and the form it feeds is pretty heavy duty (Unfortunately it can not be slimmed down). When I run it in an un-split db it works well, but a split db seems to use additional links (I believe this is something to do with "Jet" but I am not sufficiently versed in db writing to understand this.)
I thought that as this query and associated form work so well when they are not split, it might be possible to leave them with the back end when I split the rest of the db avoiding the issue with this one component of what is a pretty substantial project.
The form which sumarises pretty much the whole db is used by only one person to check that things are not being missed so there are likely to be very few conflicts with the other users, but I do want to be able to call it from the front end.
If this is possible I think it would leave me with 1 form and its associated queries in the back end and maybe 100+ other forms, queries and reports in the front end.
If anyone knows how to do this, or how to avoid the jet issue I would be very grateful.

There are some issues that I've discovered with split and unsplit dbs. eg In a single db if you open a recordset and rst.Recordcount, you get the right no of records. However the same code from the fe of a split db always returns 1 unless you rst.Movelast prior to the rst.Recordcount.

So there are some differences in how split and single dbs can react to the same code.

Is your qry a cascaded one - if so run each qry from the bottom up in a split environment until it falls over. That should give you a pointer on where to head next.

 

[JohnB]

Wis, thanks for the response.
If by cascaded you mean one union with a number of queries underpinning it the answer is yes. I have already checked every individual component works in both split and unsplit dbs, but when the db is split and I run the whole thing I get no results shown.
I have been reading up on this for several weeks and I am certain that it is something to do with exceeding the number of links Jet can handle. but I am stumped when it comes to understanding the issue.
As it works when it is in the same db as the tables, i thought it might work if I only split out the other queries and forms and then some how linked the new FE to call the form from the BE thus avoiding the additional links.