Might be outgrowing Access but daunted by SQL Server

Armen Stein wrote in
:

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.

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Banana Banana@Republic wrote in news:4B8938C6.5090606@Republic:

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

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

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

"Tony Toews [MVP]" wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Tony Toews [MVP]" wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

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

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"david" wrote in
:

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.

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

"David W. Fenton" wrote:

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/archiv...scription.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/

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?


David W. Fenton" wrote:

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

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

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

"David W. Fenton" wrote:

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

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Jon22 wrote:
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.