Access Upgrade?

[Jake]

I'm trying to run multiple users off access. Security is set up and I think
I'm coming to the point where we have too many users / too complex of a DB
and need to migrate to a different platform.

I really enjoy developing with the front end tools access provides e.g.
forms, reports, query builder. Its become a bit of a hobby and the tool is
extremely important to our business.

1. Is there a way to still use the front end development tools of access,
but get a better database, that might be quicker and allow for more users.

2. Eventually we would need this on the internet, so that would be a future
requirement.

Current users - 8
Queries - 200
Tables - 30
Forms - 100
Reports - 100

 

[TedMi]

Yes, you can use the Access front end to interact with a more robust
database, such as SQL Server or MySQL. But your parameters seem well within
the capabilities of Access as a back end - 8 users is quite manageable.
-TedMi

 

[John W. Vinson]

I'm trying to run multiple users off access. Security is set up and I think
I'm coming to the point where we have too many users / too complex of a DB
and need to migrate to a different platform.

I really enjoy developing with the front end tools access provides e.g.
forms, reports, query builder. Its become a bit of a hobby and the tool is
extremely important to our business.

1. Is there a way to still use the front end development tools of access,
but get a better database, that might be quicker and allow for more users.

2. Eventually we would need this on the internet, so that would be a future
requirement.

Current users - 8
Queries - 200
Tables - 30
Forms - 100
Reports - 100

If you haven't done so already, run - don't walk - to the Database Splitter
Utility. Whether your data is stored in an Access .mdb/.accdb file, or
SQL/Server, or MySQL, or some other data repository, the data should be in a
shared backend and each user should have their own individual copy of a
frontend (usually a .mde or .accde "compiled" database).

There are lots of resources available for how to effectively use and manage a
split database, post back if you need some pointers other than:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

MVP Tony Toews "That's TAVES dammit" Access page:
http://www.granite.ab.ca/access

 

[Albert D. Kallal]

You don't mention how many rows are in your tables.....

I have an application with 160 forms, 30,000 lines of vba, and 60 VERY
highly related tables.

with 10 users on the system, and with most small tables have only about
150,000 records in them, the application absolute screams in terms of
performance and response. Some tables does have about 500,000 records in
them, but again that is very tiny table in ms-access.

So, you are no where even REMOTE close to hitting any kind of limits here.

You can scale to 1000's users if you move your data to sql serer, but
CONTINUE to use ms-access.

However, moving to sql server will not magic make your application go
faster.

1. Is there a way to still use the front end development tools of access,
but get a better database, that might be quicker and allow for more users.

Yes, in fact ms-access is NOT a database. You would not ask is c++, vb.net,
or is Delphi a database? The same thing goes for ms-access. When you use
c++ or ms-access with oracle for the database part the performance is the
SAME IN BOTH CASES for retrieving data.

Ms-access works just fine when the database is oracle, MySql, or sql-server.
Ms-access is NOT the database engine. The database engine you are using is
called JET. However, you don't have to use the JET database engine for your
datastore.

The first thing you need to do is split your database. I explain this
concept and spell out that ms-access IS NOT a database. You get to CHOOSE
what database you use with ms-access.

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

2. Eventually we would need this on the internet, so that would be a
future
requirement.

When you say internet, do you mean for your internal users having remote
use, or for your customers to enter data? For remote use, read my article on
WANS to explain some possible solutions here:

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