Let's say you have a web hoster that supplies SQL Server.  Can Access 
link to the SQL server on the hosting site and use the database?

On Tue, 16 Nov 2010 14:33:14 -0800, Salad <salad@oilandvinegar.com>
wrote:

>Let's say you have a web hoster that supplies SQL Server.  Can Access 
>link to the SQL server on the hosting site and use the database?

Salad, I read a while back that it is possible but I can't remember
the article or how to do it!  I am not sure if it has to be Access
2010.  I think it was something like linking the tables to the web (I
believe the backend was SQL Server).

My memory stinks!

-paulw

?"Salad"  wrote in message=20
news:6b-dnTFDtYExm37RnZ2dnUVZ_oadnZ2d@earthlink.com...

>Let's say you have a web hoster that supplies SQL Server.  Can Access =
ink=20
>to the SQL server on the hosting site and use the database?

The answer's yes and I've been doing this for a number of years. However =
I=20
should probably point out that there is a distinctive difference between =

using the general term cloud computing as oppose to as purchasing some=20
hosted services. So today the popular press is using cloud computing in =
this=20
sense of the resources you're going to use are going to be shared among =
many=20
many people. (however I will  leave the semantics and differences =
between=20
these two terms for another day).

Access can link to SQL server on the hosting site if the particular =
provider=20
you're going to allows such an arrangement. So not all providers allow=20
certain ports and ODBC connections to occur to database system running =
on=20
their servers. However, it a lot of cases they do since this means you =
can=20
use the SQL server management tools to remotely administer that =
database,=20
and is another advantage is such as allowing client based applications =
like=20
access to use that server an back end or to often pull down data from =
that=20
SQL server running on the hosting site on a regular basis such as =
gathering=20
up some survey data over a period of time for example.

Also access 2010 also has baked into it support for what we call the =
Azure=20
addition of SQL server.  In another words there is an addition a SQL =
server=20
that specifically written for the cloud based operating system from=20
Microsoft called azure (azure is the name of this new cloud based =
operating=20
system). Once again you can purchase SQL server resources that run on =
this=20
Azure OS in this way, and you don't even need to purchase web hosting in =

this manner. I believe prices started $10.00 per month for 1 GB database =

with sql running on the Azure operating system. However there are =
additional=20
details such as bandwidth issues that you'll have to look up for =
pricing, I=20
don't have those numbers handy right now.

So regardless you're going to consume resources from a cloud based =
operating=20
systems such as azure, or you're going to go to some web hosting =
provider=20
that allows you to directly connect to their database server running, =
these=20
are most certainly two possibilities


As I've noted, I been deploying access applications for a number of =
years=20
and have been using a web provider that comes with hosted SQL server and =
it=20
allows odbc connecitons. The beauty is is then any laptop within the=20
organization can simply install my software and it's simply runs =
anywhere=20
they have an Internet connection, including the local coffee shop. I =
never=20
have to worry about where the back end is, or who has a server, or =
whenever,=20
they just simply install my software and are all online at the same =
time.=20
About the only issue in this setup is you of course have to write your=20
software quite carefully to be very bandwidth friendly because you're =
using=20
an Internet connection which is often many times slower than the typical =

Office Network connection you have.

--=20
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal@msn.com=20

Albert D. Kallal wrote:

> ?"Salad"  wrote in message 
> news:6b-dnTFDtYExm37RnZ2dnUVZ_oadnZ2d@earthlink.com...
> 
>> Let's say you have a web hoster that supplies SQL Server.  Can Access 
>> ink to the SQL server on the hosting site and use the database?
> 
> 
> The answer's yes and I've been doing this for a number of years. However 
> I should probably point out that there is a distinctive difference 
> between using the general term cloud computing as oppose to as 
> purchasing some hosted services. So today the popular press is using 
> cloud computing in this sense of the resources you're going to use are 
> going to be shared among many many people. (however I will  leave the 
> semantics and differences between these two terms for another day).
> 
> Access can link to SQL server on the hosting site if the particular 
> provider you're going to allows such an arrangement. So not all 
> providers allow certain ports and ODBC connections to occur to database 
> system running on their servers. However, it a lot of cases they do 
> since this means you can use the SQL server management tools to remotely 
> administer that database, and is another advantage is such as allowing 
> client based applications like access to use that server an back end or 
> to often pull down data from that SQL server running on the hosting site 
> on a regular basis such as gathering up some survey data over a period 
> of time for example.
> 
> Also access 2010 also has baked into it support for what we call the 
> Azure addition of SQL server.  In another words there is an addition a 
> SQL server that specifically written for the cloud based operating 
> system from Microsoft called azure (azure is the name of this new cloud 
> based operating system). Once again you can purchase SQL server 
> resources that run on this Azure OS in this way, and you don't even need 
> to purchase web hosting in this manner. I believe prices started $10.00 
> per month for 1 GB database with sql running on the Azure operating 
> system. However there are additional details such as bandwidth issues 
> that you'll have to look up for pricing, I don't have those numbers 
> handy right now.
> 
> So regardless you're going to consume resources from a cloud based 
> operating systems such as azure, or you're going to go to some web 
> hosting provider that allows you to directly connect to their database 
> server running, these are most certainly two possibilities
> 
> 
> As I've noted, I been deploying access applications for a number of 
> years and have been using a web provider that comes with hosted SQL 
> server and it allows odbc connecitons. The beauty is is then any laptop 
> within the organization can simply install my software and it's simply 
> runs anywhere they have an Internet connection, including the local 
> coffee shop. I never have to worry about where the back end is, or who 
> has a server, or whenever, they just simply install my software and are 
> all online at the same time. About the only issue in this setup is you 
> of course have to write your software quite carefully to be very 
> bandwidth friendly because you're using an Internet connection which is 
> often many times slower than the typical Office Network connection you 
> have.
> 
Thanks for the reply, Albert.  How is the speed of the app?  Is there 
degradation or are the responses acceptable?

