Access 2010 Data Macros: The Three-Legged Hunting Dog With One Bad Eye

[Neil]

Wow, it's just amazing how difficult it is to do even the smallest of tasks
in data macros. Like, right now, I'm creating a record in a table, and need
to insert the date value from a text box on a form into a field of that
table. That seems to be impossible to do.

I realize the response is going to be: your form won't be available when the
tables are on the web, so they can't reference it. I understand that. Still,
since Access is converting the form to a web form, and the macro to XML (I
assume) it seems there must be some way to pass a value along to the macro.
But there isn't, apparently.

All I want to do is insert a value from a text box into record field when I
create a record using a data macro. Is that possible?

Thanks!

 

[Gloops]

Neil wrote, on 01st May 2012 18:35 UTC + 1 :

Wow, it's just amazing how difficult it is to do even the smallest of tasks
in data macros. Like, right now, I'm creating a record in a table, and need
to insert the date value from a text box on a form into a field of that
table. That seems to be impossible to do.

I realize the response is going to be: your form won't be available when the
tables are on the web, so they can't reference it. I understand that. Still,
since Access is converting the form to a web form, and the macro to XML (I
assume) it seems there must be some way to pass a value along to the macro.
But there isn't, apparently.

All I want to do is insert a value from a text box into record field when I
create a record using a data macro. Is that possible?

Thanks!

Hello,

According to what you say above, your aim is not just to insert a value
from a text box into a field in a record of a table. I imagine you
already did that many times through VBA.

So, the first thing to do is probably to precise exactly the technical
context and constraints, as precisely as possible. After that, maybe
somebody is able to tell you that you have to introduce a change in the
technical context, or that it is not necessary.



(Well, you said you wanted the answer duplicate on both
comp.databases.ms-access, and microsoft.public.access, did not you ?
This is the meaning of the absence of the field followup-to in your post.)

 

[Neil]

Neil wrote, on 01st May 2012 18:35 UTC + 1 :

Hello,

According to what you say above, your aim is not just to insert a value
from a text box into a field in a record of a table. I imagine you already
did that many times through VBA.

So, the first thing to do is probably to precise exactly the technical
context and constraints, as precisely as possible. After that, maybe
somebody is able to tell you that you have to introduce a change in the
technical context, or that it is not necessary.

Well, I actually already found a workaround for my problem without doing the
insert value into table record thing I was looking to do. Still, I find that
annoyingly limited that I couldn't do that.

I find, as I begin to work with web data macros, that at every step there's
some severe limitation. Hence, the title of this thread. A three-legged
hunting dog with one blind eye COULD be used for hunting; but is it really
worth it?...

So, no sooner had I found a workaround for this limitation (which was
probably the 5th in a series of serious limitations) than I ran into another
web query limitation that caused me to abandon what I was doing completely,
and take an entirely different tack.

See, I have a table of data, and I need to grab data for a particular day
and give totals. Elementary, right? Only problem is: web queries don't have
totals!!!! Ugh!!!! What????? A simple thing like that??

