Might be outgrowing Access but daunted by SQL Server

On 26 Feb 2010 21:57:16 GMT, "David W. Fenton"
wrote:

With a SQL Server, you have to have a port for client PCs to connect
to but the big question is where it is available. You could expose
it to the public Internet like you do your web server, but that
would be extremely dangrous.

Hi David,

Lots of SQL Server databases (publicly and privately hosted) are open
outside their domain for external usage without a VPN. SQL Server is
pretty secure if you follow best practices. You can also use other
methods like restricting IP addresses in your firewall. Also, by
definition SQL Azure will be open to the "public Internet" and will
use IP address restriction, so we might as well get used to it.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Jon22 wrote:

Can anyone suggest a simpler way of achieving remote access to my database?

Now one of the things I didn't see mentioned in the discussion is will the folks who
access your data remotely from overseas always be on a reasonably reliable Internet
connection? If so then a VPN to SQL Server, Terminal Server or Sharepoint would work
well.

If not then you'd have to look at replication, either Jet or SQL Server or possibly
SharePoint has enough capabilty that you could go away from the home network for a
week or two. Of course if replication is required then backups need to be done while
on the road in case the laptop is lost/stolen/etc.

And likely you want the laptop hard drive to be encrypted such as using Windows
Vista/7 Ultimate Bitllocker.

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/
Granite Fleet Manager http://www.granitefleet.com/

"Banana" Banana@Republic wrote in message
news:4B87C75A.8050100@Republic...
While this is technically accurate, this is conceptually misleading
because in client's interaction with server, the question of "where is the
.mdf?" will never be asked by the client. In fact, a properly set up

While this is technically accurate, it is conceptually misleading,
because in a clients interaction with an application, the question of
"where is the database" will never be asked by the client.

If it is asked by the client, it works the same way: to use the
application, you use the connection string, to connect to the
service which provides the records.

server would _deny_ access to the users to those files. Only the
daemon (aka SQLSERVER service) or the administrator would have access to
those files.

Yes, only the daemon (aka SERVER service) has access to those
files. Yes, if you have the correct permissions, you can request that
data from the service. Yes, the permissions and protocols are
different, and allow you to do different things.


Thus, it's important to understand that you do not "browse and open a
file" as you would with other applications such as documents or

You do not browse and open an MDB or MDF file, unless you
want to. Normally, you connect a database application to a database.

Yes you can browse MDF files, either using a file browser or OSQL
or SQLCMD or Active Directory, or using an AD addin or etc.

Yes you can open MDF files. Using OSQL or SQLCMD or etc.
Yes you can open MDB files. Using Access or whatever.


spreadsheet, but rather "connect to" a server using one of many
protocols... TCP/IP, Named Pipes, Named Memory, and then some more.

Yes, you can connect to the SERVER service using one of many
protocols, TCP/IP, Named Pipes, Named Memory, and then some more

thus the question of "where is the file" is totally nonissue

It's a total non issue to the end-user.

However, it is not a non-issue for a person setting up a system,

Unless the end-user is also administering the logical location of
files and the physical architecture of the file-server.
Which is what the Personal Computer revolution was all about,
so you may be familar with that kind of behaviour.

because that's the daemon's job to manage it and you communicate with the
daemon.

You communicate with a daemon regardless of whose job
it is to organise the logical location of files and the physical
architecture of the server.

There are two points the

1) It's just services communicating with services. It all works
that way regardless of what kind of database you are using.

2) Databases map to URI's because someone set them up
that way. It is all mapped: none of it is physical.

Many people are familier with the simplest way a file mapping
may be set up using SERVER. Far fewer people are familer
with even the simplest way to setup a file mapping using SQL
SERVER.
Actually, it's no more difficult, and no less flexible, it's just
unfamilier.


Where does it get reside?

Like Access, they reside where you put them. If you want to share them,
you put them somewhere where they can be shared. Then you set up the
shares so the user can use them.

No. As I explained, daemon does all file handling here.

Yes, as I explaned, the server does all the file handling, and presents
it through the SERVER service and a client at the workstation. At the
workstation, you ONLY deal with the CLIENT service.

Therefore a proper setup will _deny_ the users access to the files. All

Right, this is a difference between the two services.

