Access or SQL Server 2005?

[analyst]

I need to migrate an Excel app I developed ten years ago, that has evolved
into something with a life of it's own.
But I know little to nothing about database platforms and development.

The Excel file stores info in 60 cells on each line of the spreadsheet, a
new line for every sample that is received. This spreadsheet grows to 10,000
lines in a couple months and I have to archive and purge it back down to a
more stable size.

The biggest plus I want is for more than one user to be able to enter data
in the file at once.

Although it is unlikely more than 10 users will access the file at once, I
didn't think it would grow to THIS size..

SHOULD I focus on having this migration move into an ACCESS format, running
on WinXP Pro?
I have a partnership action pack from Microsoft, and I'm trying to figure
out what the best set up would be. I'm probably going to hire a consulting
firm to handle this migration, so I'm sure they'll tell me what they
recommend.

BUT in the meantime... I'd like to load OS and software that will work best
with where I'm eventually going....

What came from Microsoft...
Office Pro '03 (10 user)
Windows Server 2003 (10 user)
Windows Small Bus. Server 2003 (10 user)
Windows XP Pro (10 user)
Windows XP Pro x64 (10 users)
SQL Server 2005 (10 users)

Roll out the Server 2003, or stick with XP Pro?

[David W. Fenton]

"analyst" <[e-mail]> wrote in
news:du*********@enews1.newsguy.com:

BUT in the meantime... I'd like to load OS and software that will
work best with where I'm eventually going....

What came from Microsoft...
Office Pro '03 (10 user)
Windows Server 2003 (10 user)
Windows Small Bus. Server 2003 (10 user)
Windows XP Pro (10 user)
Windows XP Pro x64 (10 users)
SQL Server 2005 (10 users)

Roll out the Server 2003, or stick with XP Pro?

For what?

With 10 simultaneous users, you really ought to run a standalone
server, so have one machine with Windows Server 2003. That can be a
file server storing the Access data file (the tables only), and the
10 users can run the Access app from 10 WinXP Pro workstations.

I wouldn't bother with Small Business Server, as it has limitations
that are not of any help to you. The only thing it would be good for
would be running Exchange Server, but that's outside the scope of
the app you're asking the question about.

If you have concurrency problems in an all-Access, you can install
SQL Server and upgrade the back-end data file to SQL Server.

But start with Access alone. It should be just fine for 10
simultaneous users.

I would recommend, though, that you normalize your spreadsheet
before making it into a database. If you don't, you'll regret it.
That means breaking out all the repeating information into separate
tables. There's a wizard in Access to help you analyze your data and
normalize it for you. However, it's not foolproof (if you're a fool,
then be careful! :).

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

[Darryl Kerkeslager]

> But I know little to nothing about database platforms and development.

Then you will need to hire somebody to do the work for you, and it will be
cheaper to hire an Access developer, and they will be able to do the project
quicker.

The Excel file stores info in 60 cells on each line of the spreadsheet, a
new line for every sample that is received. This spreadsheet grows to
10,000
lines in a couple months and I have to archive and purge it back down to a
more stable size.
While this may seem huge, once it is split up into tables and "normalized",
it may not be as big as it seems, and may grow much more slowly.
The biggest plus I want is for more than one user to be able to enter data
in the file at once.
Assuming you have a LAN for these users, the database will probably be
trivial. Don't go overboard - no need to spend a fortune, here.
Although it is unlikely more than 10 users will access the file at once, I
didn't think it would grow to THIS size..
You and everyone else ...
BUT in the meantime... I'd like to load OS and software that will work
best
with where I'm eventually going....

So you Don't have the LAN yet ... then better to wait until the software
developer starts, and use that person's recommendation.

Might I suggest to you that this NG is an excellent resource, and unless you
are burning to gamble cash on an unknown "consultant", you would do well to
continue here:

1. Describe your problem in detail, and decide exactly what you want the
solution to accomplish.

2. Go to the websites of several of the prominent members of this NG, and
present your specs, along with sample data from the Excel file (if it is not
sensitive data). Solicit bids.

3. Ask if one of the non-bidders would offer you insight into the
proposals, for a nominal consulting fee.
--
Darryl Kerkeslager

[analyst]

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:yc******************************@comcast.com. ..

While this may seem huge, once it is split up into tables and "normalized", it may not be as big as it seems, and may grow much more slowly.
Excellent point. It should actually shrink quite a bit once "normalized."
Assuming you have a LAN for these users, the database will probably be
trivial. Don't go overboard - no need to spend a fortune, here.

