A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Might be outgrowing Access but daunted by SQL Server



 
 
Thread Tools Display Modes
  #11  
Old February 26th, 2010, 02:06 PM posted to microsoft.public.access
Banana[_2_]
external usenet poster
 
Posts: 214
Default Might be outgrowing Access but daunted by SQL Server

david wrote:
Dunno, haven't tried that one. I guess that includes Visual Studio
Express? Otherwise you will be stuck using SQLCMD to do even
the most basic tasks.


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

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.


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

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.


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

I'm struggling with the fundamentals of SQL Server databases. Is it a
single file like an .mdb file?


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.


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's 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's
the daemon's job to manage it and you communicate with the daemon.

Where does it get reside?


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


No. As I explained, daemon does all file handling here. 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're working with a serverless
source (e.g. a .mdb will have its filepath in the ODBC connection, but
you'll never see that for a Oracle, MySQL, DB/2, SQL Server!)

connectionstrings.com

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

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

Is it creating an SQL Server on the machine which is a different thing
to an SQL server database?


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.


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
"Server" service. There is of course the notable exception that if I
wanted to use Named Pipes, then yes, I do need "Server" service. But
with TCP/IP, then no. They have nothing to do with each other.

As I explained in my previous reply to Jon22, the term "server" actually
refers to a role, rather than some kind of program. A server usually has
a daemon (aka Windows service) that listens on a specific protocol (e.g.
a port on TCP/IP, a region of address for Named Pipes/Memory, etc.) and
react to the requests through there. It goes back to why it's important
to not be so concerned with the questions such as "where is the file?"
because those are wrong questions to ask.

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

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


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

There are times and places where re-writing a piece of functionality
into T-SQL, stored procedure, views or other SQL Server objects does
make sense, but I really don't believe it requires a wholesale rewrite.
But that is largely a function of how well written the application was
to begin with.
  #12  
Old February 26th, 2010, 07:00 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Might be outgrowing Access but daunted by SQL Server

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

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


Windows Terminal Server is by far the easiest approach.

Also, if you can wait, A2010 + Sharepoint 2010 is going to make it
even easier.

Without a server (web server or terminal server or SQL Server) on
the Internet or an Internet-accessible VPN, though, there's not much
way to do it, regardless of what back end you use.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #13  
Old February 26th, 2010, 07:03 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Might be outgrowing Access but daunted by SQL Server

"Albert D. Kallal" wrote in
:

If you have an IT department at your disposal, then I often
suggest windows terminal services.


It doesn't take much at all of an IT dept. to support WTS. If you
have a Windows server, it's pretty much a matter of setting up a VPN
to get through the firewall, and then adding CALs on the server.
Both of those things are one-time setup issues, and absolutely
trivial for anyone who is getting paid to do IT support.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #14  
Old February 26th, 2010, 07:09 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Might be outgrowing Access but daunted by SQL Server

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

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


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

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

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

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

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #15  
Old February 26th, 2010, 07:12 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Might be outgrowing Access but daunted by SQL Server

Stefan Hoffmann wrote in
:

After that you can dive into SQL Server Replication which is quite
easy to master, but you need some time to get into it.


The only reason to do this would be to provide disconnected access.
Keep in mind that SQL Server Express limits replication
funcationality (though not to the point that it cannot be used for
merge replication), so you might have to use a more expensive
version of SQL Server.

If you're not supporting disconnected editing for your remote users,
then you wouldn't need to do this at all.

All forms of replication are an order of magnitude more complex than
using a database without it. Conflict resolution has to be
implemented in some form (you can't ignore it without having your
data end up in a de facto corrupted state), and there are certain
schema designs that inherently do not work (for example, a self-join
with a required field is one that has to be handled very, very
carefully).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #16  
Old February 26th, 2010, 10:44 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Might be outgrowing Access but daunted by SQL Server

Banana Banana@Republic wrote in news:4B87420C.2070209@Republic:

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.


One of the issues here is that unlike Jet/ACE, you can't just copy
the files to a laptop and use them on the laptop disconnected from
your LAN. You have to have an instance of SQL Server running on the
laptop, so that any PC where you have SQL Server as local data store
is going to be running SQL Server, and this is a potential security
risk. Now, in the case where you're not storing any data on the
disconnected machines and just using the app when connected to the
network (LAN or VPN over Internet), you don't need SQL Server
running locally in order to connect to a SQL Server.

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

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.


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

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #17  
Old February 26th, 2010, 10:57 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Might be outgrowing Access but daunted by SQL Server

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

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


Remember, the SQL Server needs to be installed on a permanent server
(in a small office, it could theoretically be a workstation, but
this is really not advisable, unless nobody ever uses it), and for
it to be accessible to remote users, the port on that server that
SQL Server is listening for connections on has to be available for
the remote users to connect to it.

Think of using a web page: when you make am HTTP request, your
browser connects on port 80 (by default -- you could request
http://dfenton.com:80 to specify it explicitly), which is the
default open port for the worldwide web.

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. Instead, it is most common that a SQL
Server used for an app like this is made available on the LAN and
remote users connect to the LAN across the Internet over a VPN
(virtual private network). The security for initiating the VPN
connection is usual very high (some VPN client software requires
special key authorization and it's all encrypted so the data inside
the VPN tunnel cannot be examined by someone as it passes through
their servers), and thus it serves as a strong locked door to keep
people out of the LAN.

The alternative, i.e., making the SQL Server port public, is much
more dangerous. Sure, SQL Server has its own authorization (or uses
Windows authorization), but historically, there have been lots of
security holes that allow people to skirt proper authorization and
get into places they shouldn't be.

A VPN is harder to exploit in that way.

So, think of the VPN as a really long network cable that's connected
to the local LAN. You have to "plug it in" in order to see the
remote network (this usually involves initiating a connection
similar to a dialup connection, with username and password
provided), but once the VPN connection is established, you will have
a "local" network that is identical to that you'd see if connected
by wired cable to the LAN in the "home" office (with some caveats: a
sysadmin can configure exactly what a remote user sees and has
access to by managing certain configuration parameters, but the
general practice is to make the VPN view of the LAN as familiar to
the users as possible, i.e., similar to what they can see and
connect to when in the office on the wired LAN).

All that said, I'd still recommend Windows Terminal Server over VPN
instead of upsizing to SQL Server, since to do the former requires
no change to your application whatsoever, while upsizing to SQL
Server is a pretty involved process (particularly if you need to
design it for slow connections from the remote users)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #18  
Old February 26th, 2010, 11:00 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Might be outgrowing Access but daunted by SQL Server

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)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #19  
Old February 27th, 2010, 02:32 AM posted to microsoft.public.access
Albert D. Kallal
external usenet poster
 
Posts: 2,874
Default Might be outgrowing Access but daunted by SQL Server

"David W. Fenton" wrote in message

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


I very much agree with your assessment.

As a general rule due to having good skills with SQL server, then the issue
of learning curve and converting an application to run correctly over a WAN
or internet using SQL server is an easy task for me. (so, then sql becomes a
cheaper setup).

Comparing the learning curve of SQL server to a one time setup of terminal
services as you state, I think the terminal services suggestion on your part
wins hands down.


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


Cost for me using sql server is less. I have many successful applications
running over the Internet using SQL server. However, I done the learning
curve. We don't even used stored procedures. We use some pass-through, and
mostly link to views for joins on reports or pick lists. I can zero in and
get a access application up and running for SQL server in a WAN environment
in VERY little time now. I am also using a hosted option for sql server.
And, sql Azure can be had for $10 a month for a 1 gig database. I believe
the trials are on now, and I can't wait to try Azure.

However, WTS vs SQL server in BOTH cases, a server + VPN must be setup.

WTS really is far less learning overall. And, one does NOT spend money
modifying an already good working access application.

So, the larger and more complex the access application, the the more work
needed for SQL server. This thus again favors he WTS choice.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



  #20  
Old February 27th, 2010, 04:28 AM posted to microsoft.public.access
a a r o n . k e m p f @ g m a i l . c o m
external usenet poster
 
Posts: 1,108
Default Might be outgrowing Access but daunted by SQL Server

dude you can't run Linked Tables outside the LAN.

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

These people are just outright lying to you.

It works best to move to Access Data Projects.
Direct SQL Server is _ALWAYS_ more efficient than linked table BS.

That's why 80% of professional developers would never use Access for
any reason--

SQL Server direct is just more fun, faster, and easier.

SQL Server allows you to do things to your data that would make Jet
puke.

Jet just plain sucks.

File, New, Project (new database), browse for MSDE installation (or
SQL Developers Editon)
if you have Access 2007, then you can use SQL 2005 Express instead of
MSDE 2.0

-Aaron



On Feb 25, 8:42*am, Jon22 wrote:
I'm 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've 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 don't work. So I've 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'm 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'm no IT guru by any stretch so please bare this in mind with any
suggestions.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.