Dear experts,



This year I've been challenging myself to break out of old habits, and 

revisit some fundamental database concepts that have always been 

suggested, but that I just never bothered with because I was just too lazy 

or too comfortable with the way I already did things.



One of the fundamental ideas I'm playing with now is the Front-End/Back-

End database concept.  



I've got a great big Access '97 database, chock full of queries (2/3 of 

which should just be obliterated), forms, reports, and tons of code.  

Until today, it just sat on the local server, accessed by multiple users 

every day and processing with the agility of a slug.



A coworker of mine was taking an Access 2000 class last week, and while he 

was training offsite, we traded some email messages.  He said the 

instructor swore that creating a back-end/front-end database would 

actually speed things up. 



I balked at this, because it seems that every time I've used linked tables 

in other scenarios, the databases with the links seemed to run even *s-l-o-

w-e-r*.  But then.... I've been using linked tables from databases that 

were getting multiple hits....so that could have been part of my problem.  



I'm certainly willing to change my ways!  So today I took my Great Big 

Access '97 database and used the Database Splitter on it.  Now I've got 

some questions:



* My front-end and back-end are still both on the local server.  Will it 

REALLY reduce network traffic and speed things up if the same multiple 

users are all using the same front-end, sitting on the same server where 

the original entire database was?  It seems no different than the way it 

was before, except that all the tables are now linked, instead of static.



* Why did the Database Splitter tell me I could continue to make design 

changes to my front-end without interrupting data population?  It's got me 

very confused.  I was making changes to forms without interrupting data 

population before I split it.  Just went into Form Design, and made 

changes.  At least -- I THOUGHT I wasn't interrupting data population!



* If the front-end with linked tables IS faster, why is that?  Is it 

because... linked tables are only in use when they are opened up in a 

form, similar to the way a table or query is only open in code when a 

recordset is created, then freed up when the recordset is closed?  Whereas 

when it was just a database with static tables, once the database was open 

it was the same as putting all the tables in use?  (This is just a theory.)



* Now that I have a front-end and back-end....which one of these things is 

supposed to get compacted and repaired?



* That's another question...compacting and repairing.  Should one compact 

and repair, or repair and compact?  I've read that repairing actually 

makes a database BIGGER, which led me to think that I should repair first, 

then compact.  But I've learned never to believe anything I think.



* The next step I will take will be to convert this new '97 front-end to 

Access 2000, thereby having two front-ends, one for each type of Office 

user (Giantdb97.mdb and Giantdb2000.mdb).  Running the Access 2000 convert 

on Giantdb97.mdb won't "hurt" the tables now that they're linked (from 

Giantdb97_be.mdb) instead of static, will it?



I guess that's all for now!  I am looking forward to your very 

enlightening responses :).



Susan

PS to my previous post:



After reading through some archive files, I have a feeling many of you 

will tell me to put these front-end versions on everyone's C drive.



I just have this to say about that:  I HATE that idea!!  It genuinely 

gives me the willies.  We've got well over 150 people I'd have to 

distribute the database to (not that many people actually use it, but you 

do have to give it to all appropriate parties). 



Even though the front-end for those users wouldn't change too much, even a 

couple of changes every couple of months would be a hassle.  You don't 

know my users.  They don't read emails about updates, or things that they 

have to download and replace.  I know because I have other little things I 

develop that get changed from time to time (an Outlook Email Form, for 

example), and even though I *email the form to the users* AND provide them 

with the intranet URL address where a fresh copy always resides for their 

use -- they delete my email without reading it, profess ignorance that we 

actually have an intranet, and invariably call me up and complain about 

some feature that isn't on their version.  To which I can only say, "Uh... 

did you get my email 3 weeks ago?" And they reply, "Oh.  I guess I deleted 

that." 



*PLEASE* don't tell me to distribute the front-end to these people!!



Browbeaten on a daily basis,

Susan

Susan,



You need to distribute the front-end to your users!





Sorry, couldn't resist.



Seriously, could you not build this as an intranet application? We've solved

many distribuition problems this way, and it runs so much faster too.





Regards,



Craig.



-----Original Message-----

