Access Front end with MySQL or SQL backend (multi-user)

[Jorge via AccessMonster.com]

I have a huge excel spreadsheet with many worksheets tabs that I need to
convert to a data entry database. I'm considering using Access as the front
end with maybe MySQL or I think SQL which is free with MS Office.

The problem is
1. We can have more than 75 concurrent users
2. budget is extremely limited, so I'm looking at Access front and/or a free
SQL backend

Will this combination support 75+ concurrent users? and if not, does anyone
have any other suggestions?



Thanks!
Jorge

 

[Jeff Boyce]

Jorge

Will all 75+ users be doing data entry simultaneously? Are all 75+
connected to the same LAN? Which version of Access?

Do you have any experience developing front-end GUIs? Using Access? The
learning curve can be a bit steep.

Depending on your application, it might be possible to buy a single
developer's license to Access and development tools and be able to deploy
the Access RunTime, rather than buy individual Access licenses for all
users.

Depending on the number of simultaneous data enter-ers, you might not need
to go to MySQL or the SQL Express version.

More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jorge via AccessMonster.com]

Yes, they are all connected to the same LAN. I'm using Access 2003, windows
XP. I do have experience developing front-end gui's in Access. I'm just not
sure about the limits of concurrent users. There is a chance that all 75 may
be in the database at the same time.

Thanks,
Jorge

Jorge

Will all 75+ users be doing data entry simultaneously? Are all 75+
connected to the same LAN? Which version of Access?

Do you have any experience developing front-end GUIs? Using Access? The
learning curve can be a bit steep.

Depending on your application, it might be possible to buy a single
developer's license to Access and development tools and be able to deploy
the Access RunTime, rather than buy individual Access licenses for all
users.

Depending on the number of simultaneous data enter-ers, you might not need
to go to MySQL or the SQL Express version.

More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a huge excel spreadsheet with many worksheets tabs that I need to
convert to a data entry database. I'm considering using Access as the

[quoted text clipped - 13 lines]

Thanks!
Jorge

 

[Jeff Boyce]

Jorge

It makes a difference whether all 75 will be doing data entry against the
same table (this probably exceeds the reasonable response time parameters
your users would have), or one data-entry person, and 74 folks looking up
information, running reports, .... (this probably would NOT exceed
acceptable response time requirements).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, they are all connected to the same LAN. I'm using Access 2003,
windows
XP. I do have experience developing front-end gui's in Access. I'm just
not
sure about the limits of concurrent users. There is a chance that all 75
may
be in the database at the same time.

Thanks,
Jorge

Jorge

Will all 75+ users be doing data entry simultaneously? Are all 75+
connected to the same LAN? Which version of Access?

Do you have any experience developing front-end GUIs? Using Access? The
learning curve can be a bit steep.

Depending on your application, it might be possible to buy a single
developer's license to Access and development tools and be able to deploy
the Access RunTime, rather than buy individual Access licenses for all
users.

Depending on the number of simultaneous data enter-ers, you might not need
to go to MySQL or the SQL Express version.

More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a huge excel spreadsheet with many worksheets tabs that I need to
convert to a data entry database. I'm considering using Access as the

[quoted text clipped - 13 lines]

Thanks!
Jorge

 

[Jorge via AccessMonster.com]

It's possible for 75 persons to do data entry against the same table. We
have people accessing the database on the network from many different states
in the US.

Right now it's a problem for excel. Is this a problem for Access too?
Should I maybe consider MySQL or SQL? or will i have the same problem?

thanks,
Jorge

Jorge

It makes a difference whether all 75 will be doing data entry against the
same table (this probably exceeds the reasonable response time parameters
your users would have), or one data-entry person, and 74 folks looking up
information, running reports, .... (this probably would NOT exceed
acceptable response time requirements).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, they are all connected to the same LAN. I'm using Access 2003,
windows

[quoted text clipped - 35 lines]

 

[Jeff Boyce]

Ah, you hadn't mentioned the "wide area network". You may want to re-post,
including this piece of information. There are Terminal Services (and
Remote Desktop Connection) options that others may be able to offer.
Natively, Access does not handle the WAN connections too well, so I
understand.

Regards

Jeff Boyce
Microsoft Office/Access MVP

It's possible for 75 persons to do data entry against the same table. We
have people accessing the database on the network from many different
states
in the US.

Right now it's a problem for excel. Is this a problem for Access too?
Should I maybe consider MySQL or SQL? or will i have the same problem?

thanks,
Jorge

Jorge

It makes a difference whether all 75 will be doing data entry against the
same table (this probably exceeds the reasonable response time parameters
your users would have), or one data-entry person, and 74 folks looking up
information, running reports, .... (this probably would NOT exceed
acceptable response time requirements).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, they are all connected to the same LAN. I'm using Access 2003,
windows

[quoted text clipped - 35 lines]

Thanks!
Jorge

 

[Jorge via AccessMonster.com]

In thinking more about your point of data entry against the same table.....

Would it make more sense to break up the data entry tables as they pertain to
the states or countries? We have Asia, Mexico, Japan, etc. For instance
right now, I have a countryID table. But if a country is picked then data
entry is done for instance, on the table for mexico. Then if another
countryID is chosen, then data entry is done for instance against the Asia
table. This way they all go to the main screen to pick the country, but then
are routed to their country for data entry. This will trim down the number
of concurrent users per country, per table to about 5 - 10 users each.

Your thoughts....

Jorge

Jorge

