Hi,
I finally did it ... upgraded to Access 2007.

I'm a little intrigued by this new "SharePoint" feature ... and I'm trying
to "wrap my head around it" a bit.

I've been working on a new app using Access 97, and plan to convert it to
the A2007 format in order to share it with other co-workers.
The idea was originally to split it into a FE/BE, and to have the BE in a
shared network folder.

Our company has 3 branches, and we already use some sort of "VPN" to tie us
all together.


Enter the SharePoint idea.
=====================

For the purposes of my app ... I'm under the impression that the data tables
("lists") can be uploaded to a SharePoint location, and that read / edit
permissions could be enabled for different users???

Each location / user PC could have a run-time version and the MDE stored
locally, and have these SharePoint "lists" (on the internet) as the BE?

Is there a charge for using these "SharePoint Services"?
Could I do the same thing using our existing "intranet"?
Office2007 also offers some sort of free file storage space, but it looks
like only Word, Excel, and Powerpoint (?) documents can be opened and viewed
directly online?

I will need to be relatively careful, as my app will contain customer
contact information, as well as vendor site logins / passwords ... so I
don't want Joe Hacker having free access to this information either.

Go easy on me if I'm asking stupid questions ... OK? :-)
IT / Networking / "Intranet" are all still relatively unexplored concepts
to me, and I do the database thing as a distraction / hobby.

TIA,
Don

"Don Leverton" <NoDamnSpam (AT) Telus (DOT) Net> wrote


Correct. Keep in mind there there are size (performance) limitations in
terms of the size of these SharePoint linked tables. While you can stuff
80,000 rows into one of a share point lifts, I would suggest that if the
using this over and the kind of general intranet connection, I would keep
the size of those tables below 10,000 records, in fact in the 6000 record
range is OK.

if you have some typical sable sizes with 50,000, or even 100,000 records,
then I'll suggest that you use a SQL server as the backend and link that
over the Internet in place of sharepoint.

correct, but keep in mind that the "offline" mode does not work for the
runtime version. What this means is that once you link your tables to share
point, if you have the full version of access, you can click on the offline
mode, and then the traveling salesman can go on the road or go to some
salmon are some of entropy is some location where they don't have a good
Internet or any Internet at all. Once they back to some similar to Civilized
palce like a good wifi connection at their local coffee shop, then they can
go back "on line" and their changes are synchronized for automatically. so a
big feature the full version as compared to the runtime version is is
offline mode, which in effect is really recognize shun for you for free with
no additional programming required.

This is kind of like asking is there a charge to host your web site? if you
are to have some company servers with Internet server services running on
them, then it won't cost you anything.

So if your company does not have SharePoint up and running now, then you'll
have to purchase a setup and install share point services. Or you can use
the "on-line" edition from Microsoft called office live.com. Office live is
really great because it is ShaerPoint for free. The 1st 5 users and the
first 50 megs of data storage is free. after that you can purchase
additional users and additional storage. kind amazing that you get the setup
for free. When I entered this computer business many years ago, a five user
computer with a 20MB drive was about $35,000.00. Now it is free, and you can
have it up and running and less time it takes for me to write this post to
you.

if your plan is to use sharepoint, and you have share point running in your
company now, then yes you can (and should consider this). However if you do
not have share point services setup, nor do you have the expertise and
training and resource personnel to run and set up those servers +
SharePoint, **if** you users NOW have some type of connection to you network
(a vpn), then then I think the easiest and least amount of effort would be
to simply put the backend database on SQL server and and link your front
ends to that back end user. this approach is better than share point for
seveal erasons:

- you don't have the same performance and table size limitations like you
do and share point as I mentioned. also keep in mind that linked tables to
share point to not have a reference will integrity to nor do they have
cascade deletes like SQL server does.

- Far lower costs and less work because you don't have to learn share
point. Setting up SQL server is free today and is very low cost, and quite
easy to learn. so full of people are to have a connection to your network,
then you can get them connected to SQL server quite easily.

- however don't underestimate the usefulness of share point, as it allows
document sharing, allows you to share data + documents with customers. and
as mention it supports the offline replication mode for access databases if
you need this feature.

Not really...access tables can also be opened with a web browser. I'm
tempted to publish and show you what an access table looks like from your
web browser.....

The above reasons one reason why share point is really good, The management
of users and logons in granting permissions to view data is all built into
share point in is quite easy to use. So the these details are made
relatively easy for you. However, this is a moot issue if those people NOW
have a connection to your corporate network. In this case, then SQL server
is the most easy approach here.