From: Susan Henesy [mailto:susan.henesy@w...]

Sent: Tuesday 28 August 2001 11:33

To: Access

Subject: [access] Re: Do Front-End/Back-End Databases REALLY speed

things up?





PS to my previous post:



After reading through some archive files, I have a feeling many of you

will tell me to put these front-end versions on everyone's C drive.



I just have this to say about that:  I HATE that idea!!  It genuinely

gives me the willies.  We've got well over 150 people I'd have to

distribute the database to (not that many people actually use it, but you

do have to give it to all appropriate parties).



Even though the front-end for those users wouldn't change too much, even a

couple of changes every couple of months would be a hassle.  You don't

know my users.  They don't read emails about updates, or things that they

have to download and replace.  I know because I have other little things I

develop that get changed from time to time (an Outlook Email Form, for

example), and even though I *email the form to the users* AND provide them

with the intranet URL address where a fresh copy always resides for their

use -- they delete my email without reading it, profess ignorance that we

actually have an intranet, and invariably call me up and complain about

some feature that isn't on their version.  To which I can only say, "Uh...

did you get my email 3 weeks ago?" And they reply, "Oh.  I guess I deleted

that."



*PLEASE* don't tell me to distribute the front-end to these people!!



Browbeaten on a daily basis,

Susan

> Sorry, couldn't resist.

> 

> Seriously, could you not build this as an intranet application? We've 

solved

> many distribuition problems this way, and it runs so much faster too.

> 

> 

> Regards,

> 

> Craig.





Hi Craig,



I do have some databases up on ASP, but not this database.  It took this 

database 4 years to become the heavily form and report laden thing that it 

is.  Plus a couple of the forms use mail merge documents and calls to MS 

Word and stuff, and... that might be difficult to recreate in ASP, 

wouldn't it?    I don't know; this thing is so big it should be its own 

Republic; reubilding it from scratch doesn't seem to be worth the 

time/effort, especially what with trying to juggle all the things that 

have yet to be built.



Please don't tell me to rebuild this database!  ACK!

It's fairly easy to re-create on an Intranet. Any queries already build can

be re-produced, but there are some drawbacks, one being the interaction with

other applications, and the Report functions aren't nearly as customisable

as Access97 allows you.



You'd need to seriously think about it before migrating if it's as big as

you say!!



We had a very large Access database which was split front end/backend.

Before it was just one file on a network server, but each time you open a

form, you're dragging a lot of data down the network compared to a split

database.



There isn't a easy solution to this one, apart from taking it to Thin Client

such as an intranet.











-----Original Message-----

From: Susan Henesy [mailto:susan.henesy@w...]

Sent: Tuesday 28 August 2001 11:51

To: Access

Subject: [access] Re: Do Front-End/Back-End Databases REALLY speed

things up?







> Sorry, couldn't resist.

>

> Seriously, could you not build this as an intranet application? We've

solved

> many distribuition problems this way, and it runs so much faster too.

>

>

> Regards,

>

> Craig.





Hi Craig,



I do have some databases up on ASP, but not this database.  It took this

database 4 years to become the heavily form and report laden thing that it

is.  Plus a couple of the forms use mail merge documents and calls to MS

Word and stuff, and... that might be difficult to recreate in ASP,

wouldn't it?    I don't know; this thing is so big it should be its own

Republic; reubilding it from scratch doesn't seem to be worth the

time/effort, especially what with trying to juggle all the things that

have yet to be built.



Please don't tell me to rebuild this database!  ACK!

Hi Susan



Got to agree with Craig on this - you won't see any performance 

improvement unless you distribute the front end to your users.



The decrease in network traffic when you split the database comes mainly 

from Access not having to request data from the file server whenever it 

needs to update a form \ report or display a dialog box. So, if you're 

going to keep these on the server you won't get any improvement. 



As Access drags whole tables across to your PC whenever you open a bound 

form or run a query, wherever the database is held, you won't see any 

improvement from a data movement point of view.



To control your users ;-) you'll need to implement some form of version 

checking into your databases. At it's simplest, put a version table in 

your data DB and one in your front end. If the latest entry in these two 

