DataBase - Might be outgrowing Access but daunted by SQL Server

Asked By Jon22

25-Feb-10 11:42 AM

I am close to completing the consolidation of various small Access databases
and a couple of Excel spreadsheets that my little company uses (5 staff) into
an all encompasing Access database and I was planning on splitting the
database when I was finished to allow simaltaneous use of it by staff on our
small office network. However it is becoming more and more obvious to me that
I am going to need to give staff access to this new database from remote
locations (outside of our office network). Two staff members are regularly
overseas, one is mainly on the road with her laptop etc etc. I have been
playing around with trying to link tables from remote locations (ftp path of
the database on our Network Storage Device at the office) and needless to say
- that do not work. So I have spent the last 10 hours downloading, installing
and trying to get my head around Microsoft SQL Server 2008 Express and how it
might help me out. It all started when I came across a very encouraging
article on keeping your Access database as your front end application and
linking to SQL Server database tables. I am finding it MUCH more complicated
than I had hoped.

Can anyone suggest a simpler way of achieving remote access to my database?
I have at my disposal a Network Storage Device which has remote access
capabilities (this is where the database is currently stored), our website
which it could be stored on, or I'd be happy to use my quite powerful
workstation at the office as some kind of server. You may have guessed by now
that I am no IT guru by any stretch so please bare this in mind with any
suggestions.

25-Feb-10 07:54 PM

Wow, what a brilliant article! That was exactly what I needed to read right
now, thank you Albert. Sounds like SharePoint is the way to go. I will be
looking into that next.

If I chose to run SharePoint from our office rather than use Office Live, do
you think it will be easier than trying to set up a Microsoft SQL Server
database with Microsoft SQL Server 2008 Express for someone like me with no
knowledge of such things? (By the way... I am the companies IT department -
heaven help us)

25-Feb-10 09:00 PM

Thanks Stefan. I installed SQL Server 2008 Express on my laptop at home last
night just to have a fiddle with it and try to get familiar with it but as I
said, it confused me no end. After I had installed it I tried to run the
Upsizing Wizard on a dummie copy of our database but after selecting "New
Database" on the first step, I got stuck at "What SQL Server would you like
to use for this database?" on the next step.

I am struggling with the fundamentals of SQL Server databases. Is it a single
file like an .mdb file? Where does it get reside? Is it creating an SQL
Server on the machine which is a different thing to an SQL server database?
Which machine/s should I be installing the program on? We do not have a
'Server' persay just a number of desktop machines and a network storage
device so are we under equipped? What IS a server anyway? Can one of our more
powerful desktop workstations act as our 'server'?

I need to be able to run all this myself as we are too small to have regular
outside IT costs, but large enough to require a WAN Access Database solution.
I might be wrong but I think it'd also be better to have the data (tables)
stored locally (office network) but accessable remotely as most of the usage
is at the office and I'd prefer not to sacrifice too much speed for the
ability to use the database remotely. Can I have my cake and eat it too using
SQL Server? Would the SharePoint option make using the database slower at the
office?

Also the database relies heavily on unique key values in the tables such as
Quote Numbers, Purchase Order numbers, Job Numbers, Invoice Numbers etc. Many
of which are automatically generated in the forms via complex functions in
the control's default values which in turn rely on up-to-date and constantly
refreshed info from the tables so as to avoid duplicating these values. Is
SQL Server the solution for this? Is there isuch a thing as a back end
database that updates instantly with every single change or addition made to
it that is instantly reflected in multiple user's front end db all over the
world?

I had a bit of a trial run a few months back at splitting this database and
playing around with the sharing options and could not for the life of me get
the thing to avoid duplicating key fields when opened simaltaneously. This
worries me greatly as the whole thing relies on the integrity of these fields.

25-Feb-10 10:37 PM

Good questions!

Let's go back to Access just for a moment. When you create an Access
database, you are directly managing a file, .mdb file. You manipulate it
via Access UI, adding data (and objects, too) to it and perhaps even
update or delete it as well. In this context, this is no different from
what you have been doing with your Word document and Excel spreadsheet.
You're working with a file.