So, Albert Kallal has been helpful, both here, as well as stuff he
previously posted elsewhere on the web that I come across through Google.
(I'd probably be lost without Albert. LOL) Anyway, in a thread I found,
Albert says to just use a report, and do the totals in the report. Sounds
great, especially since you can hide the detail section. Only thing is:
Office 365 doesn't have Access Reporting enabled yet! So if I want to use a
report, I have to use a different service.

I swear, it's like I'm jumping through an obstacle course here, and just as
I avoid landing on one obstacle, another gets you.

Anyway, getting back to this, so I can't use reports unless I switch
services. And, sure I can do totals in forms, putting Sum() in the form
footer. But there's no grouping. And I have to have group totals.

So, what am I left with? Compiling all my totals in client Access and
uploading them to SharePoint, and just pulling the pre-calculated totals.

Works fine as long as I only need to pull one day's worth of totals. But
what happens when I need to pull a week's worth? Then I'll need to add up
the daily totals, and I'm back to the same situation. Unless I can
calculate the totals live from the web site, I'm basically stuck. And
without the ability to do group totals in the form (or totals in the
underlying query), I don't see any way I can do that.

(Well, you said you wanted the answer duplicate on both
comp.databases.ms-access, and microsoft.public.access, did not you ?
This is the meaning of the absence of the field followup-to in your post.)

Yes, the point is to have a single thread that's updated in both forums,
rather than having duplicate posts, one in each forum.

Neil

 

[Gloops]

Well, you know, I am not trying to guess what you want to do with the
sole clue of knowing how Albert did it.

It works ? That is very fine.

 

[Neil]

Well, you know, I am not trying to guess what you want to do with the sole
clue of knowing how Albert did it.

It works ? That is very fine.

I'm sorry. I was kind of rambling in that last post because I was kinda
exhausted from hours of running into one obstacle after another and was very
frustrated. After having slept, let me give a more clear explanation.

There is a bunch of data in an Access app. The goal here is to give people
the ability to open a browser and report on that data, either for a single
day, or for a date range. Right now the reports we have in Access only
report on a single day. But eventually we want to expand that into date
range reports.

OK, so going to SharePoint, the original idea was to upload the raw data to
the web, and then construct the reports with summation values on the fly
when the user requests it for a particular date or date range.

My first attempt was to build temporary tables for the report, which is how
I have it in client Access. Doing this I ran into numerous limitations when
working with web data macros and queries. Most of them I overcame after a
substantial amount of research and trial and error. The one that I posted
about - being able to put a value from an unbound text box on a form into a
newly-created record, I could not overcome.

So then I realized I could probably rework my queries to not use temporary
tables at all, but just query the data. Even though I find building a report
using temporary tables to be a cleaner and more efficient method than using
complex multi-layered queries, which sometimes bog down, this was doable.

So I switched to doing that, so I wouldn't have to insert any values at all.

However, no sooner had I switched to that approach than I realized that web
queries don't allow totaling! This extremely basic and essential
functionality was something I just assumed would be possible. But, no, not
possible.

So I was left with doing the totals in the form or report footer - which is
fine except that, since I'm using Office 365, and Office 365 does not yet
have Access Reporting, I could not use a report footer for the totals (which
was Albert's solution, that I had previously mentioned); and, when trying to
do totals in the form footer, I was unable to because I need group totals,
not just report totals, and I don't know of a way to do group totals in a
form. (If anyone has any techniques for doing that, I'd be interested in
hearing.)

So what I was left with was not uploading the raw data to SharePoint and
doing calculations on the fly, but, rather, doing each day's calculations in
the Access app, and then just uploading the calculated values for each day
to SharePoint.

That works fine, and allows the user to select any day and get the values.
However, when the report is modified to be for a date range instead for a
single day, I'll be back in the same situation. I'd have to sum up all
totals for the date range, and there's apparently no way to do that in
SharePoint when using forms and needing group totals.

So what I ended up with was not a solution, but, rather, a temporary fix.
Either I find a way to do group totals in SharePoint using forms, or I'll
need to switch from Office 365 to a different SharePoint provider that does
provide reporting, but which is much more expensive.

So that's my still-unresolved situation.

BTW, note to community (or whoever here uses Office 365, anyway): I spoke
with a Microsoft technician at Office 365 yesterday, and asked if they were
going to be getting Access Reporting anytime soon. She said, yes, they would
be getting Access Reporting sometime in the future, but there is no ETA. She
said that if enough people request it, it would probably push them along to
getting it. So I suggest that anyone who is looking for that feature contact
the Office 365 team and request (and keep requesting) Access Reporting
(without being obnoxious about it, of course ).

Neil

 

[Gloops]

Neil wrote, on 02nd May 2012 16:16 UTC + 1 :

I'm sorry. I was kind of rambling in that last post because I was kinda
exhausted from hours of running into one obstacle after another and was very
frustrated. After having slept, let me give a more clear explanation.

There is a bunch of data in an Access app. The goal here is to give people
the ability to open a browser and report on that data, either for a single
day, or for a date range. Right now the reports we have in Access only
report on a single day. But eventually we want to expand that into date
range reports.

Hello,

You know, to do just that (in fact I have to admit I read specially the
beginning ), I learned the asp.net platform, with SQL Server.

Other solutions existed before, as compiling a program in VB6 to run the
queries, and that program is supposed to receive the good parameters to
run the queries. I read this solution in details a certain time ago, but
had no occasion to apply it. A special attention must be brought to the
security, for instance only use recorded queries.

Perhaps the second solution will take you less education effort, but
supposing you are going to have other applications to develop, being
up-to-date has its own certain advantages.

By the way, when you show data on an ASP page, with a dataset on an
Access database, you have to know that the parameters are only known by
their order, opposite to SQL Server or Oracle, that are able to
recognize parameters by their names.

I am afraid I cannot answer about Sharepoint.

 

[Neil]

Neil wrote, on 02nd May 2012 16:16 UTC + 1 :

Hello,

You know, to do just that (in fact I have to admit I read specially the
beginning ), I learned the asp.net platform, with SQL Server.

Other solutions existed before, as compiling a program in VB6 to run the
queries, and that program is supposed to receive the good parameters to
run the queries. I read this solution in details a certain time ago, but
had no occasion to apply it. A special attention must be brought to the
security, for instance only use recorded queries.

Perhaps the second solution will take you less education effort, but
supposing you are going to have other applications to develop, being
up-to-date has its own certain advantages.

By the way, when you show data on an ASP page, with a dataset on an Access
database, you have to know that the parameters are only known by their
order, opposite to SQL Server or Oracle, that are able to recognize
parameters by their names.

I am afraid I cannot answer about Sharepoint.

Thanks. I appreciate your input!

Neil

 

[Albert D. Kallal]

"Neil" wrote in message

Like, right now, I'm creating a record in a table, and need to insert the
date value from a text box on a form into a field of that table. That seems
to be impossible to do.

it is quite easy.

And as a side note anyone here ever use SQL server and write code for those
store procedures?

No debugger, and it is a rather lousy language.

However, I been busy and was sick today. So I shall make a short post.

I would love to tell one of my favorite "war" stories about how when came
over to Access from having used FoxPro for many years. Us FoxPro people
could not believe how stupid it was that Access did not have record numbers!
I mean, data written out to a table ALWAYS maintained the order. Having
written systems in Pascal, in FORTRAN, FoxPro, dBase and many others, they
all had record numbers and order of data written out was always maintained.
Heck writing data out to a comma delimited text file ALSO maintained order.

All of sudden years of that type of thinking had to be tossed out when I
adopted Access.

So why would such a simple thing now become so difficult with Access? Of
course the issue is as our industry moved towards a more abstracted out
database system, and farther and farther way from punch cards and the actual
file system on the disk drive, then these abstractions meant that the idea
of order of data inside a database table and that of using record numbers
simply were not appropriate designs for the needs of the industry and they
actually became not useful concepts in the Data Processing industry anymore.

In other words simple code designs that wrote data out to a table in a
particular order had to be tossed out. All of the designs and methodology
and things had been using for years all the sudden also had to be tossed
out.

At the end of the day it's an architecture change.

Anyway, I only have a bit of time here, so while I'd love to wax eloquent
about the FoxPro methodology change in which I had to go through when I
started uses Access, but let me just post an answer here.

All I want to do is insert a value from a text box into record field when
I create a record using a data macro. Is that possible?

Sure. No problem. And this does not matter if it is a bound form, or not.

Here is a data macro in which I want to create a child record A invoice
record, and I want to PASS the customer ID (the parent record PK) to this
routine that will create a new child record for me.

The code that does this is this:

https://public.sn2.livefilestore.co...7kz-R_cSfhEQDHmdNzQ/createinvoice1.png?psid=1

Note the use of a parameter to pass the value form the form. Also note how
the above RETURNS BACK the new ID of the record just created (I need that in
the form's code that calls the data macro).

The form code while not important, is this :

https://public.sn2.livefilestore.co...8-DcUfMCQmJjO1_kT0aTNkSQA/calldata.png?psid=1

Note how the above references a sub form, checks for a invoice or checks if
the current invoice date is NOT this month, then we call (and pass) the
value of the customer ID to the data macro. The data macro creates a new
invoice record, and RETURNS that PK value back to the calling form code of
what the new record ID was created.

So in above example:

I pass a value to a data macro from a form. The data macro creates a new
record in a table, and then as noted returns the PK value. You might not
need to return values, but the above code snips shows how this works anyway.

Just remember, if the value you grab is a un-bound text box and it suppose
to be a number value, then set the format of the text box to general number.

 

[Bob Barrows]

"Neil" wrote in message


it is quite easy.

And as a side note anyone here ever use SQL server and write code for
those store procedures?

No debugger, and it is a rather lousy language.

Obsolete information. There absolutely is a debugger now, starting in SQL
2005, when using VS. In SQL 2008, the debugger became available in SSMS when
it was made clear to MS that developers weren't going to switch to a
pardigm of using VS to design and SSMS to manage.And in prior versions of
SS, third-party applications such as RapidSQL offered debuggers.

For a "lousy" language, it's incredible how much can be done with it. Yeah,
some things are easier in VBA (error-handling for one, but even VBA sucks at
that - google for an article called "On Error GOTO Hell", but cursors are
much easier to employ in VBA - some would call that a bad thing ... <g>),
but there are things like string-handling that VBA does in a very
inefficient way.

 

[Neil]

Sure. No problem. And this does not matter if it is a bound form, or not.

Well, if it were a bound form, then I'd be copying from the table, right?
So, yes, an unbound control on a form.

Just remember, if the value you grab is a un-bound text box and it suppose
to be a number value, then set the format of the text box to general
number.

It's an unbound text box that contains a date value. And, yes, the format of
the text box was set to Date. But I could not get it to work. I'll look at
it again with your code in mind. Thanks.

--> Which actually brings up another question. Is there a way to get an
error message when running data macros? I noticed that when it didn't work,
it just didn't work. But I didn't get any error message. Is there a way to
get a message that tells you exactly what the problem is (or at least that
there is a problem?). Just having the macro run but not perform its task but
without an error isn't a good idea.

Anyway, getting back to this. I'll take another look at what I did wrong.
But, as I noted in a subsequent post, I actually found a workaround to avoid
having to do the insert. But then I ran into an even bigger problem: I could
not do summations in web data queries! I suppose this is an advance in
technology, like moving away from punch cards; but I don't see it as an
advantage. ;-)

Seriously, though, that was what triggered the title of this thread. Could
data macros seriously not have summations? Is there any logical reason for
that? Or has Microsoft just not gotten around to implementing them yet? That
seems like a SERIOUS limitation!

So I went searching around the inet, and found a post by you that addressed
the issue. In it, you advised someone to just use a report and do the totals
in the footer. And that's fine. Except that I'm using Office 365, and they
haven't implemented Access reporting yet. (In another place, I believe here,
you mentioned that you should be able to do with forms what you do with
reports - which is true: AS LONG AS TOTALS ARE IN THE QUERY!! LOL ).

In any case, so I found myself stuck once again. Sure, I could do the totals
in the form footer. No problem. But I need group totals. And for that you
need a report.

So I'm kind of stuck.

I ended up, as a temporary fix, to just do all the calculations in my Access
app and upload the data already calculated to SharePoint. And that's fine.
As long as the user only needs one day's worth of data (totals are per day).
But when we modify the site to allow them a date range, then I'm back in the
same place: no ability to do totaling because I need group totals, and
there's no reporting in Office 365.

So, without totals in queries (which I never DREAMED would be the case!!),
I'm left with probably having to switch to a different service instead of
Office 365. Unless you know of a way to do group totals in web forms. The
data (in simplified format) looks something like this:

Device Reading
--------- --------
Device 1 10
Device 2 13
Device 1 15
Device 3 22
Device 2 8
Device 2 20
Device 1 10
Device 3 18

etc.

Just various listings with values for each. I need a total and average for
each device.

Now, sure, I could create 3 text boxes, and use something like
Sum(IIF([Device]=Device1, [Reading], 0)) to get the sum for a particular
device, etc. But that assumes that I know how many devices there will be. I
don't.

So, with reporting totals, I could just group by device and get a total in
the group footer and have what I need. But with forms; and with no ability
to total in the data query; I'm stuck.

Thanks,

Neil

 

[Neil]

Obsolete information. There absolutely is a debugger now, starting in SQL
2005, when using VS. In SQL 2008, the debugger became available in SSMS
when it was made clear to MS that developers weren't going to switch to a
pardigm of using VS to design and SSMS to manage.And in prior versions of
SS, third-party applications such as RapidSQL offered debuggers.

For a "lousy" language, it's incredible how much can be done with it.
Yeah, some things are easier in VBA (error-handling for one, but even VBA
sucks at that - google for an article called "On Error GOTO Hell", but
cursors are much easier to employ in VBA - some would call that a bad
thing ... <g>), but there are things like string-handling that VBA does in
a very inefficient way.

Bob, PMFJI, but I looked up what you mentioned, and all I could find was
this: http://www.vbforums.com/showthread.php?t=110745 where the person talks
about having to put an On Error Goto into every routine he writes. Yeah,
that's kind of a pain. I, personally, have my error handler block stored in
a text file with the name eh.txt. Then, in Access, whenever I start a new
routine, I just do alt, i, l, e, h, <Enter> (which accesses the insert menu,
then insert file, then types "eh" as the name, and then enter selects it).
So it's very quick.

And I've found Access error handling to be very flexible and efficient.

So I'm not sure what you mean by that VBA sucks at error handling. Perhaps
you could elaborate....

Thanks,

Neil

 

[Albert D. Kallal]

"Bob Barrows" wrote in message

Obsolete information. There absolutely is a debugger now, starting in SQL
2005, when using VS. In SQL 2008, the debugger became available in SSMS
when it was made clear to MS that developers weren't going to switch to a
pardigm of using VS to design and SSMS to manage.And in prior versions of
SS, third-party applications such as RapidSQL offered debuggers.

Thanks for the heads up. I always encourage people to correct such
statements.

I should have really said I was talking about SSMS (SQL Server Management
Studio).

However, at the end of the day my point stands that for years and years with
SSMS we did not have a "easy" debugging option, and having to adopt Visual
Studio not such a simple nor light solution either.

However, I was not aware that SSMS now can debug t-sql code. Regardless, as
you point out there are options in this area now.

For a "lousy" language, it's incredible how much can be done with it. Yeah,
some things are easier in VBA (error-handling for one, but even VBA sucks
at that - google for an article called "On Error GOTO Hell"

Yes, I do agree. t-sql is quite incredible.

So perhaps lousy not the best term. What I mean to say is that T-sql is
still what I would call a weaker "programming" language.

However, t-sql is very much a procedural version of SQL and t-sql is rather
at home using SQL.

So this mix of procedural ability with the power of SQL is not something to
scoff at or laugh at. And you can write scalar functions (that simply a
user defined function that can be used in any sql expression just like we
can with VBA). I often had to replace some custom VBA functions I was using
in my Access SQL to now run server side. So far I always been able to
reproduce those custom VBA functions used in my Access SQL with custom "UDF"
(called scalar) functions in t-sql and this ability has helped huge in
migration projects.

In fact combining the non procedural ability of SQL with all its amazing
power and THEN adding even a limited procedural ability results in a VERY
powerful setup. And on top of this you can write UDF functions in t-sql
also.


To be fair, most store procedure code should not be that large of a chunk of
code anyway.

And to be fair such chunks of code hardly ever need to be that long and
winding due to having SQL at one's disposal.

 

[Albert D. Kallal]

"Neil" wrote in message

So, with reporting totals, I could just group by device and get a total in
the group footer and have what I need. But with forms; and with no ability
to total in the data query; I'm stuck.

You could write a table trigger to always maintain and update the totals -
they then be available at all times without the need for a group by query...

 

[Neil]

"Neil" wrote in message

You could write a table trigger to always maintain and update the totals -
they then be available at all times without the need for a group by
query...

No, that wouldn't work. If that were the case, then my workaround of just
uploading the totals from the Access app would also work. But it doesn't.

The problem is that the totals will be for a user-defined date range. So
it's not possible to calculate them with a trigger or upload them from
Access, because I don't know what totals will be needed. They may specify a
7-day range, a month range, a two-week range, etc.

With the very basic functionality of having totals in queries, this would be
a no-brainer - a simple where clause in a group by query. But now it's a
problem - one solved only (apparently) by moving to a much more expensive
platform and using report footers for totaling.

I am curious if you know the reason they don't have totals in web queries.
Is this a technological limitation, or has Microsoft just not gotten around
to implementing it yet?

Also, did you know about the error thing I asked about? Is there a way to
get an error message when running data macros? That is, a way to get a
message that tells you exactly what the problem is (or at least that there
is a problem)?

Thanks, Albert!

Neil

 

[Rob Parker]

Hi Neil,

A little off-topic, but since you're handling your error handler code in
such a clunky fashion, I figure it's worth mentioning.

There's a freeware add-in called MZ Tools that's a must for any serious
coder. It does heaps of useful thing; one of which is to add in a
standard - customisable - error handler in any module by a sinlge
button-click in its toolbar. You can find it at www.mztools.com, and it
will save you hours of effort in no time at all, for all sorts of common
tasks during code development.

HTH,

Rob

Bob, PMFJI, but I looked up what you mentioned, and all I could find was
this: http://www.vbforums.com/showthread.php?t=110745 where the person
talks about having to put an On Error Goto into every routine he writes.
Yeah, that's kind of a pain. I, personally, have my error handler block
stored in a text file with the name eh.txt. Then, in Access, whenever I
start a new routine, I just do alt, i, l, e, h, <Enter> (which accesses
the insert menu, then insert file, then types "eh" as the name, and then
enter selects it). So it's very quick.

<snip>

 

[Neil]

Hi Neil,

A little off-topic, but since you're handling your error handler code in
such a clunky fashion, I figure it's worth mentioning.

There's a freeware add-in called MZ Tools that's a must for any serious
coder. It does heaps of useful thing; one of which is to add in a
standard - customisable - error handler in any module by a sinlge
button-click in its toolbar. You can find it at www.mztools.com, and it
will save you hours of effort in no time at all, for all sorts of common
tasks during code development.

HTH,

Rob




<snip>

OK, thanks.

 

[Neil]

"Neil" wrote in message

You could write a table trigger to always maintain and update the totals -
they then be available at all times without the need for a group by
query...

BTW, previously I said that I could do something like
Sum(IIF([Device]=Device1, [Reading], 0)) to simulate group totals in the
form footer, if I knew how many devices there would be. Well, I decided
that, given the parameters of the data, I could make certain assumptions
about the number of devices, and then just use different forms for different
situations where the number of devices was different.

So, encouraged, I decided to plod along, creating my totals on the fly in
the form footer, using IIF statements to give me group totals. And, despite
other limitations that I had to spend an hour doing something that normally
would have taken me less than a minute to do, I got it to work! Woot!!

But, as has been the case so often in this journey, once I overcome one
hurdle, a fresh new one is staring me in the face, ruthlessly mocking me,
taunting me, _daring me_ to try and overcome it. But this one seems
unovercomable.

And this one is, again, something that I assumed would be there (something I
realized one should NEVER do with Access web databases!!!). That is, I
assumed that it would be possible to make the detail section of the form
invisible. But, again, I was proven wrong. You can't do that with an Access
web form. You can do it with an Access web report; but not a form.

So this is the end of the road, I'm afraid. I can't present the totals only
without presenting all the underlying data, since I can't hide the Detail
section of the form. So I'm stuck with either:

1) Calculate the totals in Access and upload them to the web, and base the
form on the totals table (problem here is that I have to know ahead of time
the date range for the totals, which I don't, or else limit it to one day);
or

2) Move from Office 365 to a more expensive service that provides Access
reporting in SharePoint (the simple solution, but a disappointing one, since
it wouldn't be necessary if Office 365 provided reporting, or if Access web
queries provided totals.

I suppose one last option remains, one which I hadn't considered and haven't
yet explored: use a loop in a data macro to calculate the totals based on
the specified date range. I don't know if that would work, or what other
issues I'd run into.

 

[Bob Barrows]

So this mix of procedural ability with the power of SQL is not
something to scoff at or laugh at. And you can write scalar
functions (that simply a user defined function that can be used in
any sql expression just like we can with VBA).

Scalar functions tend to be avoided, especially against large data sets, due
to their impact on performance. The more powerful table-valued functions are
preferred

I often had to
replace some custom VBA functions I was using in my Access SQL to now
run server side. So far I always been able to reproduce those custom
VBA functions used in my Access SQL with custom "UDF" (called scalar)
functions in t-sql and this ability has helped huge in migration
projects.
In fact combining the non procedural ability of SQL with all its
amazing power and THEN adding even a limited procedural ability
results in a VERY powerful setup. And on top of this you can write
UDF functions in t-sql also.

There is also now the possiblility of writing CLR functions and procedures
(introduced in SQL 2005), which, combined with the addition of new
TRY...CATCH error-handling, table variables, DATE and TIME datatypes, and
other keywords, allow t-sql to be compared very comfortably with VBA. There
is no longer any pain involved with switching to SQL Server beyond the pain
of replacing VBA keywords and functions with the t-sql equivalents.

 

[Patrick Finucane]

"Neil"  wrote in messagenews:[email protected]...

You could write a table trigger to always maintain and update the totals -
they then be available at all times without the need for a group by
query...

BTW, previously I said that I could do something like
Sum(IIF([Device]=Device1, [Reading], 0)) to simulate group totals in the
form footer, if I knew how many devices there would be. Well, I decided
that, given the parameters of the data, I could make certain assumptions
about the number of devices, and then just use different forms for different
situations where the number of devices was different.

So, encouraged, I decided to plod along, creating my totals on the fly in
the form footer, using IIF statements to give me group totals. And, despite
other limitations that I had to spend an hour doing something that normally
would have taken me less than a minute to do, I got it to work! Woot!!

But, as has been the case so often in this journey, once I overcome one
hurdle, a fresh new one is staring me in the face, ruthlessly mocking me,
taunting me, _daring me_ to try and overcome it. But this one seems
unovercomable.

And this one is, again, something that I assumed would be there (something I
realized one should NEVER do with Access web databases!!!). That is, I
assumed that it would be possible to make the detail section of the form
invisible. But, again, I was proven wrong. You can't do that with an Access
web form. You can do it with an Access web report; but not a form.

So this is the end of the road, I'm afraid. I can't present the totals only
without presenting all the underlying data, since I can't hide the Detail
section of the form. So I'm stuck with either:

1) Calculate the totals in Access and upload them to the web, and base the
form on the totals table (problem here is that I have to know ahead of time
the date range for the totals, which I don't, or else limit it to one day);
or

2) Move from Office 365 to a more expensive service that provides Access
reporting in SharePoint (the simple solution, but a disappointing one, since
it wouldn't be necessary if Office 365 provided reporting, or if Access web
queries provided totals.

I suppose one last option remains, one which I hadn't considered and haven't
yet explored: use a loop in a data macro to calculate the totals based on
the specified date range. I don't know if that would work, or what other
issues I'd run into.

The tale of your mental anguish both entertains me and I laugh while I
also recall times of code frustration and share your pain and
sympathize with your plight.

 

[Albert D. Kallal]

"Bob Barrows" wrote in message

There is also now the possiblility of writing CLR functions and procedures
(introduced in SQL 2005), which, combined with the addition of new
TRY...CATCH error-handling, table variables, DATE and TIME datatypes, and
other keywords, allow t-sql to be compared very comfortably with VBA. There
is no longer any pain involved with switching to SQL Server beyond the pain
of replacing VBA keywords and functions with the t-sql equivalents.

I am a VERY big believer in the concept of CLR and assemblies for SQL
server.

In fact I waited nearly 10 years for this ability. And I dare say that most
in the SQL server world are not too keen on this idea, but I am. This is a
two tier (as opposed to 3 tier setup), but I love this concept.

The reasons for having the database server consume and utilize a "decent"
coding system are many, but one simple reason is the ability to leverage
one's vb.net or c# skills to write server side procedure code for SQL server
and not have to adopt t-sql.

And there also a few other REALLY amazing things that bubble up as a result
of the CLR support with SQL server and I will in some future point post and
explain why this setup is beyond outer space cool and better then taking a
trip to a planet that is only populated by beautiful alien women models.