A97 to SQL?

[Leslie Isaacs]

Hello All

I have a split, FE/BE A97 mdb, that I have been 'developing' for about 8
years now: and it works great!!
As it has grown, more people are using it: typically 4 or 5 people are now
using it at a time (from seperate workstations, each with seperate
frontends).
Next year this is likely to grow to 6, 7 or 8 concurrent users.
Four of the BE tables now have ~500,000 records.
I am starting to wonder whether the time has come to move to something
bigger, like SQL server.
Apart from the cost/time involved in the converstion, I am also concerned
about losing the ability to 'develop' the application myself. Currently I am
regularly adding little enhancements, new reports, etc etc, to the A97 mdb
FE, and it's very useful that I can do this. I have developed a reasonable
level of expertise in access (well A97 anyway) in this way.
BUT: I know nothing about SQL server, and am unlikely to be able to find the
time to get into it.

So, my questions:

1) Am I right to suppose that sooner or later my A97 mdb is not going to be
up to the job
2) Is it possible to have an SQL server backend linked to a A97 (or later)
frontend - and so have the advantages of big-time backend (e.g.robust
multi-user capability) while still being able to keep refining the frontend
myself)
3) What other (affordable!) options do I have?

Hope someone can help.
Thanks
Les

 

[Jeff Boyce]

Congratulations on your success, Leslie!

You asked for options, so ...

You could migrate your data to a SQL-Server back-end, and link to your data
there. Unfortunately, some of the easy ways Access offers for connection to
Access/JET data back-ends could be ... troublesome. You might have to
redevelop portions of your front-end to work better with SQL-Server data.
This does let you keep your front-end and continue your refinements in
Access.

Note that SQL-Server does not offer a user-interface. You will need to have
some kind of front-end for the users to work with the data. That's true
now, using Access/JET for the back-end.

Are you/your users experiencing performance hits with the current setup?
Are all of your current (and future) users doing data entry (more demand),
or are some looking up info (less demand)? Is everyone hitting the
database/application simultaneously or is use spread out over the day? Are
your tables well-normalized? Well-indexed? What kind of performance are
you seeing now?

Are your forms set up to work with a single record at a time (rather than
loading all records in the table)?

How fast is your network? Is the Access/JET back-end on the same LAN that
your SQL-Server would be? Do you have a SQL-Server/DBA guru in-house?

Just a few questions/considerations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DAVID]

We have an application that supports either a
JET/MDB backend, or a SQL Server BE.

SQL Server is only marginally slower, not enough
so that it makes a difference.

Jet 4.0 /DAO 3.6/ Access 2K transactions are
broken against SQL Server. A97 not so bad,
but haven't tried it against SQL Server 2005

The A97 upgrade wizard is broken by SQL Server
version numbers. It can be fixed, or you can
use a different method of importing your tables
into SQL Server.

 

[Leslie Isaacs]

Hello Jeff

Thanks for your reply.
I am very encouraged to learn that - if I've understood you correctly - I
could have the best of both worlds (access FE with SQL BE).

All the way through this, though, I keep asking myself "Why bother?" -
based on the 'if it ain't bust don't fix it' principle. It's not bust ...
yet ... and performance isn't at all bad ... usually (some queries take
30-60 seconds to run, but these are rarely used anyway), but: I'd rather not
wait until it's bust before I do anything about it. I'm also very open to
anyone pointing out good reasons to convert.

In answer to your questions:
All users are entering data - no-one just reads it.
With regards to whether or not multiple users are 'hitting the database'
simultaneously, I'm not sure: presumably this depends on whether they are
hitting the same table/s, and also on how long the 'hit' lasts. It is
unlikely that any 2 users will ever do anything (like run a query, open a
form) at exactly the same time. Therefore their 'hits' are not concurrent
.... ? I'm guessing this will be hard/impossible to fathom - and that only
trial and error will be useful.
Tables - say ~85% normalised, and well indexed.
Current performance: usually quite aceptable. The main activity involves
running a pretty complex module, and this typically takes ~15 seconds to run
That's acceptable to us (though of course 5 seconds would be much better!).
I really don't know wheether the forms are set up to work with a single
record at a time. How would I check this?
Ditto on how fast is the network is ... and again how would I check this?
The access/JET back-end is on the same LAN that your SQL-Server would be. Is
there a reason to change this?
Regrettably, we do not have a SQL-Server/DBA guru in-house. I'm the nearest
we have!

Given the above, my remaining questions are:
Whether or not to 'do it'
What size of job would this be for a competent SQL person?

If you have any further info or answers to any of the above I would be very
grateful.
Thanks again
Les

 

[Leslie Isaacs]

Hello David

I assume that you represent a company that sells something that you think
would be useful to me. If that's correct, please could you let me have your
email address or website so that I can contact you ofline.

Thanks
Les

 

[Jeff Boyce]

Leslie

(see comments in-line below)

Hello Jeff

