Hi there! We have an Access 2000 database which is split into a local 

front end linked to a back end located on a file server. I am experiencing 

lots of problems with the VB modules within the front end going corrupt, 

normally after I have added several new forms. This leads me to believe 

that perhaps I am reaching some sort of limit to the number of objects I 

can safely have within it. Currently it is about 30meg, with a couple of 

thousand forms, reports etc. The back end is about 300meg. Its an enormous 

pain, as I often lose a days work due to this behaviour. We realise that 

it is time to move on, and so will soon be upgrading the back end to SQL 

and the front end to an Access Project. But the question is, will an 

Access project have the same problems as my existing front end? Does it 

have a problem with too many objects too? Or will it in fact be a lot 

more 'industrial strength' than our existing, purely access, front end? It 

seems pointless to upgrade to a project if I am going to encounter the 

same annoying difficulties as before. If this turned out to be the case 

then maybe its time to abandon Access altogether, so what is regarded as 

*the* tool to write a SQL front end, bearing in mind there are several 

years of work invested in the existing front end? Many thanks for any 

answers or comments.    

Wow--a couple of thousand forms?  All in active use?  It boggles my mind...

Just out of curiousity, how many concurrent users are there?



If front-end file corruption is the problem, I'm skeptical that going to an

ADP would cure it.  Moving your back-end to SQL server will definitely help

with back-end capacity tho, and once you do that I believe you can gain a

lot of perf benefits & scalability if you go with an ADP.  But my guess

(emphasis on the word guess) is that the ADP file format is similar enough

to a front-end MDB that whatever it is that's causing your MDB to become

corrupted has a good shot at corrupting your ADPs as well.



(My understanding is that the operative differences between MDBs and ADPs

are that Access does not spawn an instance of Jet automatically and all

Access-created data objects (e.g. form recordsets for bound forms) are of

the ADO variety.)



Are your users by any chance sharing a single front end on the server, or is

the FE local to each user?  If shared, I would first try moving the FE out

to the users and see if that doesn't help.  According to Access God Ken Getz

(http://www.advisor.com/Articles.nsf/aid/HEDMC01) if a user of a shared

Access front-end's machine crashes (and I bet also if they have a

not-quite-stellar network interface card) they can leave the FE corrupted

for everybody.



If they are not sharing the front-end, one other thing to try if you haven't

yet is to make the FE an MDE file instead of an MDB--that'd be relatively

easy.  See the help file for details on that.



Another thing to try is decompiling your mdb file.  See



http://www.mvps.org/access/bugs/bugs0008.htm



for details and caveats.  I like to Decompile, compact, recompile, and then

compact again.



If you do want to go the ADP/SQL server route, do yourself a favor and get a

copy of Chipman & Baron's _Access Developer's Guide to SQL Server_.  They

lay out lots of issues & best practices in that book.



If you end up ditching Access altogether, probably the most common front-end

development tool is VB proper, tho I imagine you could go with about any COM

or ODBC-compliant development tool you wanted.  IMHO, Access is way more fun

than VB--the forms engine is much better tailored to database applications,

offers a richer event model, etc.  But of course quite a lot of your VBA

code will run unmodified in VB proper.  Reporting in VB6 is especially

crappy--most devs I know about end up either purchasing a copy of crystal

reports, or installing the version that came with VB5 from off the visual

studio CD.



Ach, this message is too long... back to work for me.



HTH,



-Roy





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

From: Alan Edwards [mailto:alan.edwards@n...]

Sent: Friday, September 07, 2001 8:14 AM

To: Access

Subject: [access] Will an Access Project be more robust?





Hi there! We have an Access 2000 database which is split into a local 

front end linked to a back end located on a file server. I am experiencing 

lots of problems with the VB modules within the front end going corrupt, 

normally after I have added several new forms. This leads me to believe 

that perhaps I am reaching some sort of limit to the number of objects I 

can safely have within it. Currently it is about 30meg, with a couple of 

thousand forms, reports etc. The back end is about 300meg. Its an enormous 

pain, as I often lose a days work due to this behaviour. We realise that 

it is time to move on, and so will soon be upgrading the back end to SQL 

and the front end to an Access Project. But the question is, will an 

Access project have the same problems as my existing front end? Does it 

have a problem with too many objects too? Or will it in fact be a lot 