communication should go through the daemon.

Yes, communications always goes through Windows Services.

So you never put .mdf and .ldf files in a share folder

Conversely, if you want to use the Access/Jet, you never
put the MDB in a Master database.

but rather merely open the port 1433 for TCP/IP or use Named Pipes or
whatever for the clients to connect to.

Yes, if you want to use the native windows database primatives
through the SERVER service you

merely open the port 139 for SMB

This is why if you look at various ODBC connection strings, the only time
you do see a filepath is when you're working with a serverless source
(e.g. a .mdb will have its filepath in the ODBC connection, but you'll
never see that for a Oracle, MySQL, DB/2, SQL Server!)

Databases aren't bits of sand, or bricks. You use a mapping
syntax which depends on the ODBC driver, and you map to
names which may, or may not, mean something to the end user.

For example, it is conventional to use a similar name for the
logical name of a SQL Server database, and for the logical
name of the MDF file in the NTFS file record.

But the most important thing you got wrong is this:

working with a serverless source (e.g. a .mdb

Look at the second word of this phrase:

File Server

There is a basic misunderstanding that constantly confuses people.
Mostly, they get confused because they don't realise that a file
server is a server, and that messes up their understanding of how
Access/Jet works. That is not relevant here, so I won't say anymore
about it.



connectionstrings.com

To be crystal clear: The users do not need and should not have
access to the .mdf and .ldf files!!

What does that have to do with Port Numbers, Services, or where
you put the database files? Nothing. So what are you on my case
about?



I do believe that "Server" service is not actually germane to the
discussion here. If you read its description, it says it enables file,
print and named piped sharing over the internet. That what it does. SQL
Server services, OTOH, enables clients to connect to this machine and
issue requests for data.

The SERVER service, on the other hand, enables clients to connect
to this machine and issue requests for data. Using the SMB protocol

If you want to learn more about the SMB protocol, which includes
the Record requests, and the Record Locking requests necessary
to support the native Windows database primitives, you will have
to read more than just the 'description' of the SERVER service.



In fact, if I turn off "Server" service, I still can connect to SQL Server
on my SSMS, and reach a Sharepoint site remotely...

In fact, if you turn of the the SQL SERVER service, you can
still connect to the SERVER service, and reach your Access
database remotely (or any database which uses the native
Windows database services)

IOW, "SQL Server" service and "Sharepoint" service are not dependent on
the "Server" service. There is of course the notable exception that if I
wanted to use Named Pipes, then yes, I do need "Server" service. But with
TCP/IP, then no. They have nothing to do with each other.


I'm not sure what your point is? SERVER and SQL SERVER
and IIS are different services.

SERVER is the service (daemon if you will) used for SMB
IIS is the service (daemon) used for HTTP and others
SQL SERVER is the service used for OLEDB and others.


As I explained in my previous reply to Jon22, the term "server" actually
refers to a role, rather than some kind of program.

Yes.

A server usually has a daemon (aka Windows service) that listens on a
specific protocol (e.g. a port on TCP/IP, a region of address for Named
Pipes/Memory, etc.) and react to the requests through there.

Yes.


It goes back to why it's important to not be so concerned with the
questions such as "where is the file?" because those are wrong questions
to ask.

Except when you are setting up your servers or trying to
understand what a 'server' or trying to understand
what a 'service' is

And you've come in here and told us the answer to some completely
different question. You've told us that with the SQL Server, (as with
Access) the end user doesn't need to know where the file is physically.

And then you've told us that the original questions were 'wrong'.

But you've been so quick, you missed an important point: the OP
is not an end-user. He still has to work out where to put the files.


You will have to completely re-write that stuff in TSQL.

It's not a lot more difficult, but it is different.

I would question that. A well-written Access application usually will run
on any backend, be it Oracle, MySQL, DB/2, SQL Server with minimum change
(e.g. using linked tables). It all depends on whether one has written the
application specifically for server-client architecture in mind, of which
also benefits even ACE engine.


The original poster has an application that will not run on "any
backend"

He has clearly described the problem, and the observed behaviour.

You have implicitly equated "A well-written Access application"
with "written ... specifically for server-client architecture".

I would question that, but what difference? His application clearly
is written specifically not for server-client architecture, whether or
not you call it "well-written"