Thanks for your reply.
I am very encouraged to learn that - if I've understood you correctly - I
could have the best of both worlds (access FE with SQL BE).
Yes


All the way through this, though, I keep asking myself "Why bother?" -
based on the 'if it ain't bust don't fix it' principle. It's not bust ...
yet ... and performance isn't at all bad ... usually (some queries take
30-60 seconds to run, but these are rarely used anyway), but: I'd rather
not wait until it's bust before I do anything about it. I'm also very open
to anyone pointing out good reasons to convert.

In answer to your questions:
All users are entering data - no-one just reads it.
With regards to whether or not multiple users are 'hitting the database'
simultaneously, I'm not sure: presumably this depends on whether they are
hitting the same table/s, and also on how long the 'hit' lasts. It is
unlikely that any 2 users will ever do anything (like run a query, open a
form) at exactly the same time. Therefore their 'hits' are not concurrent
... ? I'm guessing this will be hard/impossible to fathom - and that only
trial and error will be useful.

Tables - say ~85% normalised, and well indexed.

Current performance: usually quite aceptable. The main activity involves
running a pretty complex module, and this typically takes ~15 seconds to
run That's acceptable to us (though of course 5 seconds would be much
better!).

This is something you could experiment with, and may be vastly improved if
your underlying tables have indexes on the fields used either to sort, to
select, or to join in the queries. I've also gotten improved performance by
breaking a very complex, multi-table query into individual steps (queries
"chained on" queries "chained on" ...).

I really don't know wheether the forms are set up to work with a single
record at a time. How would I check this?

What is the record source underlying the form(s)? If it is a query (or a
SQL statement) that returns all rows (or a lot of rows), the form must be
"filled" before display. If the underlying query is based on a selection
criterion (for example, on the value selected in a combo box on the form),
and returns only a single record when you run the query, well ...

Ditto on how fast is the network is ... and again how would I check this?

Ask your DESKTOP or NETWORK guru the speed of the LAN, the speed of the
Network Interface Cards (NICs) in the PCs, whether the NICs are set to
full-duplex, ... and ask them what they might change to get faster network
performance for your PCs.

The access/JET back-end is on the same LAN that your SQL-Server would be.
Is there a reason to change this?
Regrettably, we do not have a SQL-Server/DBA guru in-house. I'm the
nearest we have!

While it is possible to teach yourself enough about SQL-Server to set up
back-end data files, there are a LOT of other factors. You will need to
devote resources (your time, perhaps some texts and classes, and/or an
on-call SQL DBA) to do the fine tuning. You might be able to get it set up
(without any fine tuning), but I'd sure want some backup! What happens to
your users if the "beer truck" gets you and you're the part-time SQL guru?!

Given the above, my remaining questions are:
Whether or not to 'do it'

Given the resources (my time, my interest in learning new topics/skills,
....), working on this would be a challenge and (for me) fun, but I wouldn't
give up my day job. NOTE -- this was about ME -- you'll have to consider
your approach.

What size of job would this be for a competent SQL person?

This is NOT a job that a SQL person would be able to do on his/her own. You
will be intimately involved. The SQL specialist could help you with things
like permissions and indexing in SQL and ... You will be helping the SQL
person with things like table structure (perhaps you can persuade him/her to
give you create permissions and show you how to use the Data Transform
Services to pull in copies of your existing tables ... and they won't be
'done', just started).

If you have any further info or answers to any of the above I would be
very grateful.
Thanks again
Les

Regards, and good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[Leslie Isaacs]

Jeff

Many thanks for your comprehensive info.
I think this is something that I need to do, but not in a hurry (it's not
critical yet so I have that luxury!). Perhaps I'll run a 'virtual' version
to see how it looks and what perfoprmance gains I get. But really it's the
multi-user capability that I want to ensure.
First step is to find myself an SQL person: better get the phone book out!

Cheers
Les

 

[Jeff Boyce]

Les

Best of luck.

Now, at the risk of calling down flames on myself, I'll mention that (some
of) the folks who frequent/support SQL-Server have shown a, shall we say,
less than "inclusive" view of MS Access. Some have actually been known to
call it a "toy" database (and not a "real" database, like SQL-Server).

If you find someone who can lend a hand on the SQL-Server side AND is
amenable to working with you in your use of Access, you are ahead of the
game!

<DUCK!>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jack Isaacs]

Jeff
Message received and understood: thanks for the tip!
Don't suppose you'd be interested?
Les

 

[DAVID]

Sorry for the misunderstanding.

Yes, it is, with the notes I mentioned:

 

[Tom Wimpernark]

no, now is not the time to move to SQL Server

the time to move to SQL Server is when you have 1,000 (one thousand) records
in a single table

 

[Tom Wimpernark]

SQL Server _DOES_ have an interface, it is called 'Access Data Projects'

 

[Tom Wimpernark]

some queries take 30-60 seconds to run? yeah move to Access Data Projects
and properly index it

 

[Tom Wimpernark]

Access MDB _IS_ a toy database, I mean-- come on