So, if these people are outside of your corporate network now, then
expertise and ability to set up secure connections to allow them to come
into your corporate network and pull data from sql server is VERY seriuos
issue. Again this type of scenario that supports the free online edition of
share point, because then you don't expose your corporate network to these
people and it's relatively easy to add a few users and connect them up to a
shared linked table on SharePoint with "any" Internet connection and may
have. Furthermore you don't have some complicated system in which these
users have to be given access to your corporate network outside or when
they're traveling on the road. so this issue really comes down to what kind
of connection and what kind of access is they have now, or how easy you can
get them access to corporate network.

As I said the beauty of "office live" on line share point is you can have
SharePoint up and running and be testing a linked table in less time it
takes for me to write up his post...

So, keep in mind that linked lists in share point are not as flexible as
"related tables" we have in access or when we link to sql server. So, if you
have a highly complex access application with many related tables and many
rows in those tables, then I don't think sharepoint is the solution right
now for you.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> wrote:

Having met Albert I can say with great authority that Albert gets "excitable". Yeah,
that's a good word. Excitable. So I'm sure his brain is out running his fingers on
his keyboard many times.

That comment aside I do beleive he has stated that he is using voice recognition in
the past.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> wrote

Yes, I use the dictation software that shipped with office.

That should have read as

referential integrity

If you say "reference will integrity" fast, you see how it "sounds" the
same.

So, yes...I do use dictation software for my "long" posts....

It not that I failed to check the post, I accident hit the send key. At that
point, forcing readers to read a long winded post, and *then* discover I
have a follow-up post with corrections forces them to reread the whole thing
again - I just simply decided to leave the post as is.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

I didn't read/see anything in this thread about performance. Would placing
the BE on SharePoint improve performance (speed)? I'm so frustrated and
disappointed with our split db's slowness on the network that I am almost
regret my championing the switch from an 'old' production db to the much
more versatile Access app we are using now. All of the advantages of the
many features and functions in the Access app are totally diminished by its
'slowness' on our network. I'm searching for ways to improve the
performance such as having IT move all of the other folders/files one level
down from our app so that it stands alone on the directory it's located.
Yes, all of the updates, solid connections, patches, etc. are preformed by
IT nightly thru their Radaii system. All of our queried fields are indexed.
The auto name correct (or whatever it is called) is turned off as well as in
the subforms.

I'm trying to convince IT to give (purchase) us our own server so that
nothing else is on it but our app as this may speed queries, opening forms,
and printing significantly..

Thus I was intrigued by this discussion here on the SharePoint possibility
as our department has recently been introduced and given an
IT -sponsored/supported SharePoint portal. Maybe this is the answer.

An insights and thoughts here are appreciated....

Earl




"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> wrote

Aye-yie-yie ... I'm so confused! LOL
Comments inserted below.
---------------------------------------

"Albert D. Kallal" <PleaseNOOOsPAMmkallal (AT) msn (DOT) com> wrote

What I have in mind is a relatively simple app to record, primarily customer
special orders for auto parts at our store(s).
Right now it being done with pen and paper in something appropriately (?)
called "The Bible" ... because we swear on it constantly.
It's a mess of mammoth proportions ...

Think 1960's technology. Envision the guy with tape on the bridge of his
glasses and with the pocket-protector. Yeah, it's like that ... a genuine
time warp. There used to be a 60-something guy whose SOLE responsibility was
to order the parts and attempt to keep some semblance of order in this
archaic system. His prize possession was a rolodex of suppliers ... probably
a couple or three hundred cards.
He was bitter when he wasn't re-hired, and took them with him under his arm
when he left. (Fine by me! LOL)

We're trying to move on into the 90's here (sarcastic smile) ... and do some
on-line ordering from several vendors that do offer that kind of capability.
So naturally, I have created "tblSuppliers" which includes hyperlinks and
login information in order to facilitate that process.

I've managed to export a customer list from the Amador AutoPoint Software
.... which was in turn imported from another POS (dual meaning there)
text-based software program from Rinax.

Again ... it's in a sorry state, too.
CUSTOMER NAME field contains data in: "FirstName LastName", "LastName,
FirstName", " CompanyName", "NumberedCompany", and even a few "We're Playing
House" (I.e. "John Smith and / or Jane Doe") formats. Yeah, we're small-town
Alberta. Don't get me started on how phone numbers were entered ... but I
have that fixed.

So OK ... we're talking maybe 3000 records of customers with personal and /
or corporate accounts. Some of which are "repeats" because we had 4 branches
in 4 neighboring small towns. It was common for customers to have accounts
in more than one store / town ... which meant more than one statement.
Honestly, it couldn't get much more complex and bizarre than it was. This
Amador system at least allows billing to ONE account at all 3 of the stores
that we bought out. (The original owner retained one of them.)

Ok... not every customer that walks in is going to have a charge account.
The "Bible" includes many "Jim. 555-1234" type entries.
Is that a home, work, or cell number? Who knows!?

It's going to be my intention to offer to have customers fill out "Contact
Details" forms as a service to them. When they want to order something,
simply ask them if we have them in our database yet ... (similar to visiting
Future Shop or The Source).