Now if we go to the SQL Server, you are no longer working with a file,
per se. Rather, you "connect" to a server and thus communicate with a
daemon (e.g. the program that resides in memory persistently and listens
& reacts to requests from other programs). When you request a piece of
data, it is the daemon that opens the data file, read the piece and send
it back to you. Now, you examine this piece and decide you want to
update. You then give the daemon the request to update, but it is the
daemon that actually does updating into the data file. In all cases, you
never ever touch the file directly. You work through the daemon and the
daemon does all file handling.

So, for most RDBMS out there, we interact with the daemons while in
world of JET/ACE, or SQLite or SQL Server Compact Edition, there are no
daemons and we manage the files directly. Once you understand that
concept, it should help become clear why you connect to a "server"
rather than open a file. You never ever touch those file directly,
contrary to what you did in Access.

The server can be anything, a dusty old 386 PC to whatever's the latest
question, though it is common to market "server hardware", but in fact,
you need to have a place for the daemon to resides. The daemon very well
could not exist in a void, can it? So whenever SQL Server happens to be
installed, that is where the daemon is and thus that host is the
clients. In fact, Access is capable of being a client because you can
create a linked table or use ADO connection to fetch data from a server.

Now, generally speaking, it is usually expected that the server would
run on more powerful hardware because obviously there is more demands
placed on the server (and hence the market for "server hardware"). But
it does not mean that you have to go out and buy yourself a server farm
just to run SQL Server. As long the hardware where you install SQL
Server are adequate for your actual demands, it is fine. So if it was
lightly used, I bet that even a old computer could run SQL Server just
fine. Of course, it would be wise to plan ahead for new hardware should
you predict the use to grow beyond the current hardware's capacity.

Now, I did not answer your question about files... That was deliberate as
I wanted to emphasize that with SQL Server (and any other server-based
RDBMS) you do not really deal with files. You deal with the daemon and
the daemon manages the files. But yes, there are files, and in SQL
Server, it is .mdf and .ldf which may be stored in a certain folder,
depending on how you installed SQL Server. But the only time you
actually worry about the file is when you are dealing with backup &
restore tasks and even then that is not strictly necessary.


Hopefully the above explanation should make clear that because you
interact with daemons, not files, you need to tell the client to how to
find the daemon... hence the prompt "What SQL Server would you like to
use for this database"... it is possible that you may have two SQL Server
running in your organization... which one do you want to use? Normally,
you input in the host address. For your first time fiddling, you would
put in "localhost" because I am assuming you have installed SQL Server on
the same computer where you wanted to upsize the Access database so by
telling "localhost", it will contact the daemon residing on your computer.

BTW, I have never used Upsized Wizard since that one time when the results
was less than satisfactory... I have heard results that SQL Server
Migration Assistant provides much better experience. You may want to
give this a go.

I hope this helps.

26-Feb-10 12:39 AM

I do not want to be misleading, and I do not think SharePoint is really the
way to go in many instances, is just one of the many possibilities that now
Access offers.

I think this is what so great about Access, is that we have so many great
possibilities. I think prior to the Access 2010 offering, I do not recommend
SharePoint in a lot of situations.  In other words you have some limitations
in terms of application design that are quite notable prior to Access 2010.
In fact even with Access 2010, there are limitations that if not taking into
account, you would  still be better off to use SQL server.


There is no question that SharePoint 2010 + Access 2010 is far easier then
getting up to speed with than that of SQL server.  The reason for this is
that you get to stay 100% inside of the Access environment, and do not have
to use (or learn) some type of server or SQL server setup stuff.  In fact I
think this is why SharePoint is such a winner, you have so many the so
called users that do not wanna spend the time to learn some server based
technology, and SharePoint is the ticket in this regards.

On the other hand, SQL server is not really that hard. If you think about
this, sql server is really just the table design part much like Access.  If
you know how to use the relationships and table design in Access, then SQL
server should go pretty fast for you to learn. Your application design for
the most part continues inside ms access.  I would say learning to build
tables and use SQL server has a considerably shorter and less learning curve
than that of say sitting down and learning to do things with ms access.  In
other words ms access as a considerably steeper learning curve then learning
to do the basic things you need in SQL server.