tables do not match then prevent the user (in a nice way) from opening 

your database. With a bit more work you have the new version automatically 

install itself when the user tries to open the database.



Before you do anything though, run a few tests to see if it's worth while. 

If your database is query intensive, always dragging massive tables across 

the network to work on, you probably won't see any improvement anyway and 

might as well leave it alone.



-Brian



PS You can run the Access 2000 upgrader on your new front end without 

worrying, it won't touch the tables now they are in a seperate database.



PPS Access 2000 DOES require that you have exclusive access to 

forms\reports\etc. before it will allow you to make changes to them. So 

now you've split your DB, you will be able to work with your own offline 

copy, and then slip the new version in when no-one is using it.



> PS to my previous post:

> 

> After reading through some archive files, I have a feeling many of you 

> will tell me to put these front-end versions on everyone's C drive.

> 

> I just have this to say about that:  I HATE that idea!!  It genuinely 

> gives me the willies.  We've got well over 150 people I'd have to 

> distribute the database to (not that many people actually use it, but 

you 

> do have to give it to all appropriate parties). 

> 

> Even though the front-end for those users wouldn't change too much, even 

a 

> couple of changes every couple of months would be a hassle.  You don't 

> know my users.  They don't read emails about updates, or things that 

they 

> have to download and replace.  I know because I have other little things 

I 

> develop that get changed from time to time (an Outlook Email Form, for 

> example), and even though I *email the form to the users* AND provide 

them 

> with the intranet URL address where a fresh copy always resides for 

their 

> use -- they delete my email without reading it, profess ignorance that 

we 

> actually have an intranet, and invariably call me up and complain about 

> some feature that isn't on their version.  To which I can only 

say, "Uh... 

> did you get my email 3 weeks ago?" And they reply, "Oh.  I guess I 

deleted 

> that." 

> 

> *PLEASE* don't tell me to distribute the front-end to these people!!

> 

> Browbeaten on a daily basis,

> Susan

> 

> It's fairly easy to re-create on an Intranet. Any queries already build 

can

> be re-produced, but there are some drawbacks, one being the interaction 

with

> other applications, and the Report functions aren't nearly as 

customisable

> as Access97 allows you.



Oh now...you see right there, this database is not a good candidate for 

ASP.  It relies on interaction with other Office applications, and all the 

forms are heavily coded with VBA, as are all the reports.  And there's 

LOTS of those.  So, I see no good reason to redevelop this for the 

intranet... unless....well!  I suppose I could take my time and develop 

just a couple of the most important forms on ASP.  They do require, 

though, that an *.rtf or *.doc form be created and written to people's C 

drives; then that document feeds Word mail merge documents (this was much 

easier than using Access as the source data for Word -- takes FOREVER to 

hook those two up). 



The front-end could then be distributed with very little changes, because 

most of the other stuff is just read-only forms and graphically intense 

reports.



But that's the very very long term solution.  Can I just have the front-

end sit on the network drive, as it is doing now?



Susan

Brian,



Excellent suggestions!  I very much like the whole version control idea; 

and I was hoping I could somehow program it to automatically install a 

newer version, nice to know it's possible.



I have put a "Susans97.mdb" on my own PC for the sake of testing, and will 

see if I reap significant benefits.  If so, I'll look into the 

distribution idea.  I'm resistant to it, but if the performance improves 

signifcantly and I can control the versions, I think it's very much worth 

it!



I'm very much indebted to both you and Craig for your feedback!  



Susan

Oh, boy.



If more than 5% of your users actually do anything  - data insertions, for

example, at one time, you are looking at trouble - you'll be pushing the

Access envelope. How big is this database anyway? If you already have that

many simultaneous users without problems, God bless you - you've been lucky.

I don't want to start a religious war on this - others may disagree, but my

$.02 is more than 7/8 users simultaneous users and/or larger than 50 MB

database is too much to trust Access with.



I think eventually you >will< be looking at splitting off the the front end

onto the workstations. Maybe you'll be dragged to it kicking and screaming,

but it'll probably come to that. Possibly a re-write to SQL Server back end

as well. However, I think there may well be some third party tools to

