M
Mishanya
Conceited and fully happy with myself I've just finished my first Access
project. Since I'm a rookie, I suspect my work is not over-loaded with
bombastic VBA modules and elaborate functions (comparing to what I've seen in
some DB I've downloaded for learning purposes). Some couple of dozens of
tables (mostly with a very few fields), about the same amount of queries most
of wich serve as sources for reports' pie-charts (though, 2 with subqueries
and one union), Allen Browne's Calendar (probably the most elaborate part of
my project wich I simply copy-pasted without tempering inside - I'm not that
level of Access user to understand it's functions' labirint anyway), a dozen
of forms with nested subforms (again - nothing
super-power-rangers-staff-extraordinary).
Although, I did build 2 things wich I'm proud of:
1) The main all-in-one-workplace form wich presents simultaniously most of
the data related to selected client (a dozen of subforms,nested partly in
tab-controls, partly alone, a few sub-sub-singlview-forms content of wich is
controled by clicking its parent' datasheet-view subforms' controls, command
buttons for opening other general forms using link criteria, etc.). Again -
no common sense raping - just exploitation of the big 21'' screen.
2) The main report consisting of 13 subreports with 7 of them having
subsubreports (the pie-charts designed in forms , copy-pasted to the reports
and linked on the mainreport ClientID' parameter in their underlying queries).
Everithing works perfect while unsplitted, no delays or "calculation"
hanging while opening the main form or running the main report. I was
planning to deliver it tomorrow to the final user with only one thing to
complete - splitting the DB so it would serve 2 users (the computer of 1 of
them serving a server role). After learning, that the task of splitting is
quite simple, I've tested it anyway on my machine.
After the splitting, while both BE and FE files are still in the same
directory in my computer (no server or multiple users involved), all the
forms still react nicely (a minor delay with the mainform opening). But my
"cherry of the cream", the report, wich the whole thing is built for, have
turned out on me.
It does run end even opens in preview on page 1, but when I click the arrow
to procede to the page 2, it throws Runtime error '3048 cannot open anymore
databases', then some big box with cursing about 'an error while sending data
to the OLE server' and all the possible why's to this - at best (the first
6-7 subreports do show, then blank pages, no pies at all), or crashing and
closing Access with no further explanation at worst. Yet, every subreport
(including those, having sub-sub-reports) opens individually with no problem.
I've read all the treads on the issue in the forum - seems that that kind of
problem occurs here and there and the root might lie in Access inability to
handle many queries simultaniously. My question is:
1) Why it does not seem to be a problem at all while the DB is unsplitted?
2) The number of the mainreport queries may increase while run by multiplie
users, but in my case I'm the only one and the DB is still runs inside one
machine, so what is the crucial difference?
3) Comparing to the projects people describe in this forum, mine is really
light-weight, so how can it be unexecutable while splitted even on this low
level of sofistication and data mass?
4) And most importantly - I can't even imagine how to approach the task of
solving the issue - there are 3 or 4 domain aggregate functions in my
mainform (2 including RecordsetClone), but not a single in the report,
naturally there are few mainform' comboboxes and quite a lot of controls
populated on the open mainform. So when I run the report while the mainform
is closed, the output is much better - all the table-view subreports do show,
but the charts still fail. But is the Access so weak indeed?
For those who had patience to read this saga (my longest composition in
English in my life - but I'm really frustrated) - please advise.
Happy New Year to all of You!
project. Since I'm a rookie, I suspect my work is not over-loaded with
bombastic VBA modules and elaborate functions (comparing to what I've seen in
some DB I've downloaded for learning purposes). Some couple of dozens of
tables (mostly with a very few fields), about the same amount of queries most
of wich serve as sources for reports' pie-charts (though, 2 with subqueries
and one union), Allen Browne's Calendar (probably the most elaborate part of
my project wich I simply copy-pasted without tempering inside - I'm not that
level of Access user to understand it's functions' labirint anyway), a dozen
of forms with nested subforms (again - nothing
super-power-rangers-staff-extraordinary).
Although, I did build 2 things wich I'm proud of:
1) The main all-in-one-workplace form wich presents simultaniously most of
the data related to selected client (a dozen of subforms,nested partly in
tab-controls, partly alone, a few sub-sub-singlview-forms content of wich is
controled by clicking its parent' datasheet-view subforms' controls, command
buttons for opening other general forms using link criteria, etc.). Again -
no common sense raping - just exploitation of the big 21'' screen.
2) The main report consisting of 13 subreports with 7 of them having
subsubreports (the pie-charts designed in forms , copy-pasted to the reports
and linked on the mainreport ClientID' parameter in their underlying queries).
Everithing works perfect while unsplitted, no delays or "calculation"
hanging while opening the main form or running the main report. I was
planning to deliver it tomorrow to the final user with only one thing to
complete - splitting the DB so it would serve 2 users (the computer of 1 of
them serving a server role). After learning, that the task of splitting is
quite simple, I've tested it anyway on my machine.
After the splitting, while both BE and FE files are still in the same
directory in my computer (no server or multiple users involved), all the
forms still react nicely (a minor delay with the mainform opening). But my
"cherry of the cream", the report, wich the whole thing is built for, have
turned out on me.
It does run end even opens in preview on page 1, but when I click the arrow
to procede to the page 2, it throws Runtime error '3048 cannot open anymore
databases', then some big box with cursing about 'an error while sending data
to the OLE server' and all the possible why's to this - at best (the first
6-7 subreports do show, then blank pages, no pies at all), or crashing and
closing Access with no further explanation at worst. Yet, every subreport
(including those, having sub-sub-reports) opens individually with no problem.
I've read all the treads on the issue in the forum - seems that that kind of
problem occurs here and there and the root might lie in Access inability to
handle many queries simultaniously. My question is:
1) Why it does not seem to be a problem at all while the DB is unsplitted?
2) The number of the mainreport queries may increase while run by multiplie
users, but in my case I'm the only one and the DB is still runs inside one
machine, so what is the crucial difference?
3) Comparing to the projects people describe in this forum, mine is really
light-weight, so how can it be unexecutable while splitted even on this low
level of sofistication and data mass?
4) And most importantly - I can't even imagine how to approach the task of
solving the issue - there are 3 or 4 domain aggregate functions in my
mainform (2 including RecordsetClone), but not a single in the report,
naturally there are few mainform' comboboxes and quite a lot of controls
populated on the open mainform. So when I run the report while the mainform
is closed, the output is much better - all the table-view subreports do show,
but the charts still fail. But is the Access so weak indeed?
For those who had patience to read this saga (my longest composition in
English in my life - but I'm really frustrated) - please advise.
Happy New Year to all of You!