Can you make a table modification while the app is used?  For example, 
let's say you have a customer table.  You want to add the field 
DateCustAdded.  Can that be updated while the system is being used?

How do you distribute updates to the FE system?  I wonder if Tony's 
AutoFE could be used to DL the most current update from a central location.

On Tue, 16 Nov 2010 18:13:16 -0800, Salad <salad@oilandvinegar.com>
wrote:
  
>How do you distribute updates to the FE system?  I wonder if Tony's 
>AutoFE could be used to DL the most current update from a central location.

Currently the Auto FE Updater can only download updates from a file
server.  In the near future I will be adding the ability to download
updates from a website.   But this will likely be at least a month or
two out.

Also note that this functionality will not be free.  Indeed the next
version of the Auto FE Updater won't be free either.

Tony
-- 
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files 
  updated see http://www.autofeupdater.com/

On Tue, 16 Nov 2010 18:13:16 -0800, Salad <salad@oilandvinegar.com>
wrote:
  
>How is the speed of the app?  Is there 
>degradation or are the responses acceptable?

A fellow MVP was an expert on ADPs.  He stated that he was able to use
his app file on a 56 kbps modem connection.  It was very sluggish but
it did work.

>Can you make a table modification while the app is used?  For example, 
>let's say you have a customer table.  You want to add the field 
>DateCustAdded.  Can that be updated while the system is being used?

In my limited testing somethings like that worked and others such
schema changes didn't work.  I have no idea what the differences were
and why.  And it was a number of years ago.

Tony
-- 
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files 
  updated see http://www.autofeupdater.com/

Salad <salad@oilandvinegar.com> wrote in
news:A8mdnY2Omaqjp37RnZ2dnUVZ_sWdnZ2d@earthlink.com: 

> Can you make a table modification while the app is used? 

Of course you can, just like you can with SQL Server running on the
local LAN with ODBC connections. However, you can't really control
exactly when the SQL Server will actually be able to implement your
change, so it's not advisable to be doing this with live users
connected. It's the kind of thing you'd do when everybody's out of
the office. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
contact via website only     http://www.dfenton.com/DFA/

????"Salad"  wrote in message
news:A8mdnY2Omaqjp37RnZ2dnUVZ_sWdnZ2d@earthlink.com...

>Thanks for the reply, Albert.  How is the speed of the app?  Is there
>degradation or are the responses acceptable?

>Can you make a table modification while the app is used?  For example,
>let's say you have a customer table.  You want to add the field
>DateCustAdded.  Can that be updated while the system is being used?

>How do you distribute updates to the FE system?  I wonder if Tony's =
AutoFE
>could be used to DL the most current update from a central location.

You actually can risk making a table modifications while users are in =
the
application. I suppose it depends on the kind of modifications, but for
example the case that you're only adding another column, then this is
actually allowable and the currently existing deployed application won't =
see
that additional column or changes until you issue a new front end. And
sql server allows this even when active users are in the table.

And of course before you reissue this new front end, you obviously will =
run
your RElinking routines to the correct back end sql server anyway.

At the end of the day, this whole process is not a whole lot different =
than
a standard split database now. You are going to work on a copy of the
application that is linked to a test data backend during the testing and
development phase. So it only right before deployment would you link to =
the
production data back end anyway.

In fact if you've used SQL server, unlike the case where you have a =
split
front and back end mdb file share, when you use linked tables to SQL =
server,
modifications to the back end for the most part don't show up in the =
front
end until you RE link anyway. So, one must much has to re-link even =
during
development to the back end when schema changes are made.

I think like anything I would not regularly attempt to modify the =
production
system while users are working. During my working and testing, if I add =
a
few new columns to the test database, during that phase I simply right =
click
on the table in the sql desing tools and select script change to =
clipboard.
I then paste that into a simple text document called changed.

Then when I'm ready to deploy to
production users, I simply run those change scripts I piled up into the
that change text document. In fact, I connect to the
production web database with The SQL server management studio, and =
simply
cut paste the script changes into a query session, and run it. I will =
then
re-link the new front end, and upload that to my web site.

Since all the users are on the web, then I have my application download =
a
new front end from the web that Is packaged up as an inno .exe install.

While I have a fancy download system built into my software now,
realistically my previous edition of this used about three or four lines =
of
code to directly execute the install package from a web site. All you do =
is
execute a application.Followhyperlink to the web url where the .exe
resides and the web browser will start the download.
Then the next line executes an application quit. You done.
Updates via web are really easy.

You don't Even need any special kind of update software to
do this, because your exited your software, and you're executing a =
download
command from a web site with the Followhyperlink. I use these two lines =
of
code for years for my clients, but I did not like the additional =
security=20
prompt
you get from the browser, so I built in an FTP download system .

Now, here is the series of screen shots as to how users now upgrade my
software via the Internet :

http://www.kallal.ca/ridestutorialp/upgrade.html

As for performance, it's remarkably well. If you think about the amount =
a
text you pull down in a web browser to fill a screen. So if you pulling =
down
only 20 to 30 rows of data into an access form, it works really well. In
fact, I have a full screen Calendar display that pulls data from a sql
database, and it works amazingly well . In fact in some cases I would =
say it
works as well as a split database on an office LAN, despite the user =
being
connected over wireless Internet connection at a coffee shop.

There's no question that You have to optimize your queries, and be very
careful about bandwidth, and any type of query that uses aggregate =
totals
etc. needs to be converted to a view, and you use a link to that view.
You thus need to follow some simple rules and be careful about =
performance,
but you be more than surprised how well the setup works .

--=20
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal@msn.com=20