Yes, I have discovered that many people will have the same phone number,
whether it's a father / son / brother scenario ... or maybe they're giving
us a work number. I can deal with that ... with a pop-up form that displays
possible duplicates. Recording the "walk-in" customers could easily double
or triple the number of records?

So far we have suppliers and customers. Now it's time to tie in the parts
being ordered. A single instance might include multiple parts being ordered,
with a strong chance that they might even be coming from different suppliers
.... shipped by different methods, and arriving on different dates.

That will be the biggest table, I would imagine. I'm envisioning an
"archive" feature being incorporated to keep the record count down. Perhaps
archiving once per month for the prior 6 months? No doubt there will be
warranty situations ... and having access to that kind of data might be
useful.

----------- Finished product vision:
A customer comes in wanting to know if their order has arrived.

Name?
Phone Number?
OK ... yup, I see that Don had ordered and ignition coil for you last
Thursday, and it's here now. It looks like you've already picked up the
plugs and wires that Brad you ordered for you on the previous weekend,
right? I'm still waiting on that set of center caps that were back-ordered
from ____ in ___________ 3 weeks ago ... I had better check on those for you
again today.

Could I really imagine having that kind of information at my fingertips? Oh
yeah ...
Even if the guy was in one of the other stores ... asking "Where is it?" ...
but that's just still a fantasy. LOL




Hmmm ... I've written a little code that tests to see if the linked tables
are available at the "usual" location (which is either on the network share
at work, or in the My Documents folder of my home PC. I'm thinking that this
code could be reworked to default to a "stored copy" on the salesman's
laptop. Not up-to-the-minute ... but he could phone the store with what he
knows, and ask if there was any newer information.
No, we do not have SharePoint Services.
I had a quick look at this, and I think what you are referring to is "Office
Live Small business" which is indeed very affordable.
It does say there that Word, Excel, and Powerpoint documents can be uploaded
and opened from that location directly ... but not Access.
Are you saying that I could still have the backend (BE) stored there, and
just link to them with an MDE stored locally?

Again, I had a quick look, and found "Microsoft SQL Server Express" which
appears to be free.
( http://www.microsoft.com/express/sql/download/ )

If I could talk to the Amador people ... who are the ones who have set up
this VPN for us ... perhaps they would allow the space for this on the
server? If it's a VPN and the server resides within this protected area, the
only "downside" might be that customers would not have access to this data
.... Do I have the concept correct? If so ... that is just fine. This
application is designed to be for store staff only ... in all 3 branches
would be ideal, actually.

------------------------------
You make it sound so easy !... LOL
Not so for a guy that really hasn't played around with it much yet.
I'll "phart around" with it for a while, as time permits, though.
I do enjoy SMALL challenges.

Not all THAT complex, YET.
The present model includes about 9 tables, centered on "tblOrderDetails"
Speed is not the biggest concern, especially when I shudder to think how
much time we are wasting with the current "Bible" system.

Thanks again,
Don

Earl Anderson wrote:
I run Office 2007 on a desktop with an Intel E6750 (dual) cpu and 2 gigs
of ram. It's fine, fast if not snappy.
But on other, older computers with a single processor it's been
gruesomely slow and I've uninstalled it and reinstalled Office 2000,
2002 or 2003 depending on what seemed suitable (for instance, Office
2000 on a Celeron 1.7).
If I were going to work with Office 2007 extensively I'd look at a quad
processor, and a 64 bit operating system so I could access several gigs
of ram. But maybe I'm doing something wrong.

http://groups.google.com/group/comp....60cd1cb832fe5e


Have you followed tips at Allen Brownes or Tony Toews sites? Do you
have NameAutocorrect on? Use long filenamea (over 8.3)? Deep folder
structures?
http://allenbrowne.com/search.html
http://allenbrowne.com/VistaCPULoad.html
http://www.granite.ab.ca/access/performancefaq.htm

"Earl Anderson" <isobadd (AT) optonline (DOT) net> wrote


In most experience moving the backend data to sharepoint or even SQL server
will not fix performance problems. MS access is no slower than the vb.net
when using SQL server as the backend database.

As I stated moving the backend data to SQL server will usually slow it down
even more. It is only when you modify your design to take advantage of SQL
server who will you see an increase in performance. 9 out of 10 times the
issues of performance is due for the follwing reasons:

1) something in the network is not set up correctly or the network is simply
slow.

2) something in the configuration setup of MS access is not correct and
that's making things slow

3) the design of the architecture the application is dragging too much
information over the network.

Did the above move help at all? If the above made no difference, then the
above was not a issue for you. In other words this deeper directory issue
does not always affect performance on some networks. So, this issue made
have done nothing to help you, but at least it good to resolve one issue out
of a long list of issues.


