"Neil" wrote in message news:jafaba$ab1$1 (AT) dont-email (DOT) me...

Yes you have BOTH trigger events and also what are called "named" macros.
Those named macros can be called from VBA, macro code or even from a web
form for published applications.
So what we called "named tables macros" are in fact store procedures.
And for web development this is about the only place to put code and thus
those named macros sort of become your code modules for web based
appliations
(you have not other place for server side code!)

So you can write store procedure code that is just sitting there and such
code does NOT run and in fact WILL not be run by table events nor are they
attached to table events.

An example of this setup can be seen in my store procedure "soundex" example
code here;

http://www.kallal.ca/searchw/WebSoundex.htm

The above has a sample download that will run in the Access client (it is a
web database, but it is un-published and it can be run in a2010 100% client
side only – if you have 2010, you can give it a try).

In the above soundex example both table triggers and ALSO the form calls a
store procedure that converts a passed string into a soundex code and
returns the value. So yes to be clear, you can write and save procedural
code that is not tied to event or so called table trigger code.

Really great question!

Unfortunately, if you split your database then the front end cannot call
those stored procedures (however this might be possible to do but I not
checked this out enough so at this point in time I assume no). However the
back end table triggers can of course can call those store procedures that
exist in the back end. And this is even the case with multi-users in a
typical file share with FE on each user PC.

At the end of the day, this is not too bad of limitation since we talking
about a file based system and in ALL cases the processing has ALWAYS
occurred on the client side computer anyway.
So this would never give any performance advantages for Access applications
in a file share mode. So all processing does run client side and this
remains un-changed when using JET/ACE store procedures and Access has always
worked this way in the past.

So if your database is un-split, then VBA, or Macro code etc. can call store
procedures and values can be returned back. And in additional to VBA or
Macro code calling the store procedures, the code placed in table triggers
can ALSO call those same store procedure code and again have values passed +
returned back.

However if you split the database, then the front end cannot call the store
procedure code
(but as noted table triggers etc. can call those routines and they run even
in a split database
and that includes as noted even in a multi-user file share setup).

However for a published database, then even the VBA front end call call the
server side store
procedures and of course this extends to web based forms that also can call
those store procedures.

Sure, that is about right. Keep in mind that the published application can
also include VBA forms and VBA code modules etc. So web based applications
can be hybrid and they can have a mix of web forms and VBA forms. In fact
you can publish a application that has no web parts and is 100% VBA, but
then NONE of the features can be used in a web browser.

When you publish a application with a mix of VBA + web forms, then only web
objects run in a browser. However if you run the application with the Access
client then both types of forms run just fine and in fact by looking at them
you cannot tell the difference between a web form and a VBA form (they both
look and run the same in the client side on your desktop – in fact VBA forms
can open web forms with a standard openform command).

Yes, excellent on your part.

Upsizing a table or publishing a database with just one table in the accDB
will not result in really anything different between a publish or up-size.
However as noted publishing means you can have web forms and also those
store procedures + table triggers you have will now be moved up to the
server.

Correct. However this is not really much different how a lot of un-split
applications are now.

Yes, but that quite much how linked tables to SharePoint always worked.
(even 2003 could link to SharePoint and it still can - including office 365)
These links look like linked tables and you see them much like any other
linked
table in the "tables" tab area.

Yes, the above is correct. The big downside here is that you can't send or
have a update query run server side.
So if you update 10,000 records, - ouch you get 10,000 records flowing
across the network pipe. So this is an example where sql server runs circles
around this setup – remember I said some things work great and other things
no so great.

If your application has to update tons of records with lots of record
processing then this setup not so great. However, if your setup does not
have to update large numbers of rows, then the setup is fantastic.

Yes, a conflict manager pops up and asks what you want to do.

Adding records when off line starts using autonumber id's of -1, -2, -3 etc,
and when a sync occurs then the server and Access assign positive (> 0) auto
numbers (and this cascades to related child tables).

Yes, again you are correct.


You can do stupid huge massive scaled out databases with Azure. But your
application better not try to transfer too much data over the internet!

I think there some great possibilities for small business here also A 1 gig
database is $10 per month last time I looked – but not clear on bandwidth
charges or if there are any.

For the most part I can say yes to the above.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

"ron paii" <none (AT) nospam (DOT) com> wrote

Great! Can I call on you to help with this if needed (paid, of course)? (You
can send me an e-mail from this message.)

Thanks!

Neil

"Neil" <neil.ginsberg+newsgroup (AT) gmail (DOT) com> wrote


