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
  #1  
Old February 25th, 2010, 05:42 PM posted to microsoft.public.access
Jon22
external usenet poster
 
Posts: 36
Default Might be outgrowing Access but daunted by SQL Server

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.
  #2  
Old February 25th, 2010, 06:21 PM posted to microsoft.public.access
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default Might be outgrowing Access but daunted by SQL Server

hi Jon,

On 25.02.2010 17:42, Jon22 wrote:
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.

The first step is almost quite easy. Either use the Access built-in
upsizing wizard or use SSMA

http://www.microsoft.com/downloads/d...displaylang=en

To push your back-end to the SQL Server. You need to test your
application as some things my be broken during migration as some
DAO/Access/Jet specific things do not longer work.

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

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

The easiest way is to use a Terminal Server (-2008) or Remote Desktop
Services (2008r2).

Here you simple give your users another "virtual" desktop which can be
easily access over the net. Your application still runs local to your
domain network.


mfG
-- stefan --
  #3  
Old February 25th, 2010, 08:28 PM posted to microsoft.public.access
Albert D. Kallal
external usenet poster
 
Posts: 2,874
Default Might be outgrowing Access but daunted by SQL Server

I outline some ideas he

http://www.members.shaw.ca/albertkallal/Wan/Wans.html

if you don't want to change anything in regards to your application and
don't know anything, then you could have your IT department setup remote
desktop. They do the network + infrastructure, and once done, you continue
to do everything as you have now (well, you should split your database).


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



  #4  
Old February 26th, 2010, 01:54 AM posted to microsoft.public.access
Jon22
external usenet poster
 
Posts: 36
Default Might be outgrowing Access but daunted by SQL Server

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'll 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)

"Albert D. Kallal" wrote:

I outline some ideas he

http://www.members.shaw.ca/albertkallal/Wan/Wans.html

if you don't want to change anything in regards to your application and
don't know anything, then you could have your IT department setup remote
desktop. They do the network + infrastructure, and once done, you continue
to do everything as you have now (well, you should split your database).


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



  #5  
Old February 26th, 2010, 03:00 AM posted to microsoft.public.access
Jon22
external usenet poster
 
Posts: 36
Default Might be outgrowing Access but daunted by SQL Server

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'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'?

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

"Stefan Hoffmann" wrote:

hi Jon,

On 25.02.2010 17:42, Jon22 wrote:
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.

The first step is almost quite easy. Either use the Access built-in
upsizing wizard or use SSMA

http://www.microsoft.com/downloads/d...displaylang=en

To push your back-end to the SQL Server. You need to test your
application as some things my be broken during migration as some
DAO/Access/Jet specific things do not longer work.

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

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

The easiest way is to use a Terminal Server (-2008) or Remote Desktop
Services (2008r2).

Here you simple give your users another "virtual" desktop which can be
easily access over the net. Your application still runs local to your
domain network.


mfG
-- stefan --
.

  #6  
Old February 26th, 2010, 04:37 AM posted to microsoft.public.access
Banana[_2_]
external usenet poster
 
Posts: 214
Default Might be outgrowing Access but daunted by SQL Server

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.
  #7  
Old February 26th, 2010, 06:39 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

"Jon22" wrote in message
...
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.


I don't want to be misleading, and I don't 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 don't 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'd still be better off to use SQL server.

If I chose to run SharePoint from our office rather than use Office Live,
do
you think it'll 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)


There's 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 don't 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 don't 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're not running SharePoint 2010, and it can be pretty
much assured that you're 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 can't
say it's anything close to what we're 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 don't have to
learn anything new - however this is likely you're more expensive option
from an infrastructure and IT point of view.


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


  #8  
Old February 26th, 2010, 08:15 AM posted to microsoft.public.access
Jon22
external usenet poster
 
Posts: 36
Default Might be outgrowing Access but daunted by SQL Server

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

  #9  
Old February 26th, 2010, 08:16 AM posted to microsoft.public.access
david
external usenet poster
 
Posts: 34
Default Might be outgrowing Access but daunted by SQL Server


"Jon22" wrote in message
...
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


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.

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.

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

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.

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.

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?


Wherever you want it.

What IS a server anyway?


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.

Can one of our more powerful desktop workstations act as our 'server'?


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.


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?


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's not magic.

Would the SharePoint option make using the database slower at the
office?


Haven't tried the new SharePoint. You don't want the old SharePoint.

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 such 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?



SQL Server is not a real-time communications network. It's just a
database.



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.


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

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

(david)


  #10  
Old February 26th, 2010, 08:21 AM posted to microsoft.public.access
Jon22
external usenet poster
 
Posts: 36
Default Might be outgrowing Access but daunted by SQL Server

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'm a pretty fast learner so we'll see how we go.

"Albert D. Kallal" wrote:

"Jon22" wrote in message
...
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.


I don't want to be misleading, and I don't 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 don't 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'd still be better off to use SQL server.

If I chose to run SharePoint from our office rather than use Office Live,
do
you think it'll 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)


There's 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 don't 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 don't 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're not running SharePoint 2010, and it can be pretty
much assured that you're 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 can't
say it's anything close to what we're 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 don't have to
learn anything new - however this is likely you're more expensive option
from an infrastructure and IT point of view.


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


 




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 04:33 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.