There are times and places where re-writing a piece of functionality into
T-SQL, stored procedure, views or other SQL Server objects does make
sense,

This specific case. This specific case about the specific problem
as described in this specific thread.


but I really don't believe it requires a wholesale rewrite.

Well, I haven't seen the application, and you haven't seen the
application, but at least I never suggested a wholesale rewrite.

I suggested a "complete re-write" specifically of the stuff
which is not compatible with a "server-client" architecture"

"You will have to completely re-write that stuff in TSQL"


But that is largely a function of how well written the application was to
begin with.

And you finish off be again implicitly equating "well written"
with "specifically for server-client architecture."

You know, telling people that you think their questions are
wrong, and suggesting that a clearly described application
architecture is not well written, is mildly insulting at the best
of times, but I think that if you manage to read down this far
you've done your penance.

(david)

Jon22 wrote:
Thanks Banana! Definately cleared away some of the fog.

Glad to hear that.

Let me know if I'm off the mark here, but I think the best approach for me
is going to be: Copy the tables from my Access database to a SQL Server
database then split the Access database, delete the linked tables from the FE
database and then relink to the tables residing on the SQL database (which
should have exactly the same table names and field names and datatype as the
ones that were put into the now obselete back end Access database so that the
front end database should still function as it does now)

That can work. You'll need to learn which SQL Server data types to
replace though those are minor differences but good for you to know
nonetheless.

Now, just to be 100% sure, also do consider David Fenton's advice of
using Windows Terminal Server because he is correct that this would
require no rewrite of the application so all you would have to do is
move it to a server and deploy there then you're done. We want to be
sure you've considered all possible solutions and what they means to you
in terms of performance, scalability and cost.

I think that I'll be able to link to the SQL database tables easily enough
from my copy of the front end Access database because this FE .mdb file will
be sitting on the same computer if I install SQL Server on my computer.

What about other clients on our office network? Or even more scary, our
computers in our overseas office? How do they find the SQL database to link
to? Do I need to save the SQL database (effectively our back end database) on
to our network storage device which has a static IP address assigned to it
and ftp & https access or our website or something? Or does my computer
(effectively the server once I install SQL Server onto it) suddenly gain
remote access capabilities even sitting behind our LAN router/moden firewall?

Well, yes, SQL Server has to be accessible from somewhere. Static IP is
a simple way to implement this though you'll definitely want to ensure
you have proper security in place (e.g. accept connections from only
certain IPs that your company owns for example) or use a VPN connection
so the connection to SQL Server is as if it's local, as David Fenton
explained.

From what I can gather, when I link to the SQL database tables I'll need to
create new ODBC files which, from my understanding, in the simplest terms
gives the FE Access database the filepath of the BE database objects to link
to? Will this have to be done for each client computer? Or can I create one
database with the linked tables all done and then send this .mdb file to all
the staff to load onto their hard drives? In other words, are ODBC files only
useful to the computer they were created on?

DSN (which you call ODBC files) are useful to any computer, provided
that they can reach the destination specified on the file. To provide an
example, if you had in your DSN: "Server=192.168.0.100", it'd work for
all computer where it is in the same network with SQL Server (including
those connected via VPN), but that would not work for any client outside
the network because the 192.168.xxx.xxx is a private network IP address;
you'd have to use a public IP address if you want it to be
world-accessible.

So the answer would be "it depends."

As an alternative, you can use "DSN-less connection", means that you
don't have to distribute the DSN, but instead create the link with the
complete paths within code. Doug Steele has an excellent example at his
site:
http://www.accessmvp.com/DJSteele/DSNLessLinks.html

More importantly I guess, am I way off here or perhaps still missing any
vital fundamental steps in my perception of how this is going to all work?

I think you've covered this. As mentioned before, be sure to consider
the other advices as well. If you've decided to press on this route, and
you are interested in some referential materials which also contains
more links, here's two for you:

Beginning SQL Server by Leigh Purvis:
http://www.utteraccess.com/forum/ind...wtopic=1732935

Beginner's Guide to ODBC by myself:
http://www.utteraccess.com/forum/ind...wtopic=1843709


Best of luck!

