Might be outgrowing Access but daunted by SQL Server

[David W. Fenton]

That is a correct assumption. All of the later editions of the so
called server editions of windows, such as windows server 2003, or
windows server 2008 all allow windows terminal services to be
installed and setup and run as a service. In fact, as far as I
know, windows terminal services is already preinstalled on later
server editions of windows servers. In the past you had to install
and set up this WTS software.

This is not really correct. There was never a version of Windows
Terminal Server where you could add it on. In NT 4, it was a
separate version of Windows, and you had to re-install it. Starting
with Windows 2000 Server, it was included in the OS itself, with two
administrator-level logons pre-authorized. To have more than two
simultaneous users (and users not running as administrators, which
would be unwise for regular users!), you have to purchase
Client-Access licenses, which are about $40 each last I checked.

That doesn't provide the licenses to run Office apps, just the
permission to connect to the Terminal Server and run apps for which
you have the licenses. If you have Office 2000 to 2003, you can
usually run any Office 2000-2003 app on Terminal Server. Starting
with Office 2007, this is more strict -- you have to have the
particular app installed and it has to be version 2007 (though I'd
expect it to be downard-compatible, i.e., if you have Access 2007,
you can run Access 2003 on the Terminal Server). Also, certain
versions of Windows Server don't allow anything other than the two
administrative Terminal Server sessions. This would include all
versions of the Small Business Edition of Windows Server, and
running Office 2007 (and I'd presume 2010 when it comes out) on the
Terminal Server, you have to be running Enterprise Server.

As an alternative if you really only need to support a couple of
remote users and don't have a Windows Server (and can't justify the
expsenss) you might look into Winconnect (google it), which allows
you to use a regular desktop PC as your server (though you'd want
that to be a dedicated machine). I don't know how close the
performance is to Terminal Services running on a Windows Server, but
I've heard from reliable sources that it's pretty good. If a client
of mine needed a couple of remote users and had no server, that's
exactly where I'd start.

 

[David W. Fenton]

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 didn't know about the linked server thing. I do know that SQL
Server 2000 supported heterogeneous replication with Jet 4, which
allowed a Jet 4 user to be a subscriber to a replicated SQL Server
database. They dropped that with SQL Server 2005, but I wonder if
the linked server solution would enable it (so that you'd be running
SQL Server on the remote laptop only in order to do the
synchronization of the MDB linked "server").

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

Sharepoint is the future for that, both because it's easier and
because it's the platform MS is putting its resources into to
support disconnected editing.

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.

FWIW, when I'm responsible, I include the MDF/LDF files in the file
backup in addition to running the SQL Server agent to do its thing.
Multiple levels of redundancy are a requirement when it comes to
backups, because the danger is that multiple backups will fail
simultaneously. I've seen it happen so often that I just don't set
up a single level of backup for anyone.

 

[David W. Fenton]

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.

But a VPN is a safe solution and more versatile:

1. you can offer access to multiple server/applications with a
single port, instead of opening a port for each server/application.

2. VPNs usually require more than just a username/password
authentication, i.e., a binary security key that is used for
authorization and encryption of the connection.

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.

I don't know much about Azure. If it's doing little more than an
open SQL Server port, I'd avoid it.

Any service that can be provided via a port open to the Internet is
also going to be capable of being provided inside the LAN once
you've connected to the VPN, so I really think there's no excuse
whatsoever for opening ports like that, ever.

But I'm something of a stickler when it comes to security, much
moreso than the folks I run into in the Access newsgroups, and much
moreso than your garden-variety Windows sysadmin (most of whom
effectively know nothing at all about actually securing a Windows
network).

 

[David W. Fenton]

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 guess so!

Try turning off the Remote Procedure Call service and see what
happens!

 

[David W. Fenton]

be advised that Aaron Kempf is rather
monomaniacal on the topic of ADPs and SQL Server.

....and 99.9% of the time, completely wrong in regard to his
assertions about ADPs and SQL Server. That is, he doesn't even know
anything useful about the products he's promoting.

 