Of the 2 firewall brands I have used, I liked the current SonicWall. It is
active directory intergraded, allowing me to add and subtract VPN users in
active directory; and the users have a common VPN/Login password. The
WatchGuard had more features but it's VPN client was VERY hard to remove
properly (they may have fixed it by now). Both firewalls allow you to manage
users though the firewall's web site.

Both clients were downloadable, After installation all that was needed is
the IP address of your firewall and the passcode (Like a secured wireless
connection), then allow access though you remote's firewall. About 1/2 of my
users could install it with written instructions. The client will normally
work if the remote has internet access. The only issues I have seen is if
the VPN client is being blocked by a firewall at the remote site.

A 3nd brand that has been recommended to me is Cisco.

Thanks for the info. I appreciate it! A couple of questions.

1) Do you have to use their VPN client? Or can you use the built-in Windows
VPN client or other VPN client?

2) You say 1/2 your users could install it with written instructions. Do you
mean written instructions provided by the manufacturer, or written
instructions that you wrote for the users? (And, if the latter, could I get
a copy of those instructions?)

Thanks!

Thanks again for your reply, Albert! A couple more questions.

returns the value. So yes to be clear, you can write and save procedural
code that is not tied to event or so called table trigger code.<<

Very cool. Thanks for the example. I'll be sure to check it out.



Yes, that's what I was getting at - that a back end stored procedure has to
be able to be called from the front end for it to be useful, unless it's
called by a table trigger.


Create a database object on the back end database, and somehow run the macro
stored there? Don't know if that's possible. But that's the only thing that
comes to mind as a possible method.

exist in the back end. And this is even the case with multi-users in a
typical file share with FE on each user PC.<<

Yes, yes, and that's great. But to truly say that "we have stored procedures
in Access now", those stored procedures (named data macros) have to be
accessible from the front end. Otherwise, it's just table triggers (even if
the table trigger calls a named macro, it's still functioning as a table
trigger).

I don't mean to be argumentative here. I'm just saying that to truly have
the functionality of stored procedures in Access, they have to be accessible
from the front end. Wouldn't you agree?


occurred on the client side computer anyway.
So this would never give any performance advantages for Access applications
in a file share mode. So all processing does run client side and this
remains un-changed when using JET/ACE store procedures and Access has always
worked this way in the past.<<

Hmm, I guess I'm not following this. If I have an Access front end, using
JET to access the back end, then, yes, any processes will occur in the front
end.

But if I have a back end sitting on a server somewhere, with a named data
macro on that back end file, and I somehow send a command to that back end
file to run that named data macro, I would think that it would be running on
the server, and not in my front end client app. If the back end data macro
runs some process (as opposed to opening a recordset), and the front end
merely executes it, I wouldn't think that the data that the back end macro
would be processing would somehow be transferred to the front end to be
processed there. Seems that it would be handled on the back end.

But, in any case, to say it's "not too bad of a limitation" if it's handled
on the front end is sort of going full circle. To say we have stored
procedures in Access is only meaningful if they can process data in the back
end, without that data having to travel to the front end. If a stored
procedure is handled on the front end, then it's not really a stored
procedure after all.

But, again, this is all theoretical -- IF we were able to execute a back end
data macro from the back end, and IF it ran on the front end verses the back
end.



But if the database is unsplit, then how is calling a stored procedure
(named table macro) any different, than, say, calling VBA code that returns
values? That has always been possible with unsplit databases. The key here
would be if code could be run on the back end without involving the front
end. Then that would be a true stored procedure.

Again, not trying to be argumentative. Just really not seeing what is so
different about a data macro stored in an unsplit database verses what's
always been available.

can ALSO call those same store procedure code and again have values passed +
returned back.<<

Yes, table triggers definitely are a nice thing, and a definite step forward
for MS Access!


(but as noted table triggers etc. can call those routines and they run even
in a split database
and that includes as noted even in a multi-user file share setup).<<

Yes, understood.


procedures and of course this extends to web based forms that also can call
those store procedures.<<

OK, maybe this is what I'm not understanding (probably because I'm not
familiar with SharePoint). So if a database is published, and is now in
SharePoint, and the macros have all been ported to SharePoint, then how
would a VBA app in client Access call a named data macro (stored procedure)
in SharePoint? That is very interesting.


client then both types of forms run just fine and in fact by looking at them
you cannot tell the difference between a web form and a VBA form (they both
look and run the same in the client side on your desktop – in fact VBA forms
can open web forms with a standard openform command).<<