David W. Fenton wrote:
But if you're contemplating allowing disconnected use, you'll need
the SQL Server running on the laptops and you'll probably need to
implement SQL Server replication in order to keep the laptop
databases synchronized with the central one. Replication is not a
minor issue, and if you can avoid it, you really should.

If OP wanted to use the application in disconnected state, I would
really not want to entertain the idea of running SQL Server Express on
every client machine. That would be too much administration and training.

I'd daresay it'd be easier to just use Access local tables and write
scripts to synchronize when the laptop comes home. I also understand
that SQL Server supports replication with linked server, but I'm not
sure how well SQL Server handles synchronizing with non-SQL Server
sources or whether it'll be practical in this use, though.

I agree that replication is difficult no matter how you set it up.

In general, backing up the live SQL Server files is not going to
give you a reliable backup. It's just like backing up an MDB/ACCDB
that is open by a user -- you may or may not get a valid file out of
it (it's probably even less likely with the SQL Server files, I
would think). SQL Server has a backup agent to take care of backups
for you, but one of the disadvantages of SQL Server Express 2008 is
that the backup agent is not included! Some backup software is able
to talk directly to the SQL Server and get a backup file, but if
that is dependent on the agent, it won't work with SQL Server
Express.

That's why I had qualified my statement as "even then that is not
strictly necessary." Perhaps I should have had used "is not actually
necessary", because in most practical backup usage, we'd be dealing with
..bak files, not .mdf or .ldf files.

David W. Fenton wrote:
Banana Banana@Republic wrote in news:4B87C75A.8050100@Republic:

I do believe that "Server" service is not actually germane to the
discussion here.

It can also not be turned off without disabling your PC, even if you
aren't sharing anything at all.

(I tried it with my first copy of NT, as I wasn't sharing any files)


Well, as I said in my previous reply, I could turn it off without any
ill effects to the connections to SQL Server or Sharepoint. Now, if I
had used named pipes, this would be a problem because Server service
provides this functionality, so in this context, SQL Server service
would have to depend on Server service for named pipes connection.
Otherwise, TCP/IP connection will work with or without Server service.

Maybe it changed since last time you tried it on your first copy of NT?

I don't know why, but there seems to be snippets where it should have
been quoted but wasn't so I had to filter which was your words and which
was my words not correctly quoted.

Anyway...

david wrote:
Yes, only the daemon (aka SERVER service) has access to those
files. Yes, if you have the correct permissions, you can request that
data from the service. Yes, the permissions and protocols are
different, and allow you to do different things.

However, you said in your previous reply:
Like Access, they reside where you put them. If you want to share them,
you put them somewhere where they can be shared. Then you set up the
shares so the user can use them.

The statement sounded like you were suggesting that we could put a .mdf
and .ldf files on a share folder. Perhaps that's not what you meant, but
I was very concerned by that statement. Hence, my emphasis that users
doesn't need access to those files, even to "administer" it because it's
the daemon's job to administer it.

You do not browse and open an MDB or MDF file, unless you
want to. Normally, you connect a database application to a database.

How do you connect to a .mdb file? In Access, you get a file dialog and
browse for a file and choose a .mdb file.

In SQL Server, you will not see such choices in most tasks. The closest
to this would be when you do a CREATE DATABASE.

Yes you can browse MDF files, either using a file browser or OSQL
or SQLCMD or Active Directory, or using an AD addin or etc.

I'm not sure I understand what is the point you're trying to make here.
Sure, I can see the files in the Windows Explorer, but in the SSMS, I
don't get asked for the physical location of database I want to connect
to. Same with SQLCMD. I'm not sure what AD has to do with this here.

Yes you can open MDF files. Using OSQL or SQLCMD or etc.

No. It's daemon's decision to open the file - it may be already open
before you connect to the database contained in the .mdf file. The point
is that daemon lives on its own and does its own thing whether there are
clients connected or not. In case of file-based RDBMS such as JET/ACE,
it's the application (e.g. Access) that open & manage files. JET/ACE
runs in-process with Access so it goes out of scope when Access quits.
This is not the case with server-based RDBMS where daemon will continue
to live on its own with or without any client connections.

thus the question of "where is the file" is totally nonissue

It's a total non issue to the end-user.

However, it is not a non-issue for a person setting up a system,

Unless the end-user is also administering the logical location of
files and the physical architecture of the file-server.
Which is what the Personal Computer revolution was all about,
so you may be familar with that kind of behaviour.

However, the OP said he got stuck at the prompt to choose a SQL Server
and this was what I was replying to. I think I've already qualified the
few special cases where we do have to be concerned with physical
location, but when OP was trying to upsize it, he was working within a
client context and this is the context I explained.

There are two points the

1) It's just services communicating with services. It all works
that way regardless of what kind of database you are using.