[David W. Fenton]

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

A caution on encryption:

Friday night I spilled a glass of red wine on my laptop -- didn't
think anything about it, as it seemed that it had not gone anywhere
that could leak inside -- but Saturday morning the laptop wouldn't
boot. So, I pulled the drive and used a USB SATA/IDE adaptor
(everyone should have one of these, only about $30) to connect to it
from another WinXP machine. When I encrypted my files, I'd exported
the appropriate certificates to my thumb drive, so I imported those
to the WinXP machine to get access to the encrypted files.

But it didn't work.

So, even though I did everything right (most people who lose
encrypted files neglect to export the encryption certificate
necessary to decrypt the files from a different computer/logon), it
didn't work.

Fortunately, today the red wine had dried out and my laptop booted
and seems to be working fine (though the fan seems to not be running
nearly as often as it used to -- I worry about this so I'm preparing
in the background for a significant failure just in case). The first
thing I did is start decrypting all my files, since I no longer
believe it's a reliable thing to do. Sure, it protects the files on
a portable machine, but if I can't be guaranteed that I can import
the encryption certificate onto another machine and regain access to
my files, it's just way too risky.

So, after the decryption is complete, I'll be making several backups
to different media.

And I won't be using NTFS file encryption again.

Sadly.

 

[David W. Fenton]

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.

Er, that's a solved problem. It has to be, since replication is so
widespread.

It would require changing your database to account for it, but it's
completely solvable.

It will be an issue not matter what solution you choose if you're
providing disconnected editing of the data.

 

[David W. Fenton]

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

The latter is something an end user can and will do as a matter of
course.

The former is something only DBAs and system administrators will
ever need or want to do.

Your original statement was wrong. Just admit it and move on.

And please stop changing your email address, as you've popped out of
my killfile again and I don't want to read your replies. Your
contribution to this thread shows that you generally don't offer
anything but confusion.

 

[Tony Toews [MVP]]

I don't know much about Azure.

Be advised that you are charged by the compute hours which is really the up time
hours. Or soemthing. Apparently the docs aren't the clearest.

http://weblogs.asp.net/kencox/archi...re-usage-charges-on-an-msdn-subscription.aspx
and read the comments.

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/

 

[Jon22]

Well thats troubling. A number of the areas of my database (quotes, jobs and
designs to start with) work on an identification system that gives each
record (that is to say each Quote or each Job) a unique "Quote Number" or
"Job Number" etc. Now before you jump down my throat, I know that this is a
basic principal of Access databases and that it already has a built in unique
identifying system of it's own (Autonumber). But for various reasons, we have
elected to use our own 'code' to uniquely identify our records. The basic
breakdown of our code system is: Single letter representing the type of entry
(eg "Q" for a quote) followed by the curent year in "yy" format (eg "10" for
current entries) then a dash ("-") then a three digit number representing the
individual record for that entry type and year. So for example, "J09-154"
would be the identifying value for the 154th Job that we did in the year
2009. This record would be located in my table named "Jobs" and the value of
the [JobNumber] field in this record would be "J09-154".

So on my form named "JobEntry", the expression in the 'Default' property of
my [JobNumber] field for new records would look like this = "J" &
Format(Date(),"yy") & "-" &
Format((Mid(DMax("[JobNumber]","[Jobs]"),5,3)+1),"000").