As mentioned, if you are not running SharePoint 2010, and it can be pretty
much assured that you are not as of yet since the product is not shipping and
not available, then right now your decisions comes down to that of SQL
server, or that of terminal services (or what is often referred to as remote
desktop).  SharePoint 2007 with access 2007 is a possibility, but I cannot
say it is anything close to what we are having or going to have in Access 2010
(and stating this without regards to the new ability in Access 2010 in which
you can actually build 100% web based applications).

Keep in mind, one of the other really big advantages of SQL server is the
low cost. If you can get your system up and running with SQL server using
one of several free editions, then you not have to purchase anything else or
likely even have to set up much if any more infrastructure then what you
have now except for some type of secure connection.

If you have an IT department at your disposal, then I often suggest windows
terminal services. This means once the infrastructure is setup, then you can
deploy and use your application as it is without any modifications, nor ANY
training or learning on your part is required for this deployment option.
So, the remote desktop option is a great solution because you do not have to
learn anything new - however this is likely you are more expensive option
from an infrastructure and IT point of view.


Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

26-Feb-10 02:15 AM

Thanks Banana! Definately cleared away some of the fog. I just finished
writing a big long reply message asking about remote linking only to lose it
just as I was posting it, so I am going to rewrite it as briefly as I can:

I am having trouble finding info on linking to tables on a SQL Server
database from a computer outside the LAN.

Let me know if I am 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)

I think that I will 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?

From what I can gather, when I link to the SQL database tables I will 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?

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?

26-Feb-10 02:16 AM

Dunno, have not tried that one. I guess that includes Visual Studio
Express? Otherwise you will be stuck using SQLCMD to do even
the most basic tasks.

Another option would be to download the full version of SQL Server
to experiment on. Last I looked, the full version is FREE for you, a
DEVELOPER, to do DEVELOPMENT on. You just use Express
when your company starts using it for real data.

The advantage of the full version is that it comes with the complete
client tools and documentation, including SQL Server Enterprise
Manager, which is (was?) the primary administration tool for SQL
Server. Dunno how Visual Studio/SQL Server Express compares
with that now.


Yes, it is a couple of files, like Access requires MDB, LDB and MDW
files, and yes the data is in just one MDF file, like Access data is in just
one MDB file.


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.

For Access, creating new databases is done from Access, and setting
up network database shares in done from Windows Explorer: For SQL
Server, it is done from Enterprise Manger, or Visual Studio, or using
OSQL or SQLCMD


Creating a SQL Server on a machine means installing and starting
the SQL Server service. For Access, the "SERVER" service is
required (plus some other things). For SQL Server, a "SQL SERVER"
service is required (plus some other things). The only difference is
that the "SERVER" service is included and started by default on
Windows: the "SQL SERVER" service you had to download and
install.


Wherever you want it.


The main differences between a typical server and a typical
workstation are (1) a server is optimised for more people using it,
and (2) a server permits more people to connect to it.


SQL Server Express is optimisied for a small PC with a small
number of connections, so it fits just fine on a workstation or
on a heavily used server. And if you use a full version for development,
you will be the only person using it, so it also fits just fine anywhere.


You can never have your cake and eat it too. If you want it to
work well over the network, you have to keep learning and
keep working. it is not magic.


Haven't tried the new SharePoint. You do not want the old SharePoint.

26-Feb-10 02:21 AM

Thanks again Albert, I think I am going to persevere with trying to get my
head around SQL server. That way we can be fully independent and growth
ready. I might add that whatever solution I come up with needs to take into
account that I am our IT department and for budget reasons I do not yet want
to have to start relying on outside help (apart from this sort of help of
course). I am a pretty fast learner so we will see how we go.

26-Feb-10 08:06 AM

Visual Studio does include SSE, but SSE is also a standalone download.


I believe it actually costs $50 to have the developer version. Besides
it is not strictly necessary because the SQL Server Management Studio
(SSMS) is already available. In all versions it is bundled but I believe
for Express, you have to separately download the SSMS but it is free as
well.


It should be noted that the complete documentation is online at MSDN. If
you google "Transact-SQL <whatever>", you will find many good
documentation there.


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

Thus, it is important to understand that you do not "browse and open a
file" as you would with other applications such as documents or
spreadsheet, but rather "connect to" a server 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 because that is
the daemon's job to manage it and you communicate with the daemon.