I'm not sure about that. If Access were the front-end client to the SQL
Server, that's a application communicating to service... e.g. client to
server. This is not to say that servers can't be also a client, but I
suspect the scenario where it's a service to another service is the
minority here.

2) Databases map to URI's because someone set them up
that way. It is all mapped: none of it is physical.

This is the case with server-based RDBMS, but this is not the case with
file-based RDBMS. As I said, a connection string for an ACE database
requires a filepath. You won't see a filepath in a connection string for
a server-based RDBMS.

Many people are familier with the simplest way a file mapping
may be set up using SERVER. Far fewer people are familer
with even the simplest way to setup a file mapping using SQL
SERVER.
Actually, it's no more difficult, and no less flexible, it's just
unfamilier.

To be honest, you've totally lost me here. I already said that I don't
think "SERVER" (presumably referring to Server service??) is germane to
the discussion - it's an entirely different service from what SQL Server
service does, and as I replied to David Fenton, SQL Server may or may
not depend on Server service on whether we require Named Pipes
connection or not.

And I'm not seeing how file mapping fits in here. Maybe you're referring
to setting up a file server?? If so, that doesn't really have anything
to do with SQL Server, which we still don't deal with it in terms of
filepaths.

Yes, as I explaned, the server does all the file handling, and presents
it through the SERVER service and a client at the workstation. At the
workstation, you ONLY deal with the CLIENT service.

What client service? If Access is the front-end with linked tables to
the SQL Server, it's a client to SQL Server service, but it's not a
service itself. Web browsers are client to IIS and Apache but it's not
service themselves. As I asserted earlier, there is nothing preventing a
server from being a client itself but this is less common.

Therefore a proper setup will _deny_ the users access to the files. All

Right, this is a difference between the two services.

communication should go through the daemon.

Yes, communications always goes through Windows Services.

So you never put .mdf and .ldf files in a share folder

Conversely, if you want to use the Access/Jet, you never
put the MDB in a Master database.

As I said earlier, you earlier replied that 'they' (which is not all
that clear and could refer to .mdf/.ldf files) could be put in a share
folder, and that was completely wrong way to do this. Again, maybe that
was not what you intended, but if I could have had read it as referring
to .mdf/.ldf files, then it's possible that others would have had
likewise and thus I replied with a clarification.

I'm not following the phrase "never put the MDB in a master database."
Master database is a SQL Server construct and I don't know why anyone
would want to put .mdb file in there (presumably as a BLOB) and I don't
see how this is a converse of my assertion that .mdf and .ldf don't
belong on a share folder.

Databases aren't bits of sand, or bricks. You use a mapping
syntax which depends on the ODBC driver, and you map to
names which may, or may not, mean something to the end user.

For example, it is conventional to use a similar name for the
logical name of a SQL Server database, and for the logical
name of the MDF file in the NTFS file record.

But the point here is that you never need to know the .mdf's path in
order to connect to the database residing there, whereas with .mdb, you
must know the path to use data within it. That's the whole point,
clearly contrasting a file-based RDBMS from a server-based RDBMS.

But the most important thing you got wrong is this:

working with a serverless source (e.g. a .mdb

Look at the second word of this phrase:

File Server

There is a basic misunderstanding that constantly confuses people.
Mostly, they get confused because they don't realise that a file
server is a server, and that messes up their understanding of how
Access/Jet works. That is not relevant here, so I won't say anymore
about it.

If you're suggesting that JET/ACE is a file server (and thus not
serverless), then I've seen many places making that claim but I do not
think this is entirely accurate. Here's the reason why:

What is a server? A residual program that listens to the specificed
communication protocol and responds to any requests.