Now, if I have two users who were logged on to a TS RemoteApp session for
instance (or whatever other Terminal Services method we'd use) both with this
Access Database open and they both started a new Quote at nearly the same
time (say within 2 minutes of eachother), given that the Quote form might
take 5 minutes to fill out and print to pdf, what you're saying is that my
two users could potentially create a quote each and send their quotes off to
their prospective customers and had both used the same Quote Number that was
automatically generated for them when they went to a new record on the Quotes
form?

 

[Jon22]

Further to my reply above:

(I do know about Primary Keys too. Forgot to mention that in that last post.
However I don't know how they function when the database is in a shared
state/environment.)

 

[Banana]

Well thats troubling. A number of the areas of my database (quotes, jobs and
designs to start with) work on an identification system that gives each
record (that is to say each Quote or each Job) a unique "Quote Number" or
"Job Number" etc. Now before you jump down my throat, I know that this is a
basic principal of Access databases and that it already has a built in unique
identifying system of it's own (Autonumber). But for various reasons, we have
elected to use our own 'code' to uniquely identify our records.

Well, you're certainly not the first nor will you be the last person to
use sequential numbering in lieu of autonumber (for other readers,
autonumbers may seems to be sequential but there's no guarantee it will
be that way).

While replication may be implemented differently depending on what you
use for replicating (Jet Replication? SQL Server Replication?
Sharepoint?* Other?), you may be required to add fields necessary for
the replication to track changes made to this record. But that doesn't
mean you can't continue to use the code as you have right now. Even if
the replication changes the primary key, defining your code as
non-nullable and unique (in Access parlance, that would be
'Required=Yes' and 'Indexed=Yes(No Duplicates)') will have same effect
as it had when it was the primary key. So, what it really means you will
need to add more fields. There may be other changes required that I'm
not aware of but I doubt that would require you to drop the code you've
had in the place entirely.

But what is more important here is that whether you are replicating or
not is that DMax()+1 may be a problem in multi-user settings because as
you described, it's possible for users to obtain same ID by accident.
This will occurs regardless of whether you have replication in place or
not. One solution I've seen others use (note that I never had the need
to use a sequential key so this is all secondhand) is to create a table
with only numbers. By using DAO recordset with dbDenyRead set, you can
guarantee that when users need to save record (and thus obtain a job
code) the request will be serialized by opening recordset upon on the
"number generator" table with dbDenyRead and getting a number and adding
it to the record just before it's actually saved. Note this is a
solution suitable for where tables are in Access backend, but similar
principle can be applied to different backends in SQL Server, namely
that you want to always serialize the request for the next number and do
so just right before the record is saved (minimizing the chances that
users may not save the record and dropping that number and thus
introduce 'gaps' in the sequence)

HTH.



*To be honest, I'm not 100% clear on whether Sharepoint actually offer
replication, and whether so in same sense as what is meant by Jet
Replication/SQL Server Replication, so I may be mistaken for listing
Sharepoint as one of replication candidates.

 

[James A. Fortune]

But what exactly is a 'Windows Database primitives'? I've googled this
phrase and didn't find anything useful. I'd like to know what you mean
by that phrase.

You clearly and carefully presented your case that the DLL's used for
data access in Access are file based. I agree. david, in mentioning
native windows database primatives, seems to suggest that because the
files themselves are served (via SMB), there is some similarity to how
the information contained in a SQL Server file is served. It's an
interesting concept, to be sure, but if I haven't misunderstood
david's point, it doesn't really serve to limn the difference
between the two data access methods. The DLL's used by Access are
still file-based, as we understand the term. The SQL Server service
is still Client/Server, as we understand the term.

James A. Fortune
(e-mail address removed)

 

[David W. Fenton]

http://weblogs.asp.net/kencox/archive/2010/02/25/i-owe-282-06-for-w
indows-azure-usage-charges-on-an-msdn-subscription.aspx and read
the comments.

No, thanks. I'll wait for the hype and then for somebody to cut
through it and explain what it's all about. If nobody can do that
clearly, I'll ignore it, the same way I've ignored any number of
other buzzword-driven "innovations."

 

[David W. Fenton]

