------ Register to get rid of these "In Post" ads! ------


I have developed an Access database with confidential data and now the client
wants multiple users (less than 10) to have remote access from home. I would
like to split the database so users can have Access forms, etc. on front end,
and upsize the back end to sql server for stability of data transmission.
Can I use one of the free sql server 2005 products for the back end? If so,
which one? Can one of them be downloaded to my PC for testing? This is
totally new to me, in case you couldn't tell! Any advice would be
appreciated.

------ Register to get rid of these "In Post" ads! ------


The answer is yes SQL Express is the version you want. I'm not sure that
it's available as a download anymore. You may want to use SQL-Server 2008
instead.

If you aren't going to rewrite your front-end to use unbound forms, you
won't get any performance improvement by using SQL-Server over Access.
Instead, you may want to think about using a terminal Server or Citrix
Server. With that, you won't need to Change from Jet (MDB) but you may want
to anyway.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Candance" <Candance@discussions.microsoft.com> wrote in message
news:4DD0DA59-8E69-462E-B992-1C96DC707ABE@microsoft.com...
>I have developed an Access database with confidential data and now the
>client
> wants multiple users (less than 10) to have remote access from home. I
> would
> like to split the database so users can have Access forms, etc. on front
> end,
> and upsize the back end to sql server for stability of data transmission.
> Can I use one of the free sql server 2005 products for the back end? If
> so,
> which one? Can one of them be downloaded to my PC for testing? This is
> totally new to me, in case you couldn't tell! Any advice would be
> appreciated.

------ Register to get rid of these "In Post" ads! ------


Access is not designed to operate across the internet (or WAN). Changing the
BE to sqlserver does not change this reality. In large corporations that
have private high speed WANs - that approach LAN capability - they implement
Terminal Services.

Generic advice on remote Access database options where one has only generic
internet and not a high speed private WAN with Terminal Services capabilities…

Option 1 – stay with Access’ embedded replication feature (if .mdb format –
not available with .accdb 2007 format) presuming you can co-locate or vpn
them together.

Option B is to go with web architecture. find a web developer - turn over to
him/her your Access db for them to look at as the prototype design...and get
their quote. You will pay to have them develop it and then there will be the
recurring for the hosting company...

Option III is the www.AccessTables.com service; this allows everyone to
operate a copy of the Access db locally and then you send in the tables -
they consolidate/replicate all the data - - and return to everyone a
consolidated set of tables. Is great as long as the requirement is not for
instantaneous shared data. If periodic updates is sufficient and the user
base are all part of a team - this can be a good solution.

Option 3.5 is an online commercial db service - I would recommend
DabbleDB.com Intuit also has a product : QuickBase - but it is quite
pricey and intended for corporate users. Dabble is very reasonably priced
and pretty cool. When using a db service you are in their sandbox and must
live with the features and look they offer; the redesign using Dabble is a
bit of work & learning and there is definitely missing many features one
takes for granted in Access.

Option 5 is to have users get into Access using a commercial VPN service
such as www.GoToMyPC.com. This will also have a monthly fee. The PC must
always be on for the user. A bit of a latency/lag experience. Only one user
can log on at a time...and, importantly, the log on user will have the
ability to see everything on the PC - not just the Access application....

Option VI assuming you have Access07 or later in .accdb format you can get a
sharepoint hub and use the publish/off-network function as a mode to work
locally but push the data back & forth to a sharepoint hub. But sharepoint
itself is another big element to manage and you might look to a shared
sharepoint service from Microsoft or others.

No right or wrong per se – just right or wrong for one’s situation…finding
the right tool for the job…or redefining the job for the available tool…..








"Candance" wrote:

> I have developed an Access database with confidential data and now the client
> wants multiple users (less than 10) to have remote access from home. I would
> like to split the database so users can have Access forms, etc. on front end,
> and upsize the back end to sql server for stability of data transmission.
> Can I use one of the free sql server 2005 products for the back end? If so,
> which one? Can one of them be downloaded to my PC for testing? This is
> totally new to me, in case you couldn't tell! Any advice would be
> appreciated.

------ Register to get rid of these "In Post" ads! ------


"Arvin Meyer [MVP]" <arvinm@mvps.invalid> wrote in
news:urZtsvvpKHA.1548@TK2MSFTNGP02.phx.gbl:

> The answer is yes SQL Express is the version you want. I'm not
> sure that it's available as a download anymore. You may want to
> use SQL-Server 2008 instead.
>
> If you aren't going to rewrite your front-end to use unbound
> forms, you won't get any performance improvement by using
> SQL-Server over Access.

That's not entirely true. There are certaink kinds of operations
that may very well be speeded up by the move to SQL Server.
Unfortunately, there will likely be an equal number of operations
(or more) that are *slower* after the move. On balance, you're as
likely to see an overall slowdown as you are to see no change at
all. In my opinion, it's extremely rare for there to be any
noticeable overall increase in performance.