I have a LAN, and DSL is distributed to users on a "need email" basis.
The cost is remarkably low for me, as I have a ton of software from
Microsoft, and piles of hardware.
Just working on what to do with it.

Actually writing a list shows me up to 16 users could possibly access the
database at once.
I think that means I need it to happen in SQL Server, unless Access 2003 can
get there.

I have multi-user lic. for...

SQL server.
Office Pro 2003
Windows Server 2003
Windows XP Pro

Can the users running Access, use a database stored on the server in SQL
Server?

[analyst]

I recounted...
16 users could actually access the data file at once.

With 10 simultaneous users, you really ought to run a standalone
server, so have one machine with Windows Server 2003.
That does look like the best plan of all I have seen and heard so far.

If you have concurrency problems in an all-Access, you can install
SQL Server and upgrade the back-end data file to SQL Server.

I'm not sure if this means everyone needs to be an SQL client, or just the
server.
I have an Excel file that queries an Access datafile for customer ID. Is
this the same idea?

If it is, are there pros and cons that favor users running Access vs SQL?
(other than price - no object here)

THANKS!!

[David W. Fenton]

"analyst" <[e-mail]> wrote in
news:du*********@enews4.newsguy.com:

I recounted...
16 users could actually access the data file at once.
Still not a problem for an Access-only solution (i.e., Access front
end, Access MDB as back end). However, it is possible with even a
small number of users to encounter problems if there are too many
simultaneous users for a single record (or single data page).

With 10 simultaneous users, you really ought to run a standalone
server, so have one machine with Windows Server 2003.

That does look like the best plan of all I have seen and heard so
far.

I would not do peer-to-peer when I had more than 5 users, and
especially not when I had access to a server.

If you have concurrency problems in an all-Access, you can
install SQL Server and upgrade the back-end data file to SQL
Server.

I'm not sure if this means everyone needs to be an SQL client, or
just the server.

The server has licenses installed on it that enable a certain number
of users to connect. These could be user licenses or machine
licenses, which means that they are allocated either according to
the Windows user logon, or by the Windows machine connecting to the
server. You need as many SQL Server licenses as you will have users
(there is no longer any Microsoft licensing that will allow you to
get by with fewer licenses on the basis of never exceeding a certain
number of simultaneous users).
I have an Excel file that queries an Access datafile for customer
ID. Is this the same idea?
As what?
If it is, are there pros and cons that favor users running Access
vs SQL? (other than price - no object here)

I don't see any similarity between the two scenarios (Excel with
Access vs. Excel with SQL Server). If you're going to user Access,
use it as a front end (and not just a data store) and get rid of
Excel entirely.

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

[David W. Fenton]

"analyst" <[e-mail]> wrote in news:du********@enews4.newsguy.com:

Actually writing a list shows me up to 16 users could possibly
access the database at once.
I think that means I need it to happen in SQL Server, unless
Access 2003 can get there.
No, not at all -- that's still well within the range of Access/Jet's
capabilities. We've had plenty of reports of people running with 50
users, and no small number of reports of apps with 100 or so users,
all built on Access/Jet.

[]
Can the users running Access, use a database stored on the server
in SQL Server?

That's the whole idea of having a SQL Server, yes.

But I don't think you should get into that until you've developed
the app all in Access. If you have problems then you can upsize to
SQL Server. However, it's unlikely that an unchanged application
design will be improved by merely moving the back end to SQL Server.

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

[Darryl Kerkeslager]

"analyst" <[e-mail]> wrote

Actually writing a list shows me up to 16 users could possibly access the
database at once.
I think that means I need it to happen in SQL Server, unless Access 2003
can
get there.
It can. I run about 80 users now over a WAN, with perhaps 40 concurrent.
While running Access over a WAN is a stupid idea that I would not recommend
to anyone, it does show that Access can be stretched to handle quite a bit.
I know that a well-written Access app could handle 16 users over a LAN very
easily.

Setting the back end data to run on SQLServer or Jet (the Access data store)
will likely make no difference, in all actuality. Both will be so fast,
that there will be no noticeable difference. Both will handle the users
fine. SQL Server may be a little more difficult to set up, but may be more
stable in the long run - but if both are above 99.9% stable, then are you
that concerned with .1% (pulling numbers out of thin air, of course, but you
get the point). Lots of little trade-offs, none of which should matter much
between the two for your application.

What you will get in your situation with SQL Server on the back-end is more
reliability (no more speed or glitz or anything else), but more complex
setup.

For the client, using Access will probably get you setup faster and cheaper.
Can the users running Access, use a database stored on the server in SQL
Server?

Exactly. Either one.


--
Darryl Kerkeslager