Now, if I have two users who were logged on to a TS RemoteApp
session for instance (or whatever other Terminal Services method
we'd use) both with this Access Database open and they both
started a new Quote at nearly the same time (say within 2 minutes
of eachother), given that the Quote form might take 5 minutes to
fill out and print to pdf, what you're saying is that my two users
could potentially create a quote each and send their quotes off to
their prospective customers and had both used the same Quote
Number that was automatically generated for them when they went to
a new record on the Quotes form?

No, not at all. Using a Terminal Server is a great way to *avoid*
the problem. What I said was that if users are disconnected, you'd
have to use some other method of creating a unique sequence, either
pre-allocating blocks of numbers to particular workstations/people,
or incorporating the source workstation/person into the sequence
(either within a single field, or, better, in a pair of fields with
a unique index).

Any multi-user app that generates its own sequence numbers should
already be avoiding the problem you describe by choosing the next
number in the sequence and saving it immediately so that the next
user doesn't collide. As long as all users are adding records to a
shared back end, your only concern is locking the process while
you're generating the sequence number. The usual way to do this is
to have a single-record table where you update it to the last-used
sequence number. This is more reliable and more efficient than
locking the table you're storing the sequence number in.

 

[David W. Fenton]

A number of the areas of my database (quotes, jobs and
designs to start with) work on an identification system that gives
each record (that is to say each Quote or each Job) a unique
"Quote Number" or "Job Number" etc. Now before you jump down my
throat, I know that this is a basic principal of Access databases
and that it already has a built in unique identifying system of
it's own (Autonumber). But for various reasons, we have elected to
use our own 'code' to uniquely identify our records.

Dunno why you're getting defensive here -- any sequence that is
being exposed to the users *should* be generated as you're doing. An
Autonumber should never be exposed to the users, regardless of
what's going on behind the scenes, so from what I can tell, you're
certainly doing things correctly already.

 

[David W. Fenton]

While replication may be implemented differently depending on what
you use for replicating (Jet Replication? SQL Server Replication?
Sharepoint?* Other?), you may be required to add fields necessary
for the replication to track changes made to this record. But that
doesn't mean you can't continue to use the code as you have right
now.

SQL Server allows the allocation of identify value ranges to
particular instances of the replicated database. This allows you to
control what numbers get assigned at the database engine level,
which is quite useful. On the other hand, it's an identity key, and
perhaps subject to the same issues as Autonumbers (I'm not sure how
SQL Server's identity data type works, to be honest).

 

[David W. Fenton]

(I do know about Primary Keys too. Forgot to mention that in that
last post. However I don't know how they function when the
database is in a shared state/environment.)

I assumed you've already addressed the shared environment, and it's
the disconnected environment that would be problematic.

 

[Tony Toews [MVP]]

No, thanks. I'll wait for the hype and then for somebody to cut
through it and explain what it's all about. If nobody can do that
clearly, I'll ignore it, the same way I've ignored any number of
other buzzword-driven "innovations."

Actually that link and the comments cut through the hype and warn you about the
perils of how expensive it oculd get just for test data.

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/

 

[CW]

Jon -
I am no IT guru either but am in a very similar situation to you and have a
completely free / no learning curve suggestion for you.
It will probably have all the experts throwing their hands up in horror as
it is simplicity in itself and no doubt all kinds of holes can be picked in
it. However, the bottom line is that it works for us, and works very very
well indeed. Let me say sincerely that I have the utmost respect for all the
amazing depth and breadth of advice offered to you in this post and I have
benefited tremendously from replies given to my many queries over the last
few years, but sometimes one simply wants - well, simplicity. So, back to
basics:
We have a total of 6 users on an Access 2003 mdb that I have gradually put
together over the last 4 years. It runs on a fairly basic HP box (server)
that also runs Exchange and stores everybody's other workfiles. The mdb now
handles every aspect of the company's business and the owner is thrilled with
the control and instant information that it gives him.
It is NOT split - I didn't do that at the outset and despite thinking about
it a number of times, have never got round to it. Despite this we never
suffer from conflicts or record locking. If it ain't bust, don't fix it.
We have 3 users who regularly work remotely, from home, and 2 who travel
frequently around the world and access the system every day whilst away. We
use nothing more than LogMeIn, which in its basic form is totally free, and
even for the Pro version is a mere handful of dollars. The response time is
perfectly acceptable and the users love it.
The whole setup works wonderfully for us and I often wonder why one might
need anything more sophisticated (= expensive and time-consuming to learn and
maintain).
I rest my case.
CW