Re: A97 to SQL?
- From: "Tom Wimpernark" <Tom_Wimpernark@xxxxxxxxxxx>
- Date: Mon, 18 Jun 2007 11:48:41 -0700
some queries take 30-60 seconds to run? yeah move to Access Data Projects and properly index it :)
"Leslie Isaacs" <leslie.isaacs@xxxxxxxxxxxxxxxx> wrote in message news:OIXMsW9kHHA.1388@xxxxxxxxxxxxxxxxxxxxxxx
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
"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message news:eu7ZpklkHHA.4624@xxxxxxxxxxxxxxxxxxxxxxxCongratulations 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
"Leslie Isaacs" <leslie.isaacs@xxxxxxxxxxxxxxxx> wrote in message news:OMeuIUkkHHA.5024@xxxxxxxxxxxxxxxxxxxxxxxHello 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
.
- Prev by Date: Re: A97 to SQL?
- Next by Date: Re: A97 to SQL?
- Previous by thread: Re: A97 to SQL?
- Next by thread: Re: A97 to SQL?
- Index(es):
Relevant Pages
|