In order for the program to be "residual", it has to be started up at
the machine's startup and active the whole time. After all, it couldn't
very well listen if it wasn't alive to begin with, no? This is why we
use daemons or services - they're one that's doing the listening.

For JET/ACE (as well SQLite and SQL Server Compact Edition), they are
not listed as a service. They are just a bunch of .DLLs that may be
called by an application (e.g. Access for ACE for example), and when
called by this application, comes into the memory but it will go out of
the scope when Access quits. ACE uses filesystem to locate the data file
and work on it in much like manner as Word would upon its .doc document
and Excel upon its .xls document.

In case of split databases, when two users connect to same backend, what
it means is there are two ACE engines running on each client. When they
want to get some pages they use cooperative locking, so they "play nice"
with each other when they share the file. There is no central
administration or gatekeeper as is the case with SQL Server service
making decisions who get what access to what data. If there are ten
users, there are 10 ACE engines, all cooperatively sharing a single
file. With SQL Sever, there's only one daemon whether there's zero
client, one client, or nth client.

The reason why ACE has been called a "file server" was because it works
with files and uses pages within it to read the data and write data to
it, but this is not accurate - an actual file server merely means it
provides a set of folders that can be shared among clients. ACE
certainly may require a service of file server, but it's not a file
server itself.

The SERVER service, on the other hand, enables clients to connect
to this machine and issue requests for data. Using the SMB protocol

If you want to learn more about the SMB protocol, which includes
the Record requests, and the Record Locking requests necessary
to support the native Windows database primitives, you will have
to read more than just the 'description' of the SERVER service.

I wonder what you mean by "native Windows database primitives"? I think
I already acknowledged that this is necessary to share files, prints and
named pipes. Also, I'm not sure whether "Record requests" and "Record
Locking requests" are in reference to JET/ACE's constructs or perhaps
SMB has its own construct? I'd have had thought that a file server
dispense a file, while JET/ACE would handle the interior structure, but
I don't know SMB as much as I would. Maybe I'm missing your point here
and would like to hear it.

In fact, if you turn of the the SQL SERVER service, you can
still connect to the SERVER service, and reach your Access
database remotely (or any database which uses the native
Windows database services)

Your reply was very confusing to me that I had to go back and read, and
now I realize you were talking about Server service as essential for
file sharing for _Access_. The whole time I thought you were talking
about it in connection to SQL Server service and that was why I failed
to see how it was relevant to the discussion previously. My apologies.

Except when you are setting up your servers or trying to
understand what a 'server' or trying to understand
what a 'service' is

And you've come in here and told us the answer to some completely
different question. You've told us that with the SQL Server, (as with
Access) the end user doesn't need to know where the file is physically.

And then you've told us that the original questions were 'wrong'.

But you've been so quick, you missed an important point: the OP
is not an end-user. He still has to work out where to put the files.

Well, I answered this way because OP said he was stuck on the prompt to
choose a SQL Server. In this context he was the client so I did believe
that by stepping back and explain why this was being asked of him it
would help him better understand the overall process, including the
physical location of .mdf and .ldf.

You have implicitly equated "A well-written Access application"
with "written ... specifically for server-client architecture".

Perhaps so, but my intention behind this is indicate that how much work
will depend on how well-written it was to start with.

You know, telling people that you think their questions are
wrong, and suggesting that a clearly described application
architecture is not well written, is mildly insulting at the best
of times, but I think that if you manage to read down this far
you've done your penance.

I have no intend to insult others. I have no specific knowledge of OP's
application, which is why I wanted to clarify that this is a function
and leave it up to OP to hopefully work out a realistic assessment of
how much work he will need to invest into achieving his end goals. As
for questions being wrong, I perhaps have had went on a limb out there
and made guesses at what OP was thinking about, but the intention was to
help reorient the OP with how server works.

Thanks David, they sound like convincing enough arguments to me. I have spent
way too many hours on this Access database to go back and rewrite anything.

Can you clear something up for me though. Is Windows Terminal Services
exactly the same thing as Remote Desktop? Or is it a platform that allows
multiple simultaneous Remote Desktop connections or something?