The first number one issue on this list of network things to check is to
enusre ensure that you have a persistent connection. I think this advice is
given here once every other day in these newsgroups. for some of my clients
this does not make a difference, for others it increases performance by five
times or more. so I assumed that you create an mde, and this front and NT is
a place on each computer. I assume that in your startup code you open a
connection to the database (back end) and keep this connection open at all
times.

I doubing the above would help unless there is a significant amount a users
and network traffic and workload on that server you have now.

There's no question that you can get a SQL server backend database, or
tables linked to share point to run in a lesser bandwidth environment.
However in many cases the advantages of this technology can only be had if
you're architecture (desing of your application) takes advantage of a
limited bandwidth.

I going to repeat this again:
Moving your back end database to a super duper high speed corporate cluster
of 10 SQL servers capable handling a thousand uses at once without breaking
into a sweat will NOT improve the performance of the application if you've
not designed it well as such a to reduce bandwidth requirements. You can
increase the performance that server as much as you want, the problem is
you're not changing your network nor are you changing the bandwidth of your
network. in most cases to take advantage of these new technologies you have
to have designs that are such that respect the network you are on.

So, is SQL server or share point a possibility for you? Yes. will move into
share 0.0 SQL server solve your performance problems of magically without
effort on your part? NO.

You don't mention how many users and what is the max size of tables in your
application. Maybe it's possible that you've simply grown your current
network infrastructure and therefore you're suffering performance problems.
In other words let's say your application runs well if two users, but when
you run twenty users it runs too slow.

So after how many users does your application start slow down? At what point
are you finding your network and your application running too slow?


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

"Don Leverton" <NoDamnSpam (AT) Telus (DOT) Net> wrote

Ok, that's good. It's absolute critical for me to ascertain what mean by you
"sense" of scale. (how high, how far, how fast etc).

a talk to some people that tell me they have a little table in excel are
working with, and I go there and I see this Excel sheet that has 200,000 row
of dated and they are asking me how come it's slow?

While 200,000 rows is absolutely nothing for access, that many rows can
really bogged down excel sheet if you doing calculations on whole data set.

Ok, that menas we not dealing with 2 million records in a file. Again it's a
question scale here. If you don't have a network and you're dealing with 2
million records, you can deal with 2 million records quite well even in MS
access.

OK, so we've elimited one issue having data files that are too large to deal
with in SharePoint. As I mentioned files in the 10,000 to 20,000 reocrd
range are large for share point. In this case I would recommend SQL server
and thus these type of file table sizes are absolutely nothing.

I don't think your record count issues are too big of a problem for
SharePoint. The next probem to deal with is that SharePoint does not support
refferntinal integrity (RI). What this means is that you have to delete an
invoice record in the database then with MS access + RI them the invoice
details in the invoice detail table will also be we deleted for you
automatically.

If you move those tables to share point, then you'll have to put in some
extra code to delete the invoice detail records. For some applications that
only have three or four tables, this is not a big deal. However with any
decent order taking and sales order system you like to wind up with 30 to 50
normalized tables that are related to each ohter. So, Again moving this type
of application with many related tables to share point is NOT recommend.

Perhaps it was just a sales prospect list that your sales reps on the road
needed to share, then again sharepoint works well here. That sales force
could all use that "client list" while on the road when they have an
internet connection. In fact what's great about sharepoint is it also has
offline mode that will re-sync once anyone gets back to a civilized place
with decent Internet connections.

So solutions and answers to these problems are never simple all or nothing.
Nor do we have to move everything to share point. Simply put share point has
limitations in terms of large tables, and does not have RI built in .
Depending on the application you have (or want) these issues may be a big
problem, or they may not be a problem at all.

Sure, the above is typical of many order and sales sytems today. However,
the more Flexibility and features you want, the more complex the application
becomes. Building such a systems with sales, sales follow-up, order taking,
and even tracking status of orders etc. is going to take a considerable
amount of software developument skills. The limitation here is not MS
access, the limitation is how much time and effort you can invest here.

I mean when one of the employees has a problem and needs dental work, you
could go on the Internet and order some dental equipent. You can then read
up on how do this stuff, and then start doing some basic dental work on your
employees. You will thus save your company having to pay for a dental plan.
The same thing goes for any of the sales reps cars. Do you work these cars
yourselfs, or do you take that car to a shop that can fix it for you? In
most cases the Mechanic or dentist has less training than I do, so it's hard
to put these kinds of things into perspective.

Only you can make these decisions as to how much your time is worth and what
you're willing to put into such a project.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

On Wed, 15 Apr 2009 21:03:24 -0600, Albert D. Kallal wrote:

I've noticed that "modal" is spelled "model" in nearly all of your
posts. Those two don't sound that similar so I always figured it was
your spell-checker doing that.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com