It makes a difference whether all 75 will be doing data entry against the
same table (this probably exceeds the reasonable response time parameters
your users would have), or one data-entry person, and 74 folks looking up
information, running reports, .... (this probably would NOT exceed
acceptable response time requirements).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, they are all connected to the same LAN. I'm using Access 2003,
windows

[quoted text clipped - 35 lines]

 

[Jeff Boyce]

Jorge

Setting up separate tables to "categorize" your data will lead to
considerable difficulties down the road. I'd advise against doing that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

In thinking more about your point of data entry against the same
table.....

Would it make more sense to break up the data entry tables as they pertain
to
the states or countries? We have Asia, Mexico, Japan, etc. For
instance
right now, I have a countryID table. But if a country is picked then data
entry is done for instance, on the table for mexico. Then if another
countryID is chosen, then data entry is done for instance against the Asia
table. This way they all go to the main screen to pick the country, but
then
are routed to their country for data entry. This will trim down the
number
of concurrent users per country, per table to about 5 - 10 users each.

Your thoughts....

Jorge

Jorge

It makes a difference whether all 75 will be doing data entry against the
same table (this probably exceeds the reasonable response time parameters
your users would have), or one data-entry person, and 74 folks looking up
information, running reports, .... (this probably would NOT exceed
acceptable response time requirements).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, they are all connected to the same LAN. I'm using Access 2003,
windows

[quoted text clipped - 35 lines]

Thanks!
Jorge

 

[Guest]

Will this combination support 75+ concurrent users? and if not,

Only if it is carefully designed. It's easy to lock up a database like
that if you starting holding stuff open: that's why you don't usually
let users run ad-hoc queries against a database like that.

Also, note that MySQL was traditionally a very fast multi-user
database because it was traditionally run in a read-only kind of
webserver mode. If you want a database to carry a transactional
load, you will need decent hardware to support it.

The free MS SQL is throttled, and I think that you will have to be
very careful if you try to use it. Of course, with 75 data entry users,
you should be carefull anyway, and the care is still probably only
the kind of care you would have to exercise anyway if you wanted
750 users instead of 75.

 

[Albert D. Kallal]

The free MS SQL is throttled, and I think that you will have to be

very careful if you try to use it. Of course, with 75 data entry users,
you should be carefull anyway, and the care is still probably only
the kind of care you would have to exercise anyway if you wanted
750 users instead of 75.

Actually, the new version that is free is not throttled anymore!!!!

The MSDE was throttled, but the new 'express' version does not
have that limitation anymore. So, 75 users would be no problem.

about:
http://www.microsoft.com/sql/editions/express/default.mspx

It is only limited to one processor..which in today world..it a lot!!

 

[Guest]

thank you. another more specific article is
http://msdn2.microsoft.com/en-us/library/ms165672.aspx

(david)

 

[Guest]

I've seen conflicting suggestions on the effect this has on the number of
supported users: "unsuitable for (a dozen) users", or "when you need to
support more users (than Access)". Have you seen any reports or numbers?

Incidentally, I see that SQL Server Compact Edition is actually the product
targeted at "local data storage" applications: it looks like it's in direct
competition with the new Access data storage format (although not in the
same market: you would have to write an Access program to store your
application in the database). Unless they've fooled us all and the Office
team actually used SQLce as the new database engine.
(david)

 

[Aaron Kempf]

using MDB is like carrying a bunch of dead weight around-- in the desert--
without a REASON

every major vendor offers a freeware solution

it's ridiculous to expect to use more than 5 users on MDB

 

[Aaron Kempf]

correction

Access MDB does not work across a WAN.

ADP works _GREAT_ across a WAN, across WIRELEESS, VPN, or the PUBLIC
INTERNET

in a LOGICAL world-- people would say 'to heck with MDB' and stop trying to
blame 'the network' for everything that is going wrong

Ah, you hadn't mentioned the "wide area network". You may want to re-post,
including this piece of information. There are Terminal Services (and
Remote Desktop Connection) options that others may be able to offer.
Natively, Access does not handle the WAN connections too well, so I
understand.

Regards

Jeff Boyce
Microsoft Office/Access MVP

It's possible for 75 persons to do data entry against the same table. We
have people accessing the database on the network from many different
states
in the US.

Right now it's a problem for excel. Is this a problem for Access too?
Should I maybe consider MySQL or SQL? or will i have the same problem?

thanks,
Jorge

Jorge

It makes a difference whether all 75 will be doing data entry against the
same table (this probably exceeds the reasonable response time parameters
your users would have), or one data-entry person, and 74 folks looking up
information, running reports, .... (this probably would NOT exceed
acceptable response time requirements).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, they are all connected to the same LAN. I'm using Access 2003,
windows
[quoted text clipped - 35 lines]
Thanks!
Jorge

 

[WRONG PASS WORD]

Will this combination support 75+ concurrent users? and if not,

Only if it is carefully designed. It's easy to lock up a database like
that if you starting holding stuff open: that's why you don't usually
let users run ad-hoc queries against a database like that.

Also, note that MySQL was traditionally a very fast multi-user
database because it was traditionally run in a read-only kind of
webserver mode. If you want a database to carry a transactional
load, you will need decent hardware to support it.

The free MS SQL is throttled, and I think that you will have to be
very careful if you try to use it. Of course, with 75 data entry users,
you should be carefull anyway, and the care is still probably only
the kind of care you would have to exercise anyway if you wanted
750 users instead of 75.

 

[Tony Wickernark]

the free SQL Server backends are _NOT_ throttled, kid

 

[Bones]

It comes up with---- can`t open check for low memory,i`ve done that