Very nice.


have to update large numbers of rows, then the setup is fantastic.<<

Good to know.

Thanks, Albert!

Neil

"Neil" <neil.ginsberg+newsgroup (AT) gmail (DOT) com> wrote


The instructions were written by the manufacturer with additions information
such as the IP address and security pass code.

Following is from SonicWall

If you are outside of the offices, and need connection to the network you
can perform a remote VPN connection to the office using the SonicWALL
Global VPN Client. NOTE: When you enable this connection for the first
time, you will need a key that I provided you. Also remember, you cannot
connect to your VPN from within your office. You will need to be working
remotely to test this connection.
You can find the Sonicwall Global VPN client from this web site ..
http://msec.twcbc.com/login_v2/tw/gvc_400.zip
After installation:

1) Click "New" from the menu bar, and select "Connection".
2) When prompted, choose "Remote Access" and click next.
3) For IP address, enter: xxx.xxx.xxx.xxxand click next.
4) Check the other options as desired and complete the configuration.
5) Right click the connection and choose "Enable".
6) You will be asked for a pass code "xxxxxxxxxxxxxxxxxxx".
7) You will be asked next for a username and password (Setup on the firewall
or Active Directory)
8) You may be asked to provide this information a second time before
getting connected. This is normal for a first connection by a new user.
9) You should now be able to reach any host on any one of the locations
networks. If you are unable to reach a server or workstation by name, try
using an IP address. Certain forms of Windows generated packets will be
dropped by the firewall, which sometimes cause name resolution problems

"Neil" wrote in message news:jb2bmb$89c$1 (AT) dont-email (DOT) me...


Well, sure but there is a rather large number of users that don't necessary
split their application nor need to.

And often those Access databases are placed on a file share folder. In other
words, for light multi-user or even non multi-user you still get the "many"
benefits of centralized store procedures.

I mean one of the really great benefits of stored procedures, regardless of
a split database is the fact that you have a set of code that runs and
functions at all times in ONE place.

I mean in the past you had to use that before update event of a form. Now
later on if you update that data with some SQL update query or with some VBA
recordset code or just even during testing you edit the table direct then
that supposed code that you wanted to run will not have run.

It's kind of a paradigm change here. In fact I find this approach rather
enjoyable. You set up your tables, your relationship designs, and then set
up some particular store procedures that run. You can THEN go and build the
forms and user interface. It is a marvelous development paradigm, and one
that I wish we had sooner in access. I find it makes the development process
more delightful, because you're doing different tasks at different times.
You have off loaded some of the business logic and code writing to a
different task and "time" during development. This is much like getting your
normalized tables designed really well. When you have really great table
designs, then the rest of the application development process becomes rather
enjoyable.

In fact with good data designs, the rest of the application practically
writes itself.

So with less coding to worry about when building forms then you are able to
concentrate on just simply building the UI. I don't have to worry about
some business logic that supposed to update or maintain some value in a
table that was buried away in the application five years ago by some other
developer that you don't know about.

Thus in my soundex example, you can edit data directly in a table, create
some VBA record set code to update a table, execute a SQL update query, or
in the future build a couple of new forms to edit some data. In ALL cases
one thus never has to worry or care about to "make sure" some code runs to
update and maintain the soundex conversion codes which is needed for the
application to correctly run.

Prior to these table triggers and store procedures, you did not have this
benefit and architecture to utilize in your daily development of
applications.

So I would say just because we can't necessarily use some of the store
procedures in a split database in no way is mutually exclusive to the fact
that we still do receive a lot of benefits of all this new centralized or
table code model.

Well I somewhat clarified that we can't use a split database and call those
store procedures.
This does however as mentioned not eliminate many benefits we can receive
here.

The only thing I want to be clear of here is that we were/are talking about
the jet(ace) engine here. And when using this engine then code that runs
ALWAYS executes on the client side, not the server side in a file share mode
(I'm reasonable sure this was clear to everyone here, but I want to make
doubly so).

What kind of server you're talking about here?
A SQL server,
SharePoint server,
or just a file share folder with a accDB file?

This makes a difference now, just like it always did in the past.

Yes, but this is no different than how SQL commands are parsed or indexing
and things like referential integrity or anything else works as part of the
JET data engine now. This just means that the definitions of the indexing,
RI etc. is defined in the accDB back end file, but it is the JET engine on
your FE that executes all that stuff. So this is also what occurs when
you're running those table triggers. It simply part of the data engine along
with RI and things like cascade deletes etc. We define those cascade deletes
in the table, but that deleting and running of the cascade delete code
occurs on your FE and on your desktop since that is where the JET engine is
running.

