SQL Server or Sharepoint?

"C.Voce" escreveu na mensagem
...
I am considering starting a business offering a cataloguing and library
management system to potentially hundreds of clients. Individual clients
will
ideally have their own custom-made databases, although all of them will
have
essentially the same features, altered slightly from case to case.

I would like to have a main table of data (common to all clients) which
only
I can add to and edit, and then give clients the ability to copy any
number
of records from this table into their database, also adding in other
information to related tables (which will differ from client to client). I
will need to give clients access to forms and reports, but also ideally
have
the ability to monitor all of their data input remotely for the purcposes
of
checking and consistency.

Would this be possible using SQL Server - having my main table on SQL
server, and then giving an .accdb file to my clients, linked to my main
table? Or will all the information need to be stored on my server in order
for me to be able to edit it? Alternatively, would Sharepoint better suit
my
needs.

Any guidance greatly appreciated.

"C.Voce" wrote in message
...

- Am I right to think that SQL server would store the tables and queries,
and I could then create an access application with the forms and reports
etc
to install on my clients machines? How exactly would those applications
communicate with my server? Is an internet connection enough?


An internet connection is enough, but you want to read the following article
of mind of using access over a wide area network:

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

Also keep in mind it's quite easy to set up the connection. The problem here
is do you have the expertise to set a proper security? In fact it was a
couple of months ago I was setting up SQL server and I opened it up to the
Internet to do some testing for just a few hours. Within less than an hour I
was receiving logon requests and attempts from people on the internet to get
into that server. If you ever seen the old move "war games" there's a scene
where the guy has an auto dialer on a modem to look for unsecured computers.
Today there is internet "bots" that look over hundreds of thousands of IP
addresses in a fairly short time and they are looking for open unsecured
versions of SQL server. Once they find one, then and other bot takes over
and starts to entering hundreds of thousands of logon attempts of the most
common types of passwords and logon names used. At this point they then can
take the data from the SQL server, or often worse even get some basic
controls the actual server itself. So I can only say if you don't know what
the heck you doing in terms of networking securty you better hire and get
somebody else to set this up for you. This is not a do it yourself projects
unless you have some good training in this area.

Again I will restate this: Setting up the data connection is really easy and
anybody can do it in a matter of minutes. Setting up this security correctly
is a whole and other matter an issue.

- Which version of SQL server would I need to support possibly a couple of
hundred separate clients accessing their own separate databases and tables
on
SQL server. I'm thinking the 2005 express edition is a good place to
start,
but I don't know how far that would go?

How many users your system will support is an issue of how much data and how
much complex the processing is involved for each user. I don't see the
problem of hanging 50 users off of sql 2005. After that you could monitor it
and always upgrade to the traditional higher version that has more users and
processing capabilities.


- What file extension should I be using for the Access end of the
applications? I can't work out whether .accdb or .adp would be better. I
would ideally like it to appear just like an Access database on the
clients
machines, but I don't know if that's a possibility?


I only suggests using an ADP project if you're rewriting a new application
from scratch OR you're just converting an application has very little record
set code that's based on DAO. When you migrate to an ADP project a lot of
your code can break, so I can only recommend this as a good choice if you're
starting a whole application from scratch (or as metioned there not a lot of
DAO code in your existing application).

There are some performance advantages to an ADP. Another significant
advantage of the ADP projects is that it takes less skills to get better
performance over a network. Since you're using a network which has 100 times
less performance than your typical office network, then any amounts of
improvement in performance is going to be very important for the success of
this project. As a general rule I would avoid ADP project, but if you have
limited skills to SQL server then your performance will likely be better
with less effort by choosign an ADP project.

- I'm basically enlarging and upsizing an existing database structure, but
I
will need to invest in some technology. Is a high-powered PC enough to put
SQL server on, or am I looking at something much more extensive and
complex?


For starters a standard pc is enougth. Your biggest challenge will be to
reduce the applications bandwidth requirements and ensure you have the
security of that system set up correctly.

You could also consider the cloud edition of SQL server and is a really
interesting video of using access + sql server in the cloud with what's
called huron he

http://channel9.msdn.com/pdc2008/BB40/

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

I am completely confused by this.

I am in the process of creating an access db using 2007 for a small company
of approx 10 users.

There are 2 offices 1 in LA and 1 in NY.
I have them on a Sharepoint Hosted Site

