Relink tables?

[Leslie Isaacs]

Hello All

I have a very complex, A2K frontend/backend split mdb, which is often now
used by 7 users at the same time. The backend sits on a server machine
running Windows2K Server, and is approaching 1Gb. I know that sooner or
later I should move to SQLserver, but do not have the expertise to do this
myself and cannot afford to buy it in yet (hopefully in a year or so!).
Until I can move to SQLServer, I was wondering whether a solution might be
to split the backend in half (or even into thirds), and get the users then
to relink the tables of their frontend to whichever backend they need (from
the actual usage point of view of the users this would be perfectly
feasible, and would probably only need to be done once - at the start of
each day). That way the 7 users would be very unlikely all to be using the
same backend at the same time, and each backend would obviously be half (or
third) of the size of the original backend.

Is there any reason why this idea would not be a good one? Obviously another
idea would be simply to have 2 (or 3) separate frontends which link to the 2
(or 3) cutdown backends, but that way I would have to maintain 2 (or 3)
frontends and as I am regularly required to make amendments to the frontend
this would be tedious and carry the risk of the frontends becoming
inconsistent with each other.

Hope someone can help.

Many thanks
Leslie Isaacs

 

[Douglas J. Steele]

Allowing the users to change the backend to which they're linked isn't an
unreasonable thing.

There's sample code to do this at
http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web", or you
can get the free J Street Access Relinker at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

 

[Steve]

Perhaps all you need to do is compact the backend. Open the backend and go
to Tools - Compact and Repair. Have you tried that?

Steve
(e-mail address removed)

 

[John Spencer]

First thing is have you ever run compact on the backend? If not, make a
backup copy and then try compacting the backend and see if that
decreases the size significantly.

You can split the backend into multiple sections with some tables in one
backend and other tables in another backend. The problem with this is
you can see some performance degradation and you cannot enforce
relational integrity between tables in one backend and tables in another
backend. You can program around that, but it is a headache to handle.

If your data is such that the users can work with separate backends then
you can do that.

Moving to SQL Server is not too difficult. In most cases, moving the
backend to SQL Server database and linking to the tables via ODBC and a
DSN or DSN-less connection will work. Sometimes there are problems that
will cause a need to redesign some forms. And performance can suffer a
bit in some cases.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Leslie Isaacs]

Hello Douglas

Many thanks for your reply: it's good to know that there is no fundamental
reason not to have users relink the tables.

I will have at look at the links you have given, and will probably use one
of them.

Thanks again
Les

 

[Leslie Isaacs]

Hello Steve

Many thanks for your reply.

I do compact/repair the mdb regularly.

Apart from its size, it's the number of simultaneous users issue that I
think I'd like to tackle by splitting the backend. I seem to remember
reading somewhere that access may have problems with more than 6 or 7: is
that right?

Thanks again
Les

 

[Leslie Isaacs]

Hello John

Many thanks for your reply.

I do compact/repair the mdb regularly.

Apart from its size, it's the number of simultaneous users issue that I
think I'd like to tackle by splitting the backend. I seem to remember
reading somewhere that access may have problems with more than 6 or 7: is
that right? The way I was thinking of splitting the backend, each frontend
would only have linked tables from one backend: sort of, data for clients A
to M in backend 1 and data for clients N to Z in backend 2.

I am mystified that you say "Moving to SQL Server is not too difficult.", as
I have had quotes for serveral thousand £GBP for the job at least one of
which was from a very reputable company! The frontend is very complex (large
numbers of forms, reports and queries, and tons of code). I hesitate to say
this on this newsgroup (!), but if you or anyone you know believes it may be
a simple matter that would cost £hundreds instead of £thousands, and would
be interested in the job, please let me know.

Thanks again
Les

 

[John Spencer]

Depending on the design of your database, Access with JET (the native
database engine for Access) can often support a few dozen users with
little problem and degradation of performance. It depends on what the
users are doing - entering new data, changing existing data, or just
accessing the information for reports and information.

Also, good design does not load all the records in a table. You filter
the records so tat you only access a few records at one time. For
instance, in one of my databases I load one patient in a main form and
use subforms to load only the records associated with that patient
(visits, caregivers, etc).

I have moved medium level complexity databases from JET to SQL Server in
less than a day. The data structure and data was transferred from JET
to SQL server using the built-in tools in Access - upsizing wizard.

Then I linked to the SQL Server tables and tested. In most cases, I did
not have to make any further changes to the front-end to have a working
application. I did find that the performance could be enhanced by using
some of the features of SQL server.

I wouldn't undertake this job at a distance because I can only get the
SQL server set up correctly (security, backups, etc) by being on site.
That is probably due to my limited ability working with SQL server.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Tony Toews [MVP]]

Apart from its size, it's the number of simultaneous users issue that I
think I'd like to tackle by splitting the backend. I seem to remember
reading somewhere that access may have problems with more than 6 or 7: is
that right?

Not at all. I've got clients with 25 users in the back end all the
time. Granted half of those are generally doing lookups and reports
but the other half are doing heavy duty data entry for hours at a
time.

Tony

 

[Tony Toews [MVP]]

The way I was thinking of splitting the backend, each frontend
would only have linked tables from one backend: sort of, data for clients A
to M in backend 1 and data for clients N to Z in backend 2.

That's going to be a huge pain in the uhh heck for the users. It
could take 10 or 20 seconds to relink the tables to the other back
end.

And what happens with reports where you need to use both sets of data?

I am mystified that you say "Moving to SQL Server is not too difficult.", as
I have had quotes for serveral thousand £GBP for the job at least one of
which was from a very reputable company! The frontend is very complex (large
numbers of forms, reports and queries, and tons of code). I hesitate to say
this on this newsgroup (!), but if you or anyone you know believes it may be
a simple matter that would cost £hundreds instead of £thousands, and would
be interested in the job, please let me know.

Well, that's reasonable enough. A thousand or two thousand pounds
would be a weeks worth of consulting work. Just guessing. So sure it
could easily take a week or two or three to move a system over to SQL
Server and remove the initial bottlenecks.

Tony