No. As I explained, daemon does all file handling here. Therefore a
proper setup will _deny_ the users access to the files. All
communication should go through the daemon. So you never put .mdf and
.ldf files in a share folder but rather merely open the port 1433 for
TCP/IP or use Named Pipes or whatever for the clients to connect to.
This is why if you look at various ODBC connection strings, the only
time you do see a filepath is when you are working with a serverless
source (e.g. a .mdb will have its filepath in the ODBC connection, but
you will never see that for a Oracle, MySQL, DB/2, SQL Server!)

connectionstrings.com

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


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.

In fact, if I turn off "Server" service, I still can connect to SQL
Server on my SSMS, and reach a Sharepoint site remotely... IOW, "SQL
Server" service and "Sharepoint" service are not dependent on the
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.

As I explained in my previous reply to Jon22, the term "server" actually

27-Feb-10 10:19 AM

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 am not
sure how well SQL Server handles synchronizing with non-SQL Server
sources or whether it will be practical in this use, though.

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


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.

27-Feb-10 10:18 AM

Glad to hear that.


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 are done. We want to be
sure you have considered all possible solutions and what they means to you
in terms of performance, scalability and cost.


Well, yes, SQL Server has to be accessible from somewhere. Static IP is
a simple way to implement this though you will 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 is local, as David Fenton
explained.


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 would  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
do not 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


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

Beginning SQL Server by Leigh Purvis:
http://www.utteraccess.com/forum/index.php?showtopic=1732935

Beginner's Guide to ODBC by myself:
http://www.utteraccess.com/forum/index.php?showtopic=1843709


Best of luck!

27-Feb-10 10:22 AM

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?

27-Feb-10 12:08 PM

I do not know why, but there iseems to be snippets where it should have
been quoted but was not so I had to filter which was your words and which
was my words not correctly quoted.

Anyway...


However, you said in your previous reply:

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


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.


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


No. it is 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 is 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.


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


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


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 will not see a filepath in a connection string for
a server-based RDBMS.


To be honest, you have totally lost me here. I already said that I do not
think "SERVER" (presumably referring to Server service??) is germane to
the discussion - it is 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.

28-Feb-10 02:53 AM

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

28-Feb-10 03:16 AM

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 is 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 is 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 cannot do table level security. ACE
needs Windows record-level security to implement ACE
table-level security. Windows security does not 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)

28-Feb-10 03:32 AM

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.

28-Feb-10 05:18 AM

I have been looking up Windows Terminal Server and related subjects. We do not
have at this point in time a stand alone server computer. Is Terminal Server
a type of system software? Should I be considering buying a new computer to
act as our server which would run this software?

01-Mar-10 07:17 AM

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
basic principal of Access databases and that it already has a built in unique
identifying system of it is 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 are 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?

01-Mar-10 07:29 AM

Further to my reply above:

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

01-Mar-10 08:22 AM

Well, you are 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 is 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 does not
mean you cannot 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 am
not aware of but I doubt that would require you to drop the code you have
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 is 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 have 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
it to the record just before it is 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 am 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.

01-Mar-10 10:46 PM

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

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

01-Mar-10 10:51 PM

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 would
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 does not collide. As long as all users are adding records to a
shared back end, your only concern is locking the process while
you are 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 are storing the sequence number in.

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

01-Mar-10 10:53 PM

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

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

01-Mar-10 10:57 PM

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 is an identity key, and
perhaps subject to the same issues as Autonumbers (I am not sure how
SQL Server's identity data type works, to be honest).

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

01-Mar-10 11:02 PM

I assumed you have already addressed the shared environment, and it is
the disconnected environment that would be problematic.

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

02-Mar-10 12:06 AM

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/

01-Mar-10 07:16 PM

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 did not 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, do not 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

01-Mar-10 08:31 PM

I have been running ODBC-linked tables for servers on WANs since the days of
Access 2.0. That includes, but is not limited to, Microsoft SQL Server -- it
works with any ODBC-compliant server.


This claim is not true.  Mr. Kempf compounds his lack of knowledge on the
subject of Access with frequent false statements that he knows to be false.


ADPs are no longer recommended by the Access product group. Though ADPs are
still supported and have not been officially "deprecated", I'd certainly
advise you to seriously consider before launching a new product using one.
ADPs only support MS SQL Server,
so if you may ever have to support a different server DB, they are not
appropriate.

You can safely disregard Mr. Kempf's comments, even if no one has refuted
the specifics. His useful contributions to this newsgroup, over all the time
he is been posting here, have been minimal.

Larry Linson
Microsoft Office Access MVP

02-Mar-10 07:54 PM

The scenario you outline will not work in more than 1 out of 1000
cases. It is just wrong.

it is also much harder to update the application part of an unsplit
database, so it must be a pretty trivial app if that is working for
you.

There is really nothing hard about managing a split app and it is
much, much safer and more reliable. I have never once put an unsplit
app into production use with multiple users, and that includes the
very first one I ever did, back in 1996. I do not know exactly where
I got the information that I needed to split it, but that was the
way I designed the whole thing. This was back in the days of Access
2, Windows for Workgroups 3.1 and 10BaseT networking. I was blown
away by Access's ability to cache data (particular for a stupidly
designed combo box that had hundreds of records -- first time you
used it was slow, after that, totally fast).