The reason I ask is, I regularly use Remote Desktop to access/connect to my
workstation at the office from my notebook at home and I have to say, I find
it great for some things but not so great for others. The concept is
brilliant but I don't like the way the interface looks through this
connection or how it reacts to commands. Everything looks pixelated and runs
'jumpy' and not very responsively.

So with WTS would the staff be just remotely opening the desktops of their
computers on our office network and then opening the Access database from
there? Or is it something closer to a copy of the Front end being on their
remote computers and the back end being on our office network and the two
talk to eachother via WTS?

One other thing, the firewall settings on our 2701HGV-W Gateway modem only
allows one instance of Remote Desktop to be used at a time between all the
computers on the network (although this is a relatively small issue in the
scheme of things).


"David W. Fenton" wrote:

=?Utf-8?B?Sm9uMjI=?= wrote in
:

I think I am going to persevere with trying to get my
head around SQL server.

I think this is a mistake. Writing an Access app to run on SQL
Server across the Internet means you have to really understand how
to design and maintain server-side components (view and stored
procedures) and then you have to carefully design your app to use
those server-side objects, and you may have to jump through certain
hoops (such as using ADO to get editable results from sprocs) that
are not compatible with default Access behaviors/designs.

Also, you'd have to set up a VPN (assuming you're not silly enough
to just expose your SQL Server port directly to the wide-open
Internet). If you've got a server to run SQL Server, you've got a
server that can run Windows Terminal Server. Since you'd have the
VPN anyway, the only step to use WTS would be to acquire the CALs to
allow users to connect to run their Remote Desktop Sessions, and
then the basic setup of the terminal server for your application
(giving each user an individual copy of the front end, automating
updates, etc.).

And WTS requires NO ALTERATIONS to your application, and no learning
curve. Going with SQL Server to support remote access has a very
high learning curve, and would require substantial alteration to
most Access apps designed to run with a Jet/ACE back end (or even
most designed to use SQL Server on a local LAN).

There really is no comparison in regard to the cost and difficulty
level.

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

Banana,

Access/Jet is built on top of the Windows Database primatives.

There is no indexing, or transactions, in the database
services that JET uses. Transactions in particular have
recently been added to Windows, and are not used by JET.

Jet builds it's own locking and Workgroup security on
top of the Windows Services. Workgroup security,
because Jet pre-dates Windows security. Locking,
because without Windows Security, Jet needed to build
it's own lock database to be able to report who had
a record locked.

So the Jet/MDB locking is done by locking records in
the lock file, rather than by locking records in the data
file. The locking of records in the lock file is done by
using Windows Record locking.

Windows has a mechanisim for reading/writing/locking
records. Jet uses this to get what are sometimes called
'physical records' from the database. On old IDE drives,
physical records are disc sectors, not even disk clusters,
and certainly not what is returned by a record request,
so that nomenclenture is not correct, but if the OS was
integrated with the File System and the Disk Controller,
as they were in old OS's, then the OS database primatives
would return 'physical records'.

ACE uses Windows security instead of Jet Workgroup
security, which is good, because Jet Workgroup security
is very old, not properly implemented, and has never been
updated.

But ACE as written can't do table level security. ACE
needs Windows record-level security to implement ACE
table-level security. Windows security doesn't have record-
-level security, and Windows table-level security was
based on a different model. So ACE will get table-level
security if ACE is completely re-written, or if Windows
adds record-level security.

(david)

Replication is out of the question. Too much of the data relies on unique
values that if doubled up would cause massive headaches at the time of
consolidating it all.

"Tony Toews [MVP]" wrote:

Jon22 wrote:

Can anyone suggest a simpler way of achieving remote access to my database?

Now one of the things I didn't see mentioned in the discussion is will the folks who
access your data remotely from overseas always be on a reasonably reliable Internet
connection? If so then a VPN to SQL Server, Terminal Server or Sharepoint would work
well.

If not then you'd have to look at replication, either Jet or SQL Server or possibly
SharePoint has enough capabilty that you could go away from the home network for a
week or two. Of course if replication is required then backups need to be done while
on the road in case the laptop is lost/stolen/etc.

And likely you want the laptop hard drive to be encrypted such as using Windows
Vista/7 Ultimate Bitllocker.

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/
Granite Fleet Manager http://www.granitefleet.com/
.