A wholesale rewrite with unbound forms is not at all necessary to
gain performance (and I'm doubtful that doing that is going to
enhance performance to any significant degree). Instead, you locate
the bottlenecks and move them server-side, and that generally gets
you the best performance possible, without a wholesale rewrite of
any kind.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

------ Register to get rid of these "In Post" ads! ------


=?Utf-8?B?R0JB?= <GBA@discussions.microsoft.com> wrote in
news:C8E396CF-8A69-4AEB-BD7B-A04F02A1E231@microsoft.com:

> Option 1 ƒ " stay with Accessƒ T embedded replication feature (if
> .mdb format ƒ " not available with .accdb 2007 format) presuming
> you can co-locate or vpn them together.

This has its own set of issues, since Jet replication works only
with LAN-speed network connections (10Mbps or higher). It's also
much more complex to set up synchronization across a WAN/VPN than it
is to do it on a LAN.

Jet Replication Wiki:

http://dfenton.com/DFA/Replication/

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

------ Register to get rid of these "In Post" ads! ------


=?Utf-8?B?R0JB?= <GBA@discussions.microsoft.com> wrote in
news:C8E396CF-8A69-4AEB-BD7B-A04F02A1E231@microsoft.com:

> Option III is the www.AccessTables.com service; this allows
> everyone to operate a copy of the Access db locally and then you
> send in the tables - they consolidate/replicate all the data - -
> and return to everyone a consolidated set of tables. Is great as
> long as the requirement is not for instantaneous shared data. If
> periodic updates is sufficient and the user base are all part of a
> team - this can be a good solution.

This is the same as Jet replication, but you've turned over the guts
of your application to a 3rd-party service. What if they go out of
business? What if you need to change the schema?

There are a whole lot of problems with that as a solution, but my
main objection is that you're completely beholden to an outside
party who may not be in business 5 years from now, or who may try to
charge you an arm and a leg for reasonable changes that come up on a
regular basis.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

------ Register to get rid of these "In Post" ads! ------


GBA wrote:
> Access is not designed to operate across the internet (or WAN). Changing the
> BE to sqlserver does not change this reality.

I would disagree. I've implemented an Access application connecting to
MySQL across WAN and got great performance. This is because unlike an
Access backend, there is no file swapping going on across the network -
the driver <-> daemon handles all the communication across the internet
while Access interacts with the driver locally, and Access has proved to
be a very excellent front-end client, intelligently fetching and caching
data.

------ Register to get rid of these "In Post" ads! ------


Banana <Banana@Republic> wrote in news:4B6DF902.3090709@Republic:

> GBA wrote:
>> Access is not designed to operate across the internet (or WAN).
>> Changing the BE to sqlserver does not change this reality.
>
> I would disagree. I've implemented an Access application
> connecting to MySQL across WAN and got great performance. This is
> because unlike an Access backend, there is no file swapping going
> on across the network - the driver <-> daemon handles all the
> communication across the internet while Access interacts with the
> driver locally, and Access has proved to be a very excellent
> front-end client, intelligently fetching and caching data.

While I agree that it's doable, I would agree with GBA that Access
was not really *designed* for that. It works, but that wasn't part
of the built-in design. Indeed, you have to avoid many of the
default Access behaviors in order to make it work efficiently, which
is a pretty good indicator that you're working against Access's
fundamental design.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

------ Register to get rid of these "In Post" ads! ------


David W. Fenton wrote:
> While I agree that it's doable, I would agree with GBA that Access
> was not really *designed* for that. It works, but that wasn't part
> of the built-in design. Indeed, you have to avoid many of the
> default Access behaviors in order to make it work efficiently, which
> is a pretty good indicator that you're working against Access's
> fundamental design.

I'm not sure what "wasn't part of the built-in design" we would be
referring to. In this application I cited, I used the standard
techniques available in Access development (e.g. linked tables, bound
forms, saved queries, some VBA) and for most part used the default
behaviors where they were appropriate. The only difference would be in
the networking layer which is technically outside the Access application
domain anyway.

As I said, Access is interacting with the locally-installed driver,
which subsequently handles the connection across WAN so there's nothing
within Access interacting with the network.

------ Register to get rid of these "In Post" ads! ------


"Banana" <Banana@Republic> wrote in message
news:4B6F83D5.1020006@Republic...

> As I said, Access is interacting with the locally-installed driver, which
> subsequently handles the connection across WAN so there's nothing within
> Access interacting with the network.

What you are describing is an RDP or Terminal Services WAN connection, and
yes that works nicely. You do need to remember not to share the front-end
though. I normally do that by making sure that each user has his/her own
copy of the front-end in their own folder.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com