It just does not seem like an issue to me.

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

02-Mar-10 08:08 PM

So far as I can recall, they are not >0.

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

02-Mar-10 10:01 PM

Thank you all very much for your advice, especially those who have taken the
time to follow this issue right through with me and who have outlined their
advice so comprehensively.

At this stage, for the sake of a couple of hours work, I think I'd be silly
not to at least do a bit of testing of the functionality of the database as
it is now (designed entirely in Access) with some of the tables linked to an
SQL Server database (all going well I will then investigate dsn / dsn-less /
VPN connections etc), with the view to look at Terminal Services / RDP
options if it becomes apparent that too much rewriting is needed.

My thought process being, I can install SQL Server on my own workstation for
free (Express with SQL Server Management Studio), but to do the TS option
properly, we'd need to purchase a dedicated server with (unfamiliar) server
software plus CALs.

My business partner has given me a lot of time and breathing room to get our
internal data and process management all consolidated (he is one of the ones
who needs regular overseas access to the database) and I'd like to be able
say to him "OK, all done. Just do this, this and this and you will be in. And
it cost us virtually nothing more than my time."

We're nearly 7 years old and so far, I have managed to keep the IT entirely
under my control. I have concerns about outsourcing IT as I feel it is a path
that, once started down, would be very difficult to come back from.

I will ask any new questions I might have relating to this matter in a new
post.

Cheers,
Jon.

02-Mar-10 10:09 PM

David;

Go play with your deprecated baby sized jet databases, kid

-Aaron



/

03-Mar-10 04:38 PM

On 1 Mar 2010 22:53:01 GMT, "David W. Fenton"


Hi David,

I agree with this statement if you add the word "almost" before

We do have apps where we expose the AutoNumber/Identity key to the
user as a read-only "reference" number.  They know that there may be
gaps, and that it is merely a unique identifier to be sure that two
people are looking at the correct record.  it is especially handy in
scenarios where there are many similar-looking transactions, for
example.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

04-Mar-10 05:14 AM

That's not going to tell you anything about what performance will be
like over the Internet.


Do you have a Windows server? If so, you can test WTS with one of
the administrative logons.

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

04-Mar-10 01:43 AM

Jet was never officially deprecated, but it did not get a lot of attention
for a while. Then the product team gave it some attention, and improved it
along with creating a descendant (the ACE database engine) in Access 2007.
At about this time, they ceased recommending ADP as the preferred Access
front-end for SQL Server, and now recommend MDB with ODBC for that purpose.

Some people just cannot seem to get the story right... either they are
ignorant or they choose to disseminate false information.

Larry Linson
Microsoft Office Access MVP

04-Mar-10 06:03 PM

wrote in


We will, we will.

But at least we actually know something about it that is factually
true. You promote SQL Server for everything, yet, you do not seem to
know the first thing about it given how often you post factually
challenged information.

(I know you are going to ask for examples, and I will just say read the
followups to any post you make about SQL Server and you will have your
answer)

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

04-Mar-10 06:08 PM

This is true and false. Many people point to some MSDN articles that
have the roadmap for MS's database technologies and it deprecates
Jet. But they fail to notice that the article was written to guide
developers transitioning from MS's old development tools (from the
VB6 era) to the .NET family of development tools. it is obvious that
one really oughtn't use Jet/ACE for .NET development (though perhaps
the 64-bit ACE will make that no longer a vulnerability), and that is
all the articles "deprecating" Jet were about. They were deprecating
Jet for a particular purpose, not for all uses.