I was planning on just putting the Completed DB on Sharepoint and
maintaining a backup copy and a development Copy.

But now i am hearing that I should be exporting all of the tables / queries
and reports to Sharepoint Lists, or I should be splitting the DB into a
Front End and a Back End.
and keep the Back End on Sharepoint Server, but give out local copies of the
Front End.

Why would i need to export it all to sharepoint lists?
Please HELP ME
--
Nancy


"edson batista miranda" wrote:


"C.Voce" escreveu na mensagem
...
I am considering starting a business offering a cataloguing and library
management system to potentially hundreds of clients. Individual clients
will
ideally have their own custom-made databases, although all of them will
have
essentially the same features, altered slightly from case to case.

I would like to have a main table of data (common to all clients) which
only
I can add to and edit, and then give clients the ability to copy any
number
of records from this table into their database, also adding in other
information to related tables (which will differ from client to client). I
will need to give clients access to forms and reports, but also ideally
have
the ability to monitor all of their data input remotely for the purcposes
of
checking and consistency.

Would this be possible using SQL Server - having my main table on SQL
server, and then giving an .accdb file to my clients, linked to my main
table? Or will all the information need to be stored on my server in order
for me to be able to edit it? Alternatively, would Sharepoint better suit
my
needs.

Any guidance greatly appreciated.

On Tue, 30 Jun 2009 16:29:00 -0700, Nurse Nancy
wrote:

I am completely confused by this.

I am in the process of creating an access db using 2007 for a small company
of approx 10 users.

There are 2 offices 1 in LA and 1 in NY.
I have them on a Sharepoint Hosted Site

I was planning on just putting the Completed DB on Sharepoint and
maintaining a backup copy and a development Copy.

But now i am hearing that I should be exporting all of the tables / queries
and reports to Sharepoint Lists, or I should be splitting the DB into a
Front End and a Back End.
and keep the Back End on Sharepoint Server, but give out local copies of the
Front End.

Why would i need to export it all to sharepoint lists?
Please HELP ME

If the users in LA and in NY need concurrent access to the same data, you
CANNOT use just a native Access database. Access doesn't "play nice" over a
Wide Area Network or the internet. The data itself would need to be in some
shared data storage medium, either in New York or in Los Angeles - and the
other office would need to connect to it.

If neither office needs any information from the other office you could just
give each office a copy. But a .mdb or .accdb file can't be executed as an
item on a Sharepoint list, and you can't connect from a .mdb/.mde/.accde
frontend to a remote backend.

What you CAN do is store the data on a Sharepoint List, and use Access to
connect to that data. This has disadvantages - you can't enforce Referential
Integrity on sharepoint lists as you can in a table, and would need to use VBA
code to ensure that you're not adding garbage data to the tables (e.g. putting
in sales to nonexistant customers of nonexistant products).
--

John W. Vinson [MVP]

John W. Vinson wrote in
:

If the users in LA and in NY need concurrent access to the same
data, you CANNOT use just a native Access database.

Sure you can, as long as you host the app on a Windows Terminal
Server in LA or NY.

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

thanks, i am still a bit confused.
NY and LA do need to share information.

My main tables are customer approx 2000 records
and Stations approx 6000 records. Is this manageable in sharepoint lists?
How would all of the queries forms, reports that are in the DB work if i
exported the tables to sp lists?

Going back to the question of splitting the DB,, i thought you could have a
front end on local machines connecting to backend on a server?
--
Nancy


"John W. Vinson" wrote:

On Tue, 30 Jun 2009 16:29:00 -0700, Nurse Nancy
wrote:

I am completely confused by this.

I am in the process of creating an access db using 2007 for a small company
of approx 10 users.

There are 2 offices 1 in LA and 1 in NY.
I have them on a Sharepoint Hosted Site

I was planning on just putting the Completed DB on Sharepoint and
maintaining a backup copy and a development Copy.

But now i am hearing that I should be exporting all of the tables / queries
and reports to Sharepoint Lists, or I should be splitting the DB into a
Front End and a Back End.
and keep the Back End on Sharepoint Server, but give out local copies of the
Front End.

Why would i need to export it all to sharepoint lists?
Please HELP ME

