Please accept apology in advance if posted in wrong section… it feels like a QUERY question to me…. This question assumes Access 2003 delivered in MDE format.
I've been developing Access apps for 7+ years, and have a particular app with 8-10 users. These users are not database proficient, so all the user interface is via forms and heavy VBA. The app deals with several components of compliance/SOX information, and has taken on a life of it's own in the flexibility and functionality it provides. The admitted ugliness is the reporting section of this tool in its organization and structure. These criteria- driven "reports" are available in both standard report format and also...where appropriate...in query/datasheet format. There are literally several dozen of these reports...probably close to 75-80.
I'm in the process of taking a week or two and revamping this entire reporting scheme, and the issue I'm trying to solve for is for those reports where the results are delivered in datasheet format. I want to control the output region in a defined portion of the screen versus standard datasheet presentation that fills the entire screen. In other words, I want the query output to be presented in a predefined form or subform. I'm also trying to develop this in such a way as to where I can introduce new queries without having to actually update the MDB file, but instead have the SQL in a data field in a table on the network that I can merely update and introduce on the fly… the eliminate the constant versioning issue.
I have this working just as I want it to using VBA code that rebuilds a form with the appropriate data fields included that is built dynamically from the SQL. This form is then presented on the screen in datasheet format as a subform and allows me to control where it appears. Pretty cool EXCEPT that this methodology will prevent me from delivering this application in MDE format because I can can't change the design of the form in as MDE deliverable.
I've been delivering this Access 2003 database in MDE format for 4+ years for the obvious reasons, and don't really want to have to revert back to MDB delivery. I've tried about everything I know to do to control the OPENQUERY in datamode = acReadOnly (because they have the capability to sort/filter/export the query results).
So the question is: Is there a easy way (or even a complicated way) to accomplish what I’m trying to do? Hopefully I’ve explained that poorly enough for someone to get the idea of my dilemma.
I've been developing Access apps for 7+ years, and have a particular app with 8-10 users. These users are not database proficient, so all the user interface is via forms and heavy VBA. The app deals with several components of compliance/SOX information, and has taken on a life of it's own in the flexibility and functionality it provides. The admitted ugliness is the reporting section of this tool in its organization and structure. These criteria- driven "reports" are available in both standard report format and also...where appropriate...in query/datasheet format. There are literally several dozen of these reports...probably close to 75-80.
I'm in the process of taking a week or two and revamping this entire reporting scheme, and the issue I'm trying to solve for is for those reports where the results are delivered in datasheet format. I want to control the output region in a defined portion of the screen versus standard datasheet presentation that fills the entire screen. In other words, I want the query output to be presented in a predefined form or subform. I'm also trying to develop this in such a way as to where I can introduce new queries without having to actually update the MDB file, but instead have the SQL in a data field in a table on the network that I can merely update and introduce on the fly… the eliminate the constant versioning issue.
I have this working just as I want it to using VBA code that rebuilds a form with the appropriate data fields included that is built dynamically from the SQL. This form is then presented on the screen in datasheet format as a subform and allows me to control where it appears. Pretty cool EXCEPT that this methodology will prevent me from delivering this application in MDE format because I can can't change the design of the form in as MDE deliverable.
I've been delivering this Access 2003 database in MDE format for 4+ years for the obvious reasons, and don't really want to have to revert back to MDB delivery. I've tried about everything I know to do to control the OPENQUERY in datamode = acReadOnly (because they have the capability to sort/filter/export the query results).
So the question is: Is there a easy way (or even a complicated way) to accomplish what I’m trying to do? Hopefully I’ve explained that poorly enough for someone to get the idea of my dilemma.