The proof is in the pudding of course, with A2007 having shown that
MS has no intention whatsoever to deprecate or replace Jet at all.
Even if they are hoping that Sharepoint will take over all small
business usages, they still need Jet/ACE as local database engine
for managing disconnected data. But having it also brings with it
Jet's historical ability to work with the widest variety of data
sources of any database tool out there. And do it well, with good
default performance and a host of tools to make it easy.


It will be interesting to see what comes of the current efforts to
work with SQL Server developers to make the version of Access after
2010 more versatile and SQL Server-friendly.


I cannot quite understand Aaron's actions as anything but those of a
person who is at some level mentally disturbed. But I am not a
doctor, so I could be wrong on that.

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

15-Mar-10 05:06 AM

uh, the extra overhead from maintaining, developing and administering
linked tables is just flat out unnecessary work

Access Data Projects allow you to keep all your information, queries
and logic - in one place - where it belongs - on a database server
(SQL Server 2005 is most likely candidate these days)



re:
There is really nothing hard about managing a split app and it is
much, much safer and more reliable.


F7FA-A493-468B-B976-8CA2D987CDB9@microsoft.com:
/

15-Mar-10 05:07 AM

Larry;

The only reccomendation I have ever seen from MS says something about
'reccomending ADP for performance reasons.. ESPECIALLY FOR REPORTING'

So I do not have any clue WTF you are talking about, jackass!

-Aaron



t
.
e.

15-Mar-10 05:10 AM

David;

you run around, claiming that jet replication is the only answer for
copying data back and forth over a wan.

you sit there and claim that SQL Server is _SOOOOO_ difficult to use.

but the FACTS state that using a single link, instead of 50 different
connection strings (one for each table? WTF?) is somehow less
efficient

You sit there and claim that copying tables, and copying queries and
refreshing links.. Is this somehow _FUN_ to you?

Access Development is much more fun if you just STFU, ignore the Jet
crybabies.. and use the most popular database engine in the world (SQL
Server)
Access Development is much more fun if you just STFU, ignore the Jet
crybabies.. and use the most popular database engine in the world (SQL
Server)
Access Development is much more fun if you just STFU, ignore the Jet
crybabies.. and use the most popular database engine in the world (SQL
Server)






/

17-Mar-10 06:59 PM

JET does not have the concept of trusted locations.
ACE does not have the concept of MDW security.



But then it is not ACE is it. it is JET.

(david)

17-Mar-10 07:39 PM

The idea that you can adequately describe a concept
by definining it is differences from a familiar concept is
a beginners mistake in teaching and training.

More fundamentally, the idea that you can teach a
concept by replicating the way an experienced person,
who understands the concepts, thinks about the concepts,
is a beginners mistake in teaching and training.

So, I was never trying to explain the differences between
SQL Server and the service derived from SHARE.EXE.

I was trying to explain what they had in common, for a
person who had described himself as 'daunted by SQL
Server'


The rest of it was trying to help people who were putting up
misleading information about 'opening ports' and 'services'
and stuff like that. Those mistakes always lead to
misunderstandings further down the line.

(david)

17-Mar-10 10:37 PM

Well, since you ask...

it is like Windows explorer: you never stop to think how it works
any more. But once upon a time, Word Processing was done on
Typewriters, Layout was done on TypeSetters, and databases
were the main reason why commercial computer systems existed.
Even Unix, which in its academic version had no database system,
had built-in database primitives in the version most used inside Bell.

People bought DOS computers and used them for database
systems: the OS primitives which supported this are the
Windows Database Primitives.


DOS had only the most basic database service: read/write/flush
record, and file folders, which were then called directories. The
file folder is the 'Database' unit of DOS-based database systems.

The next important step was the addition of SHARE.EXE,
which added record locking to the file system. Windows 3.x
added a File API which was a thin cover for the DOS file
system, including GET,SET,LOCK, UNLOCK and file
folders.

The next important step was the addition of the Network
Redirector, which was the part of the Network Client which
re-directed File System commands to a Network Server.