So this is really no different in the past in that all indexing +
definitions + everything else is defined in the back end. The engine that
"uses" this information is the jet engine running on YOUR desktop. So your
desktop is still what executes that information. The exact same thing
applies to the table triggers and calling store procedures. At the end of
the day all this is occurring at the engine level, and not application or
VBA level. The cascade delete that we always had runs on your desktop - not
the server side. Same goes for trigger code and trigger code that calls
those store procedures.

Why would this be so? Many people use ODBC to Oracle databases, or SQL
server databases and there's all kinds of triggers that run and those
triggers often call stored procedures.
The user of the application via ODBC was never using or calling those store
procedures directly anyway and in fact probably never cared.

This is a relatively common set up, so I'm not really sure where you're
going with this issue?

Why? Who says when you using an oracle database by a ODBC, the fact that the
database engine supports triggers and store procedures NEVER implied that
you are going to necessarily call or utilize those store procedures via the
particular connection in question. You certainly might wanna do this, and
you certainly make a great case that one would often want to do this, but
it's still a mutually exclusive issue here.

Because it occurring at the table level, and it means that you've moved
application logic out of your UI interface, and you've moved it to a
different layer.

For access developers this was never really much of a important concept
(especially for typical file share based system). However for server based
systems like SQL server, and especially the move towards web based
development, then this architecture change is a critical paradigm change
that we developers will have to embrace in the future.

We don't know who going to be updating the data. It might be your VBA front
end, might be a web browser running on your SmartPhone. And worse it might
be neither and it might NOT even be some type of traditional ODBC connection
either. This "agent" that updates the row of data might be some type of
workflow software running on the server written in .net. (and it might not
even be using SQL to update that data!).

Without triggers and store procedures means we have to write the code logic
for EVERY client. That means we have to write the code in our VBA forms. And
suppose we write a new applications for the iPad or native application for
the iPhone. Without this table code then we have to re-write and re-produce
the update code for the iPhone, your VBA, your .net code and anything else
in your company that might come along and update that data.

So we come along and hit that data with a web browser? Now we going to write
the update code in a web browser?

And this list just goes on and on - what about some web service that comes
along and needs to chomp at and manipulate that data?.

I mean the general philosophy and overall concept of this is a long time and
well known concept in the information technology industry.

In this amazing connected world of web browsers, smartphones, and tablets,
we can't simply write the code in the front end like we always did in
access. When you move towards web based development systems, then you tend
to move towards an application or business logic layer that is independent
of the user interface. This is something we've never had native to access.

We now have this option to utilize in our designs, and this is regardless of
being able to call store procedures (or not) from the front end UI.

Why? At the end of the day prior to 2010 meant that any update to data means
that your form code or VBA code ALSO HAS to update that data.
And a SQL update query you run would have to reproduce the logic also. In
fact with a SQL update query it is rather difficult if not impossible to get
code to run for each row you update. So now we have to execute the update
query, and THEN call some VBA code (and you have to remember to do that!).

So even in a split envirnment the trigger code does run, the only difference
is in a split environment you can't necessarily call those store procedures
from your FE, but the table triggers most certainly can call those store
procedures.

Well, how in the past could you have some code run when you execute an SQL
update query?
You simply did not have this option available.

MORE importantly here is in the past you had to build that code logic into
the user interface, and now you don't.

The syntax and how you do this is un-changed for published or even non web
databases.

So for non web, and non split VBA applications or in fact when using a Web
based with SharePoint, the syntax remains the same.

To be honest the macro language is setup MUCH better to call those routines
but it CAN be done from VBA.

However, since the VBA developer is about to call some code FROM some VBA
code then one perhaps just call some VBA code anyway?

(you don't gain performance benefits in a file share, but I suppose calling
the store proc does give the advantage(s) I spoke about above).


The code would look like this:

TempVars.Add "strName", "Albert"
DoCmd.SetParameter "sText", "[TempVars]![strName]"
DoCmd.RunDataMacro "customersM.soundex"
Debug.Print ReturnVars("strSoundex")

So the above calls my soundex code that runs at the JET level (ok, now
called ACE).

Note how a expression must be sent as a parameter. You can use a form
control name such as me.SomeTextBox, but I don't believe that you can use a
VBA var direct just like you cannot in a SQL statement
(so you have to use something that is part of the expression service in
which Access can get it hands on).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

Great. Thanks so much. This has been very helpful!

Neil