SQL Server

[Candance]

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.

 

[Arvin Meyer [MVP]]

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.

 

[GBA]

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

 

[David W. Fenton]

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]

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]

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.

 

[Banana]

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.

 

[David W. Fenton]

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.

 

[Banana]

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.

 

[Arvin Meyer [MVP]]

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.

 

[Banana]

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.

While RDP or VNC in general is also an effective technique and was
already mentioned by GBA, that was not what I used at all. The driver I
was referring to was MySQL's ODBC driver which, like all other ODBC
drivers, has to be installed on the client machine. In the application I
was describing, Access interacts with MySQL ODBC driver which in turns
gets data from remote MySQL server across WAN as well updates data. This
technique has worked well, and I've seen other report using remote SQL
Server database across WAN as well.

Of course, there are security concerns for connecting across WAN but
that's generally outside the scope of Access application anyway. In the
mentioned application, the connection has to be encrypted and server
will accept connections only from a small list of approved IPs.

 

[Armen Stein]

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.

Hi Arvin,

The OP's app might need a rewrite, but I disagree on the use of
unbound forms. We hardly ever use unbound forms, and our Access-SQL
Server apps perform well, even over WAN connections. We just make
sure that most processing is done by SQL Server, and that we don't
open large updatable recordsets. But bound forms are one of Access'
greatest strengths, and we use them extensively.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[erdalllamine]

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.

 

[henry bril]

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.