The API has changed from Win16 to Win32 to WinNT,
but it has remained backward-compatible to DOS through
the whole change. The data storage system has changed
underneath, but the API has remained backward-compatible.

The Database Primitives than WinXP offers and that are
re-directed to the Server, are still the same database primitives
that DOS offered with SHARE. Create Record. Read Record,
Update Record. Delete Record. Lock Record. Unlock Record.
Missing is FLUSH (required for persistence), indexing, and
transactions. Novell Netware had the missing three: transactions
have finally been added to Windows, but are not yet used by
JET or ACE.

'CRUD' is the basis of all database systems, not just Relational
Database Systems. Create Record, Read-Record, Write-Record,
Lock-Record, Flush, and File Folders were the basis of all DOS
database systems, the systems which were replaced by Access
and SQL Server.

Access was built on top of the OS database primitives, as
were all previous DOS database system. It differed by
including everything inside one file (as SQL Server does).
The code was stored in records in the database. The
queries were stored in records in the database. The
relationships (that is, the table definitions) were stored in
records in the database. And the only way to manipulate
this data was by using the database system. In other words,
it was a very good (for the time and market) implementation
of a Relational Database System.

But the database facilities offered by Access far exceeded
those offered by the OS. Access was not a DOS database
system. It was a system built inside a DOS database
system.

Since that time, the OS has expanded a lot in some
directions, not at all in others.

In particular, DOS and Windows 3.x had no user-level
security. Windows now does have user level security.
Windows Servers now know not only which folders
are shared, but who is allowed access to them.
Windows servers now know not only which records are
locked, but which user and computer has locked them.

If Windows had had user-level security when Access
was first written, Access might have used it. Instead,
Access created its own system of keeping track of which
users and groups had permissions, and which users and
workstation locked records in the database. It did this on
the one hand by using a database of users and groups, and
on the other by using a database of logged-in users and
record locks, which it implements by using OS-level record
locks to lock the Access-level lock records

There used to be some old white papers around which
explained that clearly and with diagrams.

Note, Record locks are not File locks, are not Page locks, are not Sector
locks, are not Cluster locks, because OS-level data records are not sectors,

19-Mar-10 03:15 PM

I am not trying to cause you grief.  It seemed like you were merging
two separate concepts.  I will take your word for it that you "dumbed
down" your explanation to point out similarities.  The only refereed
publication I ever wrote dealt with the issue of learning based on
existing knowledge.  It was for the Systems, Man, and Cybernetics IEEE
Journal.  The theme was that learning by analogy is a byproduct of
optimization rather than a means to an end.  I also gave credit to the
person who pointed that fact out to me.  Naturally, the talk went into
more detail than the paper.

James A. Fortune
MPAPoster@FortuneJames.com

I'd like a computer to have a model of everything I know, then read
books and just show me the parts I do not already know.  -- JK

07-Apr-10 05:12 PM

wow.. is _TEST_ a valid licensing use of something designed for
_ADMINISTRATIVE_ purposes?

sounds to me like David Fenton does not understand the licensing
implications of breaking licensing agreements??



/

28-Jul-10 01:25 AM

I stumbled upon this post while researching something else and was intrigued by the trains of thought expressed.  IMHO, the problem originally expressed had nothing at all to do with comparing database products but with accessibility to information.  You could simply create a universally accessible website, in your favourite environment (php, ASP, ASP.NET, ...) connected to any database you prefer (yes, even Access, though that choice is WAY down the list, for reasons like bloat, security, programmability, when compared with MySQL, SQL Server or myriad real databases).  Ego driven arguments comparing products seem to have subverted the original poster's accibility problem.



Did you ever sort this out?

28-Jul-10 06:07 PM

wrote in


Sounds to me like Aaron Kempf does not have a clue what he is talking
about.

There is no distinction between test and administrative purposes.
Anyone who has an administrative logon can connect via RDP to any
Windows server that has Terminal Server connections enabled. This
feature is provided to allow administrators to administer the
server, but there are no limitations on what you can and cannot do.

If you want to test WTS, change your user logon to an admin and then
you can log onto the server and see how WTS performs. There are no
license limitations here, either stated or implied.

--
David W. Fenton                  http://www.dfenton.com/
contact via website only     http://www.dfenton.com/DFA/