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'm going to rewrite it as briefly as I can:

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

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)

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?

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?

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?

"Banana" wrote:

Jon22 wrote:
I'm 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 don't 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'?

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've 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's 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's 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
"big iron" supercomputer- it has nothing to do with hardware in
question, though it is common to market "server hardware", but in fact,
"server" refers to the role in the communication. As explained above,
you need to have a place for the daemon to resides. The daemon very well
couldn't 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
"server" while other machines that requests data from this server are
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 doesn't 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's 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 didn't answer your question about files... That was deliberate as
I wanted to emphasize that with SQL Server (and any other server-based
RDBMS) you don't 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's .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.

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.

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's 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'm assuming you've installed SQL Server on
the same computer where you wanted to upsize the Access database so by
telling "localhost", it'll contact the daemon residing on your computer.

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

I hope this helps.
.