more 'industrial strength' than our existing, purely access, front end? It 

seems pointless to upgrade to a project if I am going to encounter the 

same annoying difficulties as before. If this turned out to be the case 

then maybe its time to abandon Access altogether, so what is regarded as 

*the* tool to write a SQL front end, bearing in mind there are several 

years of work invested in the existing front end? Many thanks for any 

answers or comments.

Thanks for the comments, Roy. To answer your questions, we currently have 

about 35 people accessing the database, but the front end is indeed stored 

locally on their individual machines. The users never see the problem, 

only I have this hassle! Only the back end is shared, and we rarely have a 

problem with that. The forms are pretty much all in active use, I actually 

spent some time searching through for redundant ones on Friday and only 

got rid of about 20! Decompiling is a regular operation here. At the first 

sign of trouble, illegal operations and whatnot, we give it one of those 

and that drags it back from the brink for a while!



I think I have little choice but to give an ADP a go, because of the 

amount of time and hassle involved in redesigning everything in VB. An 

option we are considering is splitting the front end so there are several 

different versions containing only the particular functionality that 

individual users need, but its looking like quite a messy operation. Oh 

well, fingers crossed!



Alan

One thing to watch out for in an adp. No local tables. You can only have one

back end - one source of tables. Now that back end can have links to others,

but that is not at all the same thing as local tables.



Temporary tables will fill in to some degree, but if you use local tables as

recordsources for Access subforms, you had  best do some research and

experimenting first before you jump into an adp. So far the only workaround

I have come up with is kinda dirty.



Peter



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

> From: Alan Edwards [mailto:alan.edwards@n...]

> Sent: September 10, 2001 9:57 AM

> To: Access

> Subject: [access] RE: Will an Access Project be more robust?

>

>

> Thanks for the comments, Roy. To answer your questions, we currently have

> about 35 people accessing the database, but the front end is

> indeed stored

> locally on their individual machines. The users never see the problem,

> only I have this hassle! Only the back end is shared, and we

> rarely have a

> problem with that. The forms are pretty much all in active use, I

> actually

> spent some time searching through for redundant ones on Friday and only

> got rid of about 20! Decompiling is a regular operation here. At

> the first

> sign of trouble, illegal operations and whatnot, we give it one of those

> and that drags it back from the brink for a while!

>

> I think I have little choice but to give an ADP a go, because of the

> amount of time and hassle involved in redesigning everything in VB. An

> option we are considering is splitting the front end so there are several

> different versions containing only the particular functionality that

> individual users need, but its looking like quite a messy operation. Oh

> well, fingers crossed!

>

> Alan



>

>

Wow, drag--it sounds like you're covering all the regular bases.  Thanks for

indulging my questions.



One last thing comes to mind--if you haven't yet, have a look at this KB

article on troubleshooting corrupted databases: 

http://support.microsoft.com/support/kb/articles/Q209/1/37.ASP



The last bit on common causes of corruption may be of use.  I'm not sure

what to make of the fact that it's only you having the problem.  Are you

working with a local copy of the front-end, or off the server?  If the

latter, try copying the file to your local drive & working with it there

(that's my standard procedure--I work with a local copy & then refresh the

networked copy when I'm done).  If nothing else, you should see some speed

improvement when you save the .mdb.



Good luck!



-Roy



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

From: Alan Edwards [mailto:alan.edwards@n...]

Sent: Monday, September 10, 2001 2:57 AM

To: Access

Subject: [access] RE: Will an Access Project be more robust?





Thanks for the comments, Roy. To answer your questions, we currently have 

about 35 people accessing the database, but the front end is indeed stored 

locally on their individual machines. The users never see the problem, 

only I have this hassle! Only the back end is shared, and we rarely have a 

problem with that. The forms are pretty much all in active use, I actually 

spent some time searching through for redundant ones on Friday and only 

got rid of about 20! Decompiling is a regular operation here. At the first 

sign of trouble, illegal operations and whatnot, we give it one of those 

and that drags it back from the brink for a while!



I think I have little choice but to give an ADP a go, because of the 

amount of time and hassle involved in redesigning everything in VB. An 

option we are considering is splitting the front end so there are several 

different versions containing only the particular functionality that 

individual users need, but its looking like quite a messy operation. Oh 

well, fingers crossed!



Alan