If the users in LA and in NY need concurrent access to the same data, you
CANNOT use just a native Access database. Access doesn't "play nice" over a
Wide Area Network or the internet. The data itself would need to be in some
shared data storage medium, either in New York or in Los Angeles - and the
other office would need to connect to it.

If neither office needs any information from the other office you could just
give each office a copy. But a .mdb or .accdb file can't be executed as an
item on a Sharepoint list, and you can't connect from a .mdb/.mde/.accde
frontend to a remote backend.

What you CAN do is store the data on a Sharepoint List, and use Access to
connect to that data. This has disadvantages - you can't enforce Referential
Integrity on sharepoint lists as you can in a table, and would need to use VBA
code to ensure that you're not adding garbage data to the tables (e.g. putting
in sales to nonexistant customers of nonexistant products).
--

John W. Vinson [MVP]

On Sun, 5 Jul 2009 23:51:01 -0700, Nurse Nancy
wrote:

thanks, i am still a bit confused.
NY and LA do need to share information.

My main tables are customer approx 2000 records
and Stations approx 6000 records. Is this manageable in sharepoint lists?

Easily. That's a tiny list.

How would all of the queries forms, reports that are in the DB work if i
exported the tables to sp lists?

The sharepoint list IS the table. It works very much the same as a local table
- you can use it as the basis of a query, a form, or a report.

Going back to the question of splitting the DB,, i thought you could have a
front end on local machines connecting to backend on a server?

You can... *if the server is on the same fast, stable local area network*. A
LAN is typically 100MByte/sec or better (often much better). A good wide-area
network, on a good day with light traffic, might be 1% of that. Access simply
*will not work* if your Access backend is in New York and the frontend in LA.

Actually it's worse: it can be done, and simple tests might trick you into
thinking that it's usable. But it will be too slow, too unreliable, and much
too prone to corruption and destruction of your database.

Another option you might want to consider is having the database (front and
backends) in the "main office" and having the remote office use Windows
Terminal Server, Citrix Server, or some other remote terminal connection to
log on to the home computer. This just transmits screen images from NY to LA,
rather than having the database contents vulnerable.
--

John W. Vinson [MVP]

John W. Vinson wrote in
:

Another option you might want to consider is having the database
(front and backends) in the "main office" and having the remote
office use Windows Terminal Server, Citrix Server, or some other
remote terminal connection to log on to the home computer. This
just transmits screen images from NY to LA, rather than having the
database contents vulnerable.

I would like to voice my vote for this as being by far the easiest
solution, assuming the server infrastructure is already in place and
all that would be necessary would be acquiring the WTS CALs and
adding some RAM to the machine promoted to serve as a Windows
Terminal Server.

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

John W. Vinson wrote in
:

Another option you might want to consider is having the database
(front and backends) in the "main office" and having the remote
office use Windows Terminal Server, Citrix Server, or some other
remote terminal connection to log on to the home computer. This
just transmits screen images from NY to LA, rather than having the
database contents vulnerable.

I would like to voice my vote for this as being by far the easiest
solution, assuming the server infrastructure is already in place and
all that would be necessary would be acquiring the WTS CALs and
adding some RAM to the machine promoted to serve as a Windows
Terminal Server.

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

I have built a sharepoint site for them via APTIX hosting. Right now the
Access DB is in one piece and resides in a document library in sharepoint.
Only 2 people are using it and they area able to check it out and enter their
data and run queries, reports by taking turns.

I thought Sharepoint could be used to share the DB,, and allow multiple
people to use, update the DB at the same time via active directory but it
doesn't look like that is allowable.

Soon the other 9 people will need acess. I think sharepoint is the best
option for them since they are already paying for it.

When i export the files to lists, can the queries and reports remain in the
access db? Would i give each person a copy of the DB to run locally and link
to the tables in Sharepoint?
--
Nancy


"David W. Fenton" wrote:

John W. Vinson wrote in
:

Another option you might want to consider is having the database
(front and backends) in the "main office" and having the remote
office use Windows Terminal Server, Citrix Server, or some other
remote terminal connection to log on to the home computer. This
just transmits screen images from NY to LA, rather than having the
database contents vulnerable.

I would like to voice my vote for this as being by far the easiest
solution, assuming the server infrastructure is already in place and
all that would be necessary would be acquiring the WTS CALs and
adding some RAM to the machine promoted to serve as a Windows
Terminal Server.

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