"Bob Barrows" <(E-Mail Removed)> wrote in message
news:jntmg4$5bh$(E-Mail Removed)...
> Albert D. Kallal wrote:
>> "Neil" wrote in message news:jnp3f3$ebd$(E-Mail Removed)...
>>
>>> 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.
>
> 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

"Bob Barrows" wrote in message news:jntmg4$5bh$(E-Mail Removed)...

>> 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.

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 (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

"Neil" wrote in message news:jnu9g6$m4r$(E-Mail Removed)...

>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...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

"Albert D. Kallal" <(E-Mail Removed)> wrote in message
news:71Yor.176317$(E-Mail Removed)...
> "Neil" wrote in message news:jnu9g6$m4r$(E-Mail Removed)...
>
>>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...
>

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

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


"Neil" <neil.ginsberg+(E-Mail Removed)> wrote in message
news:jnua42$qc0$(E-Mail Removed)...
>
<snip>
> 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>

"Rob Parker" <NO robpparker SPAM @ FOR optusnet.com.au ME> wrote in message
news:jo21vj$q9u$(E-Mail Removed)...
> 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
>
>
> "Neil" <neil.ginsberg+(E-Mail Removed)> wrote in message
> news:jnua42$qc0$(E-Mail Removed)...
>>
> <snip>
>> 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>
>

OK, thanks.

"Albert D. Kallal" <(E-Mail Removed)> wrote in message
news:71Yor.176317$(E-Mail Removed)...
> "Neil" wrote in message news:jnu9g6$m4r$(E-Mail Removed)...
>
>>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...
>

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.

Albert D. Kallal wrote:
>
> 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.

On May 5, 12:04*am, "Neil" <neil.ginsberg+newsgr...@gmail.com> wrote:
> "Albert D. Kallal" <PleaseNOSpamkal...@msn.com> wrote in messagenews:71Yor.176317$(E-Mail Removed)...
>
> > "Neil" *wrote in messagenews:jnu9g6$m4r$(E-Mail Removed)...
>
> >>So, with reporting totals, I could just group by device and get a totalin
> >>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...
>
> 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.

"Bob Barrows" wrote in message news:jo34km$l8r$(E-Mail Removed)...


>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.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)