automate rolling out new front ends if you don't want to take on versioning

control yourself - have a look around. One place to look is FMS.



BTW, I really sympathize with the problem of users not reading instructions,

email, etc. The one that gets me the most is users who automatically close

any and all message boxes without even reading them, then come whining that

the database 'doesn't work'! Probably the message box was telling them they

were trying to enter text into date field or something like that. :-(.



Peter









> -----Original Message-----

> From: Susan Henesy [mailto:susan.henesy@w...]

> Sent: August 28, 2001 11:33 AM

> To: Access

> Subject: [access] Re: Do Front-End/Back-End Databases REALLY speed

> things up?

>

>

> PS to my previous post:

>

> After reading through some archive files, I have a feeling many of you

> will tell me to put these front-end versions on everyone's C drive.

>

> I just have this to say about that:  I HATE that idea!!  It genuinely

> gives me the willies.  We've got well over 150 people I'd have to

> distribute the database to (not that many people actually use it, but you

> do have to give it to all appropriate parties).

>

You can have the front end sit on the network server, but as Brian was

explaining - no performance gains will be awarded. If you're okay with this, then that's fine.



Our Intranet offers the users of an application on there to view/run 

reports from the system and either view it on the screen, or download as 

an Excel File, or straight into word. Neither the Word or Excel file 

existed right up until the user needed it. It was created on the fly.



Users can select that they wish to mail merge. From this, then get asked 

for a letter template, or they cut+paste the letter content, select the 

list of users, and a word letter is built and downloaded to the PC. Very 

fast, and available to over 400 users without a problem.



But it took several months to convert it!





The Internet/Intranet technologies are removing boundaries every day, and 

we never thought it possible to offer more functionality before we 

completed it.











-----Original Message-----

From: Susan Henesy [mailto:susan.henesy@w...]

Sent: Tuesday 28 August 2001 12:21

To: Access

Subject: [access] Re: Do Front-End/Back-End Databases REALLY speed

things up?





> It's fairly easy to re-create on an Intranet. Any queries already build

can

> be re-produced, but there are some drawbacks, one being the interaction

with

> other applications, and the Report functions aren't nearly as

customisable

> as Access97 allows you.



Oh now...you see right there, this database is not a good candidate for

ASP.  It relies on interaction with other Office applications, and all the

forms are heavily coded with VBA, as are all the reports.  And there's

LOTS of those.  So, I see no good reason to redevelop this for the

intranet... unless....well!  I suppose I could take my time and develop

just a couple of the most important forms on ASP.  They do require,

though, that an *.rtf or *.doc form be created and written to people's C

drives; then that document feeds Word mail merge documents (this was much

easier than using Access as the source data for Word -- takes FOREVER to

hook those two up).



The front-end could then be distributed with very little changes, because

most of the other stuff is just read-only forms and graphically intense

reports.



But that's the very very long term solution.  Can I just have the front-

end sit on the network drive, as it is doing now?



Susan

Hi, Craig!

 

> Our Intranet offers the users of an application on there to view/run 

> reports from the system and either view it on the screen, or download as 

> an Excel File, or straight into word. Neither the Word or Excel file 

> existed right up until the user needed it. It was created on the fly.



Good show -- I've done the Excel version of this, by denoting what type of 

document to spit the ASP page out as in the header.  I very much like 

the "on the fly" part of this method, which allows users to download the 

latest, greatest info.



AND I already have buttons where the underlying queries of a few of my 

reports can be downloaded to Excel -- the Financial Analyst likes this 

option, as he can crunch numbers any way he sees fit.



The reports that are most graphically intense only need to be accessed by 

two people at this point, who could certainly have their own Access front-

end (and need it, anyway, in order to process payments to our external 

contractors).



> Users can select that they wish to mail merge. From this, then get asked 

> for a letter template, or they cut+paste the letter content, select the 

> list of users, and a word letter is built and downloaded to the PC. Very 

> fast, and available to over 400 users without a problem.



Wow, Craig, you've really developed something incredible there!  400 

users?!  Is Access really your back-end?!  (Microsoft White Papers on 

Access instabilities have a way of making my heart go faint, but it's 

worked very well for the two ASP projects I've created so far.)  



I think that my Mail Merge might actually be a piece of cake -- they're 

just Word documents sitting on the network, which talk to each person's C 

drive.



> But it took several months to convert it!



You mean, it took several months to convert it up to a new version of 

Access??

 

> 

> The Internet/Intranet technologies are removing boundaries every day, 

>and 

> we never thought it possible to offer more functionality before we 

> completed it.



Yes, I have to say, I really love creating databases for ASP.  (I guess I 

should say, I love creating NEW databases for ASP ;).  So far it's 

actually proven to be EASIER to write validation via JavaScript and ASP 

than it was to write it in VBA in Access.  I need as many ways to control 

those pesky users as I can get.



Thanks again for all this great advice!

Susan

Hi,



It took a few months to upgrade it from MS Access 97 front end, to a fully

fledged ASP application.



Although many people on this list would argue about the limits of Access,

we've not experienced anything. I know that all 400 users do not hammer the

system at the exact same time, but the speed is very quick and no one has

ever complained about that.



We're moving to SQL Server as the backend shortly as we are merging even

more apps from their desktop equvilents to the Intranet, but Access has been

more than faithful to us.



The backend database is around 100MB with over 68,000 records, so it's not

huge. This may be the reason for the speed.





Regards,

Craig.



-----Original Message-----

From: Susan Henesy [mailto:susan.henesy@w...]

Sent: Tuesday 28 August 2001 12:47

To: Access

Subject: [access] Re: Do Front-End/Back-End Databases REALLY speed

things up?





Hi, Craig!



> Our Intranet offers the users of an application on there to view/run

> reports from the system and either view it on the screen, or download as

> an Excel File, or straight into word. Neither the Word or Excel file

> existed right up until the user needed it. It was created on the fly.



Good show -- I've done the Excel version of this, by denoting what type of

document to spit the ASP page out as in the header.  I very much like

the "on the fly" part of this method, which allows users to download the

latest, greatest info.



AND I already have buttons where the underlying queries of a few of my

reports can be downloaded to Excel -- the Financial Analyst likes this

option, as he can crunch numbers any way he sees fit.



The reports that are most graphically intense only need to be accessed by

two people at this point, who could certainly have their own Access front-

end (and need it, anyway, in order to process payments to our external

contractors).



> Users can select that they wish to mail merge. From this, then get asked

> for a letter template, or they cut+paste the letter content, select the

> list of users, and a word letter is built and downloaded to the PC. Very

> fast, and available to over 400 users without a problem.



Wow, Craig, you've really developed something incredible there!  400

users?!  Is Access really your back-end?!  (Microsoft White Papers on

Access instabilities have a way of making my heart go faint, but it's

worked very well for the two ASP projects I've created so far.)



I think that my Mail Merge might actually be a piece of cake -- they're

just Word documents sitting on the network, which talk to each person's C

drive.



> But it took several months to convert it!



You mean, it took several months to convert it up to a new version of

Access??



>

> The Internet/Intranet technologies are removing boundaries every day,

>and

> we never thought it possible to offer more functionality before we

> completed it.



Yes, I have to say, I really love creating databases for ASP.  (I guess I

should say, I love creating NEW databases for ASP ;).  So far it's

actually proven to be EASIER to write validation via JavaScript and ASP

than it was to write it in VBA in Access.  I need as many ways to control

those pesky users as I can get.



Thanks again for all this great advice!

Susan

In my informal experimentation (dealing w/a poorly performing app converted

from A97 to A2K), performance *decreases* when you split the database--even

if both front- and back-ends are left on the user's machine (not a practical

scenario if you've got more than one user).  Search the help file for

'performance' and you'll find an entry for improving performance of linked

tables.  Perf continues to decrease as you move the files out to network

servers.



IMHO the main reason you split .mdb files is convenience in rolling out

updates, not speed.  The actual number of bytes devoted to app elements (as

opposed to actual table data) in a unified .mdb file has been next to

negligible in the apps I've done.  But I greatly value being able to

entirely blow away a front-end file with a new version without having to

worry about losing my users 2 weeks worth of data entry and maintenance.



(To my way of thinking, it's actually sort of weird that Access combines app

stuff with data in the first place--what other database/app development

product that does that?)



You can mitigate the perf hit though, by putting the front-end on the user's

machine, and setting them up with a shortcut that opens the FE exclusively.

This way you're not pulling the UI element data over the network (tho again,

probably a teeny amount of data compared to the tables) and you're also not

incurring the hit of having Access try to do record locking on the front-end

file (which is unnecessary since it's only the one user in the db) as well

as the back-end file.



I definitely take the point about it being a pain in the tuchus to get users

to update.  I've seen several articles in magazines (most recently

Access*VB*SQL Advisor) that present schemes for checking versions &

automating updates.  For my stuff, putting a shortcut on the user's desktop

labeled "Refresh <<app name>>" and hitting them with e-mailed requests to

refresh when they get a chance (which are mostly replies to their change

requests/bug reports) has worked out well.  One of my collegues here

manually connects to his users' hard drives through the admin share &

overwrites the files for the each one, tho he said it was a pain to remember

who didn't get updated b/c they were in the db at the time he wanted to

update.



So... If what you're doing is working for you, I say stick with it.  You

might play around with using an exclusively-opened local front-end file on

your own machine, and see if you can tell a difference.  If you can't, no

doubt your users won't either.



As for your other Qs--compact & repair is one operation in A2K, so you don't

have a choice.  I don't think the order matters in A97.  It's most important

to compact the BE, though the FE will sometimes benefit from compacting

also.



Cheers,



-Roy





' Dear experts,

' 

' This year I've been challenging myself to break out of old 

' habits, and revisit some fundamental database concepts that have 

' always been suggested, but that I just never bothered with 

' because I was just too lazy or too comfortable with the way I 

' already did things.

' 

' One of the fundamental ideas I'm playing with now is the Front-

' End/Back- End database concept.  

' 

' I've got a great big Access '97 database, chock full of queries 

' (2/3 of which should just be obliterated), forms, reports, and 

' tons of code.  Until today, it just sat on the local server, 

' accessed by multiple users every day and processing with the 

' agility of a slug.

' 

' A coworker of mine was taking an Access 2000 class last week, and 

' while he was training offsite, we traded some email messages.  He 

' said the instructor swore that creating a back-end/front-end 

' database would actually speed things up. 

' 

' I balked at this, because it seems that every time I've used 

' linked tables in other scenarios, the databases with the links 

' seemed to run even *s-l-o- w-e-r*.  But then.... I've been using 

' linked tables from databases that were getting multiple 

' hits....so that could have been part of my problem.  

' 

' I'm certainly willing to change my ways!  So today I took my 

' Great Big Access '97 database and used the Database Splitter on 

' it.  Now I've got some questions:

' 

' * My front-end and back-end are still both on the local server. 

' Will it REALLY reduce network traffic and speed things up if the 

' same multiple users are all using the same front-end, sitting on 

' the same server where the original entire database was?  It seems 

' no different than the way it was before, except that all the 

' tables are now linked, instead of static.

' 

' * Why did the Database Splitter tell me I could continue to make 

' design changes to my front-end without interrupting data 

' population?  It's got me very confused.  I was making changes to 

' forms without interrupting data population before I split it. 

' Just went into Form Design, and made changes.  At least -- I 

' THOUGHT I wasn't interrupting data population!

' 

' * If the front-end with linked tables IS faster, why is that?  Is 

' it because... linked tables are only in use when they are opened 

' up in a form, similar to the way a table or query is only open in 

' code when a recordset is created, then freed up when the 

' recordset is closed?  Whereas when it was just a database with 

' static tables, once the database was open it was the same as 

' putting all the tables in use?  (This is just a theory.)

' 

' * Now that I have a front-end and back-end....which one of these 

' things is supposed to get compacted and repaired?

' 

' * That's another question...compacting and repairing.  Should one 

' compact and repair, or repair and compact?  I've read that 

' repairing actually makes a database BIGGER, which led me to think 

' that I should repair first, then compact.  But I've learned never 

' to believe anything I think.

' 

' * The next step I will take will be to convert this new '97 

' front-end to Access 2000, thereby having two front-ends, one for 

' each type of Office user (Giantdb97.mdb and Giantdb2000.mdb). 

' Running the Access 2000 convert on Giantdb97.mdb won't "hurt" the 

' tables now that they're linked (from Giantdb97_be.mdb) instead of 

' static, will it?

' 

' I guess that's all for now!  I am looking forward to your very 

' enlightening responses :).

' 

' Susan

' 

' 

' 

' 

' 

' 

' --- When you work at Microsoft, you don't have to wait for the 

' latest technologies to be released. You'll create them ? with 

' XML, SOAP, and C# -- and help decide what happens next. Find out 

' where your career can take you: take a look at our software 

' design engineer opportunities at http://www.microsoft.com/jobs. -

' --

' 



-----Original Message-----

From: Susan Henesy [mailto:susan.henesy@w...]

Sent: Tuesday, August 28, 2001 3:28 AM

To: Access

Subject: [access] Do Front-End/Back-End Databases REALLY speed things

up?

My experience differs from Roy in that splitting the database into

front-end/back-end has consistently improved performance.  Of course the

front-end is on the users' machine, the data is typically on a server.  I

have

one application with almost 900 users running against 14 distributed Access

97

back-end databases.  I works very well, however, these are not 900

concurrent

users.  

Upgrading the application is controlled by the application itself in that a

configuration table is on the server indicating the current version, the

value

is compared against the local (front-end) version id, if different, then it

fires off an upgrade routine.  The user is advised, but not allowed to

intervene.  Results are logged and processing resumes.  



HTH



Tom Hamilton

T_Systems, Inc

Database Programmer

(xxx) xxx-xxxx



>>> Pardee, Roy E 08/28/01 08:59AM >>>

In my informal experimentation (dealing w/a poorly performing app converted

from A97 to A2K), performance *decreases* when you split the database--even

if both front- and back-ends are left on the user's machine (not a practical

scenario if you've got more than one user).  Search the help file for

'performance' and you'll find an entry for improving performance of linked

tables.  Perf continues to decrease as you move the files out to network

servers.



IMHO the main reason you split .mdb files is convenience in rolling out

updates, not speed.  The actual number of bytes devoted to app elements (as

opposed to actual table data) in a unified .mdb file has been next to

negligible in the apps I've done.  But I greatly value being able to

entirely blow away a front-end file with a new version without having to

worry about losing my users 2 weeks worth of data entry and maintenance.



(To my way of thinking, it's actually sort of weird that Access combines app

stuff with data in the first place--what other database/app development

product that does that?)



You can mitigate the perf hit though, by putting the front-end on the user's

machine, and setting them up with a shortcut that opens the FE exclusively.

This way you're not pulling the UI element data over the network (tho again,

probably a teeny amount of data compared to the tables) and you're also not

incurring the hit of having Access try to do record locking on the front-end

file (which is unnecessary since it's only the one user in the db) as well

as the back-end file.



I definitely take the point about it being a pain in the tuchus to get users

to update.  I've seen several articles in magazines (most recently

Access*VB*SQL Advisor) that present schemes for checking versions &

automating updates.  For my stuff, putting a shortcut on the user's desktop

labeled "Refresh <<app name>>" and hitting them with e-mailed requests to

refresh when they get a chance (which are mostly replies to their change

requests/bug reports) has worked out well.  One of my collegues here

manually connects to his users' hard drives through the admin share &

overwrites the files for the each one, tho he said it was a pain to remember

who didn't get updated b/c they were in the db at the time he wanted to

update.



So... If what you're doing is working for you, I say stick with it.  You

might play around with using an exclusively-opened local front-end file on

your own machine, and see if you can tell a difference.  If you can't, no

doubt your users won't either.



As for your other Qs--compact & repair is one operation in A2K, so you don't

have a choice.  I don't think the order matters in A97.  It's most important

to compact the BE, though the FE will sometimes benefit from compacting

also.



Cheers,



-Roy





' Dear experts,

' 

' This year I've been challenging myself to break out of old 

' habits, and revisit some fundamental database concepts that have 

' always been suggested, but that I just never bothered with 

' because I was just too lazy or too comfortable with the way I 

' already did things.

' 

' One of the fundamental ideas I'm playing with now is the Front-

' End/Back- End database concept.  

' 

' I've got a great big Access '97 database, chock full of queries 

' (2/3 of which should just be obliterated), forms, reports, and 

' tons of code.  Until today, it just sat on the local server, 

' accessed by multiple users every day and processing with the 

' agility of a slug.

' 

' A coworker of mine was taking an Access 2000 class last week, and 

' while he was training offsite, we traded some email messages.  He 

' said the instructor swore that creating a back-end/front-end 

' database would actually speed things up. 

' 

' I balked at this, because it seems that every time I've used 

' linked tables in other scenarios, the databases with the links 

' seemed to run even *s-l-o- w-e-r*.  But then.... I've been using 

' linked tables from databases that were getting multiple 

' hits....so that could have been part of my problem.  

' 

' I'm certainly willing to change my ways!  So today I took my 

' Great Big Access '97 database and used the Database Splitter on 

' it.  Now I've got some questions:

' 

' * My front-end and back-end are still both on the local server. 

' Will it REALLY reduce network traffic and speed things up if the 

' same multiple users are all using the same front-end, sitting on 

' the same server where the original entire database was?  It seems 

' no different than the way it was before, except that all the 

' tables are now linked, instead of static.

' 

' * Why did the Database Splitter tell me I could continue to make 

' design changes to my front-end without interrupting data 

' population?  It's got me very confused.  I was making changes to 

' forms without interrupting data population before I split it. 

' Just went into Form Design, and made changes.  At least -- I 

' THOUGHT I wasn't interrupting data population!

' 

' * If the front-end with linked tables IS faster, why is that?  Is 

' it because... linked tables are only in use when they are opened 

' up in a form, similar to the way a table or query is only open in 

' code when a recordset is created, then freed up when the 

' recordset is closed?  Whereas when it was just a database with 

' static tables, once the database was open it was the same as 

' putting all the tables in use?  (This is just a theory.)

' 

' * Now that I have a front-end and back-end....which one of these 

' things is supposed to get compacted and repaired?

' 

' * That's another question...compacting and repairing.  Should one 

' compact and repair, or repair and compact?  I've read that 

' repairing actually makes a database BIGGER, which led me to think 

' that I should repair first, then compact.  But I've learned never 

' to believe anything I think.

' 

' * The next step I will take will be to convert this new '97 

' front-end to Access 2000, thereby having two front-ends, one for 

' each type of Office user (Giantdb97.mdb and Giantdb2000.mdb). 

' Running the Access 2000 convert on Giantdb97.mdb won't "hurt" the 

' tables now that they're linked (from Giantdb97_be.mdb) instead of 

' static, will it?

' 

' I guess that's all for now!  I am looking forward to your very 

' enlightening responses :).

' 

' Susan

' 

' 

' 

' 

' 

' 

' 

Hi everyone,



I've got a new problem now!



I was given a chance to visit someone's Office 2000 PC, and gave a couple 

of the A2K front-ends a try. 



On the first database:  EXTREMELY SLOW.  Completely totally unacceptably 

and unbelievably slow!  The employee has a Pentium II, whilst I have a 

Pentium III, but I don't know if that should make it as awful as it was.  

I also copied the front-end to the user's C: drive and that didn't improve 

it one bit.  Oh my.  If this is to be the performance -- my company isn't 

going to let me get away with it.



Has anyone experienced this problem?  



On the other A2K database front-end, the database wouldn't open up a 

report.  I got a "debug" message that said it couldn't perform the 

action.  The "action" was nothing more than: 



DoCmd.ShowToolbar "Form View", acToolbarNo



This is totally acceptable according to A2K Help Files, so why is the code 

breaking here?  A2K *does* have a "Form View" toolbar, so whassup with 

*that*?



Even when I programmed it to "Resume Next" upon hitting that error, it 

tied up the system until we finally had to ALT-CTRL-DELETE it.



Help?!



Susan