Corruption woes.

[Fred MacMurray]

Hello, all.

So we're all happy with Access. Or Access is "good enough" with most
of our applications. I built a split database here at work that has
about seven users who are on pretty much the entire workday. The
backend is on the server with each client having a frontend.

I've spent the past couple days researching corruption because I am
constantly restoring backed up copies of my backend. Usually once a
week I have to restore a backup or create a new database and import
all the objects from the old database. This is getting to be a pain
because the database is a growing 500mb and making my coworkers pissed
off. Thank God that I have a redundant error logs (sometimes the
error log become corrupt!) to record when the backend is broke. I am
confident in my backend design and client VB code (over three years
experience writing/designing) but some tables in the database just
become corrupt by not allowing record additions. I'll try to add a
record myself, one that I know is unique, but still Access gives a
message that an existing record already exists in the table. From
reading corruption articles from this newsgroup and the Microsoft
website I've noticed that a lot of the corruption issues have to do
with hardware and networking equipment such as: faulty NIC's,
hibernation of client's PC's, intermittent network connections.

So my question to all of you is the following: is the solution ASP?
Is the solution to use Access as a backend and use browser forms to
interact with the database? I've built three HTML and ASP websites
with Access as the backend and have never had any problems. Let me
reiterate, no problems at all. There would probably be performance
issues with over ten or so users but with less than ten users I think
it would be the way to go.

I would appreciate people's thoughts on the matter.

Fred

[Pavel Romashkin]

Re: Corruption woes.

I have notice this kind of thing with my customer's database. It is
VB6-ADO I suspect: I didn't write it, its secured and I don't want to
mess with it - they purchased it from another vendor). But it is over
400 Mb and it got corrupted several times (not nearly as frequently as
yours though - like once in 3-4 months). I keep daily backups of it so
it has not been that much of a pain.
Is BE properly repaired/compacted? It is becoming rather large. Maybe
its time to upscale.
Pavel

Fred MacMurray wrote:[color=blue]
>
> because the database is a growing 500mb[/color]

[Tony Toews]

Re: Corruption woes.

fmacmurray@juno .com (Fred MacMurray) wrote:
[color=blue]
>I've spent the past couple days researching corruption because I am
>constantly restoring backed up copies of my backend. Usually once a
>week I have to restore a backup or create a new database and import
>all the objects from the old database. This is getting to be a pain
>because the database is a growing 500mb and making my coworkers pissed
>off. Thank God that I have a redundant error logs (sometimes the
>error log become corrupt!) to record when the backend is broke. I am
>confident in my backend design and client VB code (over three years
>experience writing/designing) but some tables in the database just
>become corrupt by not allowing record additions. I'll try to add a
>record myself, one that I know is unique, but still Access gives a
>message that an existing record already exists in the table.[/color]

You sound fairly knowledgable so my commment is something you may already be aware
of. Are there unique indexes present on fields which aren't the primary index which
are causing the record additions to not be allowed?
[color=blue]
>From
>reading corruption articles from this newsgroup and the Microsoft
>website I've noticed that a lot of the corruption issues have to do
>with hardware and networking equipment such as: faulty NIC's,
>hibernation of client's PC's, intermittent network connections.[/color]

Not at all. The most common problem is the OpLocks problem which is especially
apparent when an app is converted to Access 2000 or newer or when the first Win 2000
client is added to a network consisting of a Win NT/2000/2003 server and until now
Win 95/98/ME clients.

Other problems can be caused by differing SR or Jet versions on clients. See the
Access Corruptions FAQ at my website.
[color=blue]
>So my question to all of you is the following: is the solution ASP?
>Is the solution to use Access as a backend and use browser forms to
>interact with the database? I've built three HTML and ASP websites
>with Access as the backend and have never had any problems. Let me
>reiterate, no problems at all. There would probably be performance
>issues with over ten or so users but with less than ten users I think
>it would be the way to go.[/color]

I don't think so because you lose the rich event model of Access, subforms and combo
boxes become uglier, and it's likely a lot more work.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

[Dean Arnold]

Re: Corruption woes.


"Tony Toews" <ttoews@teluspl anet.net> wrote in message
news:ma21pv0om1 u6vb44jd5r3jg7d 8tiiu6p2o@4ax.c om...[color=blue]
> fmacmurray@juno .com (Fred MacMurray) wrote:
>[color=green]
> >I've spent the past couple days researching corruption because I am
> >constantly restoring backed up copies of my backend. Usually once a
> >week I have to restore a backup or create a new database and import
> >all the objects from the old database. This is getting to be a pain
> >because the database is a growing 500mb and making my coworkers pissed
> >off. Thank God that I have a redundant error logs (sometimes the
> >error log become corrupt!) to record when the backend is broke. I am
> >confident in my backend design and client VB code (over three years
> >experience writing/designing) but some tables in the database just
> >become corrupt by not allowing record additions. I'll try to add a
> >record myself, one that I know is unique, but still Access gives a
> >message that an existing record already exists in the table.[/color]
>
> You sound fairly knowledgable so my commment is something you may already[/color]
be aware[color=blue]
> of. Are there unique indexes present on fields which aren't the primary[/color]
index which[color=blue]
> are causing the record additions to not be allowed?
>[color=green]
> >From
> >reading corruption articles from this newsgroup and the Microsoft
> >website I've noticed that a lot of the corruption issues have to do
> >with hardware and networking equipment such as: faulty NIC's,
> >hibernation of client's PC's, intermittent network connections.[/color]
>
> Not at all. The most common problem is the OpLocks problem which is[/color]
especially[color=blue]
> apparent when an app is converted to Access 2000 or newer or when the[/color]
first Win 2000[color=blue]
> client is added to a network consisting of a Win NT/2000/2003 server and[/color]
until now[color=blue]
> Win 95/98/ME clients.
>
> Other problems can be caused by differing SR or Jet versions on clients.[/color]
See the[color=blue]
> Access Corruptions FAQ at my website.
>[color=green]
> >So my question to all of you is the following: is the solution ASP?
> >Is the solution to use Access as a backend and use browser forms to
> >interact with the database? I've built three HTML and ASP websites
> >with Access as the backend and have never had any problems. Let me
> >reiterate, no problems at all. There would probably be performance
> >issues with over ten or so users but with less than ten users I think
> >it would be the way to go.[/color]
>
> I don't think so because you lose the rich event model of Access, subforms[/color]
and combo[color=blue]
> boxes become uglier, and it's likely a lot more work.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm[/color]


Tony:
Have you ever visited the MVP site?

http://mvp.org

Dean Arnold, MVP

[Fred MacMurray]

Re: Corruption woes.

Tony,

I can't compact and repair (C&R) the backend database. I tried
several times to C&R the database outside of normal working hours when
nobody is there. Don't get me wrong, the database will C&R
successfully, however, one or more tables in the database will then
stop accepting more records. For instance I have a table called
tbl_Account with 95K records, with one non-duplicate index primary key
field being Account and a non-duplicate index of field ID (see figure
1 below). If I try to add an account number that I know doesn't
exist, I just get an Access message saying a existing record already
exists in the table and the new record cannot be added. The database
doesn't seem to care which tables become corrupt. My error log
doesn't have any indexes, yet it became corrupt and it only has 20 or
so records. Most of the errors in the error log are 3000 level errors
which as you know are network related problems, thus my original post
in the first place to go with a pure HTML and ASP solution. The only
way that I can save the backend is to create a new database and import
all the objects, delete the old backend, rename the new backend, and
then set permissions.

Figure 1.

tbl_Account
---------------
Account Text(10)
ID AutoNumber
RecordCreated Date
RecordCreatedBy Text(15)
LastModified Date
LastModifiedBy Text(15)

The backend is Access 2000 format. The client machines run a combo of
Office 2000 and Office XP. There are five clients with mde files, and
two Admins that I allow to run mdb files. I have no idea if the
server is running Windows 2000 or XP but I can't just phone over to IT
to ask them to start fiddling with the server and adjust the OpLocks
properties because a lot of people use to the server outside of my
department running a variety of applications. Besides, I'll come off
as a lunch room conversation dumbass (we told you that Access sucks
Fred) from the IT department. Furthermore, I can't just go around to
the frontend clients and ask to kick them off their computers for
their lunch break to install service packs which may or may not be
backwards compatible with their existing applications. Sort of a my
cubicle = my domain = go away thing exists.

As for subforms, well they kind of suck to begin with in my own
opinion. Please don't launch some new thread in defense of subforms
but my experience with subforms is that frontend users find them
rather complicated. They don't understand/care to understand database
schema, specially, one to many relationships. Your right in saying
that HTML is more complicated because you can't have subforms (maybe
some guru could design a pure Java applet website with one) but it can
be done with good backend design, specifically, an ID (AutoNumber)
field in each table. Look at your personal address book on
Amazon.com. That is such an example of one to many written in HTML,
works perfectly, and is rather easy for frontend users to understand.

My point is that once you get up to 50 or so tables the database is
already complex. If you are the sole designer of the database you
should be able to follow your code, even spaghetti code such as ASP.
If you have a good web hosting company, a good internet connection,
solid backend design, and good code (objRS.close, etc...) then I think
that an HTML and ASP solution is the way to go. It hasn't failed me
yet.

Fred

Tony Toews <ttoews@teluspl anet.net> wrote in message news:
<ma21pv0om1u6vb 44jd5r3jg7d8tii u6p2o@4ax.com>. ..[color=blue]
> fmacmurray@juno .com (Fred MacMurray) wrote:
>[color=green]
> >I've spent the past couple days researching corruption because I am
> >constantly restoring backed up copies of my backend. Usually once a
> >week I have to restore a backup or create a new database and import
> >all the objects from the old database. This is getting to be a pain
> >because the database is a growing 500mb and making my coworkers pissed
> >off. Thank God that I have a redundant error logs (sometimes the
> >error log become corrupt!) to record when the backend is broke. I am
> >confident in my backend design and client VB code (over three years
> >experience writing/designing) but some tables in the database just
> >become corrupt by not allowing record additions. I'll try to add a
> >record myself, one that I know is unique, but still Access gives a
> >message that an existing record already exists in the table.[/color]
>
> You sound fairly knowledgable so my commment is something you may already be aware
> of. Are there unique indexes present on fields which aren't the primary index which
> are causing the record additions to not be allowed?
>[color=green]
> >From
> >reading corruption articles from this newsgroup and the Microsoft
> >website I've noticed that a lot of the corruption issues have to do
> >with hardware and networking equipment such as: faulty NIC's,
> >hibernation of client's PC's, intermittent network connections.[/color]
>
> Not at all. The most common problem is the OpLocks problem which is especially
> apparent when an app is converted to Access 2000 or newer or when the first Win 2000
> client is added to a network consisting of a Win NT/2000/2003 server and until now
> Win 95/98/ME clients.
>
> Other problems can be caused by differing SR or Jet versions on clients. See the
> Access Corruptions FAQ at my website.
>[color=green]
> >So my question to all of you is the following: is the solution ASP?
> >Is the solution to use Access as a backend and use browser forms to
> >interact with the database? I've built three HTML and ASP websites
> >with Access as the backend and have never had any problems. Let me
> >reiterate, no problems at all. There would probably be performance
> >issues with over ten or so users but with less than ten users I think
> >it would be the way to go.[/color]
>
> I don't think so because you lose the rich event model of Access, subforms and combo
> boxes become uglier, and it's likely a lot more work.
>
> Tony[/color]

[MeadeR]

Re: Corruption woes.

I've implemented MS Access with ASP and it's a great approach - you
don't have the 'flexability' of the forms you would in Access or VB -
but you don't really lose much and the maintainability is great - no
need to ever visit a user workstation again - they just point to the
web site.....anothe r approach would be Citrix - if you have the $ for
it. I've supported over 50 users with ASP and have had no performance
problems at all - I would expect being able to support up to 200
active prior to moving to a more robust database...


fmacmurray@juno .com (Fred MacMurray) wrote in message news:<74db5eb6. 0310180925.1914 04ad@posting.go ogle.com>...[color=blue]
> Tony,
>
> I can't compact and repair (C&R) the backend database. I tried
> several times to C&R the database outside of normal working hours when
> nobody is there. Don't get me wrong, the database will C&R
> successfully, however, one or more tables in the database will then
> stop accepting more records. For instance I have a table called
> tbl_Account with 95K records, with one non-duplicate index primary key
> field being Account and a non-duplicate index of field ID (see figure
> 1 below). If I try to add an account number that I know doesn't
> exist, I just get an Access message saying a existing record already
> exists in the table and the new record cannot be added. The database
> doesn't seem to care which tables become corrupt. My error log
> doesn't have any indexes, yet it became corrupt and it only has 20 or
> so records. Most of the errors in the error log are 3000 level errors
> which as you know are network related problems, thus my original post
> in the first place to go with a pure HTML and ASP solution. The only
> way that I can save the backend is to create a new database and import
> all the objects, delete the old backend, rename the new backend, and
> then set permissions.
>
> Figure 1.
>
> tbl_Account
> ---------------
> Account Text(10)
> ID AutoNumber
> RecordCreated Date
> RecordCreatedBy Text(15)
> LastModified Date
> LastModifiedBy Text(15)
>
> The backend is Access 2000 format. The client machines run a combo of
> Office 2000 and Office XP. There are five clients with mde files, and
> two Admins that I allow to run mdb files. I have no idea if the
> server is running Windows 2000 or XP but I can't just phone over to IT
> to ask them to start fiddling with the server and adjust the OpLocks
> properties because a lot of people use to the server outside of my
> department running a variety of applications. Besides, I'll come off
> as a lunch room conversation dumbass (we told you that Access sucks
> Fred) from the IT department. Furthermore, I can't just go around to
> the frontend clients and ask to kick them off their computers for
> their lunch break to install service packs which may or may not be
> backwards compatible with their existing applications. Sort of a my
> cubicle = my domain = go away thing exists.
>
> As for subforms, well they kind of suck to begin with in my own
> opinion. Please don't launch some new thread in defense of subforms
> but my experience with subforms is that frontend users find them
> rather complicated. They don't understand/care to understand database
> schema, specially, one to many relationships. Your right in saying
> that HTML is more complicated because you can't have subforms (maybe
> some guru could design a pure Java applet website with one) but it can
> be done with good backend design, specifically, an ID (AutoNumber)
> field in each table. Look at your personal address book on
> Amazon.com. That is such an example of one to many written in HTML,
> works perfectly, and is rather easy for frontend users to understand.
>
> My point is that once you get up to 50 or so tables the database is
> already complex. If you are the sole designer of the database you
> should be able to follow your code, even spaghetti code such as ASP.
> If you have a good web hosting company, a good internet connection,
> solid backend design, and good code (objRS.close, etc...) then I think
> that an HTML and ASP solution is the way to go. It hasn't failed me
> yet.
>
> Fred
>
> Tony Toews <ttoews@teluspl anet.net> wrote in message news:
> <ma21pv0om1u6vb 44jd5r3jg7d8tii u6p2o@4ax.com>. ..[color=green]
> > fmacmurray@juno .com (Fred MacMurray) wrote:
> >[color=darkred]
> > >I've spent the past couple days researching corruption because I am
> > >constantly restoring backed up copies of my backend. Usually once a
> > >week I have to restore a backup or create a new database and import
> > >all the objects from the old database. This is getting to be a pain
> > >because the database is a growing 500mb and making my coworkers pissed
> > >off. Thank God that I have a redundant error logs (sometimes the
> > >error log become corrupt!) to record when the backend is broke. I am
> > >confident in my backend design and client VB code (over three years
> > >experience writing/designing) but some tables in the database just
> > >become corrupt by not allowing record additions. I'll try to add a
> > >record myself, one that I know is unique, but still Access gives a
> > >message that an existing record already exists in the table.[/color]
> >
> > You sound fairly knowledgable so my commment is something you may already be aware
> > of. Are there unique indexes present on fields which aren't the primary index which
> > are causing the record additions to not be allowed?
> >[color=darkred]
> > >From
> > >reading corruption articles from this newsgroup and the Microsoft
> > >website I've noticed that a lot of the corruption issues have to do
> > >with hardware and networking equipment such as: faulty NIC's,
> > >hibernation of client's PC's, intermittent network connections.[/color]
> >
> > Not at all. The most common problem is the OpLocks problem which is especially
> > apparent when an app is converted to Access 2000 or newer or when the first Win 2000
> > client is added to a network consisting of a Win NT/2000/2003 server and until now
> > Win 95/98/ME clients.
> >
> > Other problems can be caused by differing SR or Jet versions on clients. See the
> > Access Corruptions FAQ at my website.
> >[color=darkred]
> > >So my question to all of you is the following: is the solution ASP?
> > >Is the solution to use Access as a backend and use browser forms to
> > >interact with the database? I've built three HTML and ASP websites
> > >with Access as the backend and have never had any problems. Let me
> > >reiterate, no problems at all. There would probably be performance
> > >issues with over ten or so users but with less than ten users I think
> > >it would be the way to go.[/color]
> >
> > I don't think so because you lose the rich event model of Access, subforms and combo
> > boxes become uglier, and it's likely a lot more work.
> >
> > Tony[/color][/color]

[Fred MacMurray]

Re: Corruption woes.

MeadeR,

See, that's what I am talking about! No more service pack hassles, no
more network and NIC troubleshooting , and no more running around to
client machines to install the latest frontends.

You really have 50 users hitting your backend at the same time? How
big is the mdb?

Fred

MeadeR@Computer SOSNJ.com (MeadeR) wrote in message news:
<32684913.03101 81510.30f828f@p osting.google.c om>...[color=blue]
> I've implemented MS Access with ASP and it's a great approach - you
> don't have the 'flexability' of the forms you would in Access or VB -
> but you don't really lose much and the maintainability is great - no
> need to ever visit a user workstation again - they just point to the
> web site.....anothe r approach would be Citrix - if you have the $ for
> it. I've supported over 50 users with ASP and have had no performance
> problems at all - I would expect being able to support up to 200
> active prior to moving to a more robust database...
>
>
> fmacmurray@juno .com (Fred MacMurray) wrote in message news:<74db5eb6. 0310180925.1914 04ad@posting.go ogle.com>...[color=green]
> > Tony,
> >
> > I can't compact and repair (C&R) the backend database. I tried
> > several times to C&R the database outside of normal working hours when
> > nobody is there. Don't get me wrong, the database will C&R
> > successfully, however, one or more tables in the database will then
> > stop accepting more records. For instance I have a table called
> > tbl_Account with 95K records, with one non-duplicate index primary key
> > field being Account and a non-duplicate index of field ID (see figure
> > 1 below). If I try to add an account number that I know doesn't
> > exist, I just get an Access message saying a existing record already
> > exists in the table and the new record cannot be added. The database
> > doesn't seem to care which tables become corrupt. My error log
> > doesn't have any indexes, yet it became corrupt and it only has 20 or
> > so records. Most of the errors in the error log are 3000 level errors
> > which as you know are network related problems, thus my original post
> > in the first place to go with a pure HTML and ASP solution. The only
> > way that I can save the backend is to create a new database and import
> > all the objects, delete the old backend, rename the new backend, and
> > then set permissions.
> >
> > Figure 1.
> >
> > tbl_Account
> > ---------------
> > Account Text(10)
> > ID AutoNumber
> > RecordCreated Date
> > RecordCreatedBy Text(15)
> > LastModified Date
> > LastModifiedBy Text(15)
> >
> > The backend is Access 2000 format. The client machines run a combo of
> > Office 2000 and Office XP. There are five clients with mde files, and
> > two Admins that I allow to run mdb files. I have no idea if the
> > server is running Windows 2000 or XP but I can't just phone over to IT
> > to ask them to start fiddling with the server and adjust the OpLocks
> > properties because a lot of people use to the server outside of my
> > department running a variety of applications. Besides, I'll come off
> > as a lunch room conversation dumbass (we told you that Access sucks
> > Fred) from the IT department. Furthermore, I can't just go around to
> > the frontend clients and ask to kick them off their computers for
> > their lunch break to install service packs which may or may not be
> > backwards compatible with their existing applications. Sort of a my
> > cubicle = my domain = go away thing exists.
> >
> > As for subforms, well they kind of suck to begin with in my own
> > opinion. Please don't launch some new thread in defense of subforms
> > but my experience with subforms is that frontend users find them
> > rather complicated. They don't understand/care to understand database
> > schema, specially, one to many relationships. Your right in saying
> > that HTML is more complicated because you can't have subforms (maybe
> > some guru could design a pure Java applet website with one) but it can
> > be done with good backend design, specifically, an ID (AutoNumber)
> > field in each table. Look at your personal address book on
> > Amazon.com. That is such an example of one to many written in HTML,
> > works perfectly, and is rather easy for frontend users to understand.
> >
> > My point is that once you get up to 50 or so tables the database is
> > already complex. If you are the sole designer of the database you
> > should be able to follow your code, even spaghetti code such as ASP.
> > If you have a good web hosting company, a good internet connection,
> > solid backend design, and good code (objRS.close, etc...) then I think
> > that an HTML and ASP solution is the way to go. It hasn't failed me
> > yet.
> >
> > Fred
> >
> > Tony Toews <ttoews@teluspl anet.net> wrote in message news:
> > <ma21pv0om1u6vb 44jd5r3jg7d8tii u6p2o@4ax.com>. ..[color=darkred]
> > > fmacmurray@juno .com (Fred MacMurray) wrote:
> > >
> > > >I've spent the past couple days researching corruption because I am
> > > >constantly restoring backed up copies of my backend. Usually once a
> > > >week I have to restore a backup or create a new database and import
> > > >all the objects from the old database. This is getting to be a pain
> > > >because the database is a growing 500mb and making my coworkers pissed
> > > >off. Thank God that I have a redundant error logs (sometimes the
> > > >error log become corrupt!) to record when the backend is broke. I am
> > > >confident in my backend design and client VB code (over three years
> > > >experience writing/designing) but some tables in the database just
> > > >become corrupt by not allowing record additions. I'll try to add a
> > > >record myself, one that I know is unique, but still Access gives a
> > > >message that an existing record already exists in the table.
> > >
> > > You sound fairly knowledgable so my commment is something you may already be aware
> > > of. Are there unique indexes present on fields which aren't the primary index which
> > > are causing the record additions to not be allowed?
> > >
> > > >From
> > > >reading corruption articles from this newsgroup and the Microsoft
> > > >website I've noticed that a lot of the corruption issues have to do
> > > >with hardware and networking equipment such as: faulty NIC's,
> > > >hibernation of client's PC's, intermittent network connections.
> > >
> > > Not at all. The most common problem is the OpLocks problem which is especially
> > > apparent when an app is converted to Access 2000 or newer or when the first Win 2000
> > > client is added to a network consisting of a Win NT/2000/2003 server and until now
> > > Win 95/98/ME clients.
> > >
> > > Other problems can be caused by differing SR or Jet versions on clients. See the
> > > Access Corruptions FAQ at my website.
> > >
> > > >So my question to all of you is the following: is the solution ASP?
> > > >Is the solution to use Access as a backend and use browser forms to
> > > >interact with the database? I've built three HTML and ASP websites
> > > >with Access as the backend and have never had any problems. Let me
> > > >reiterate, no problems at all. There would probably be performance
> > > >issues with over ten or so users but with less than ten users I think
> > > >it would be the way to go.
> > >
> > > I don't think so because you lose the rich event model of Access, subforms and combo
> > > boxes become uglier, and it's likely a lot more work.
> > >
> > > Tony[/color][/color][/color]

[MeadeR]

Re: Corruption woes.

Yep - ASP - takes away allot of the hassles of workstation based
installations. I have 50 active users - probably 5-10 hitting the
backend at any one time. The mdb is about 100 megs, but it also
connects to about 20 SQL Server based tables. It's a combination
Incident tracking system, config management and release scheduling
system. I used Dreamweaver to develop the web side.

fmacmurray@juno .com (Fred MacMurray) wrote in message news:<74db5eb6. 0310182248.71aa d886@posting.go ogle.com>...[color=blue]
> MeadeR,
>
> See, that's what I am talking about! No more service pack hassles, no
> more network and NIC troubleshooting , and no more running around to
> client machines to install the latest frontends.
>
> You really have 50 users hitting your backend at the same time? How
> big is the mdb?
>
> Fred
>
> MeadeR@Computer SOSNJ.com (MeadeR) wrote in message news:
> <32684913.03101 81510.30f828f@p osting.google.c om>...[color=green]
> > I've implemented MS Access with ASP and it's a great approach - you
> > don't have the 'flexability' of the forms you would in Access or VB -
> > but you don't really lose much and the maintainability is great - no
> > need to ever visit a user workstation again - they just point to the
> > web site.....anothe r approach would be Citrix - if you have the $ for
> > it. I've supported over 50 users with ASP and have had no performance
> > problems at all - I would expect being able to support up to 200
> > active prior to moving to a more robust database...
> >
> >
> > fmacmurray@juno .com (Fred MacMurray) wrote in message news:<74db5eb6. 0310180925.1914 04ad@posting.go ogle.com>...[color=darkred]
> > > Tony,
> > >
> > > I can't compact and repair (C&R) the backend database. I tried
> > > several times to C&R the database outside of normal working hours when
> > > nobody is there. Don't get me wrong, the database will C&R
> > > successfully, however, one or more tables in the database will then
> > > stop accepting more records. For instance I have a table called
> > > tbl_Account with 95K records, with one non-duplicate index primary key
> > > field being Account and a non-duplicate index of field ID (see figure
> > > 1 below). If I try to add an account number that I know doesn't
> > > exist, I just get an Access message saying a existing record already
> > > exists in the table and the new record cannot be added. The database
> > > doesn't seem to care which tables become corrupt. My error log
> > > doesn't have any indexes, yet it became corrupt and it only has 20 or
> > > so records. Most of the errors in the error log are 3000 level errors
> > > which as you know are network related problems, thus my original post
> > > in the first place to go with a pure HTML and ASP solution. The only
> > > way that I can save the backend is to create a new database and import
> > > all the objects, delete the old backend, rename the new backend, and
> > > then set permissions.
> > >
> > > Figure 1.
> > >
> > > tbl_Account
> > > ---------------
> > > Account Text(10)
> > > ID AutoNumber
> > > RecordCreated Date
> > > RecordCreatedBy Text(15)
> > > LastModified Date
> > > LastModifiedBy Text(15)
> > >
> > > The backend is Access 2000 format. The client machines run a combo of
> > > Office 2000 and Office XP. There are five clients with mde files, and
> > > two Admins that I allow to run mdb files. I have no idea if the
> > > server is running Windows 2000 or XP but I can't just phone over to IT
> > > to ask them to start fiddling with the server and adjust the OpLocks
> > > properties because a lot of people use to the server outside of my
> > > department running a variety of applications. Besides, I'll come off
> > > as a lunch room conversation dumbass (we told you that Access sucks
> > > Fred) from the IT department. Furthermore, I can't just go around to
> > > the frontend clients and ask to kick them off their computers for
> > > their lunch break to install service packs which may or may not be
> > > backwards compatible with their existing applications. Sort of a my
> > > cubicle = my domain = go away thing exists.
> > >
> > > As for subforms, well they kind of suck to begin with in my own
> > > opinion. Please don't launch some new thread in defense of subforms
> > > but my experience with subforms is that frontend users find them
> > > rather complicated. They don't understand/care to understand database
> > > schema, specially, one to many relationships. Your right in saying
> > > that HTML is more complicated because you can't have subforms (maybe
> > > some guru could design a pure Java applet website with one) but it can
> > > be done with good backend design, specifically, an ID (AutoNumber)
> > > field in each table. Look at your personal address book on
> > > Amazon.com. That is such an example of one to many written in HTML,
> > > works perfectly, and is rather easy for frontend users to understand.
> > >
> > > My point is that once you get up to 50 or so tables the database is
> > > already complex. If you are the sole designer of the database you
> > > should be able to follow your code, even spaghetti code such as ASP.
> > > If you have a good web hosting company, a good internet connection,
> > > solid backend design, and good code (objRS.close, etc...) then I think
> > > that an HTML and ASP solution is the way to go. It hasn't failed me
> > > yet.
> > >
> > > Fred
> > >
> > > Tony Toews <ttoews@teluspl anet.net> wrote in message news:
> > > <ma21pv0om1u6vb 44jd5r3jg7d8tii u6p2o@4ax.com>. ..
> > > > fmacmurray@juno .com (Fred MacMurray) wrote:
> > > >
> > > > >I've spent the past couple days researching corruption because I am
> > > > >constantly restoring backed up copies of my backend. Usually once a
> > > > >week I have to restore a backup or create a new database and import
> > > > >all the objects from the old database. This is getting to be a pain
> > > > >because the database is a growing 500mb and making my coworkers pissed
> > > > >off. Thank God that I have a redundant error logs (sometimes the
> > > > >error log become corrupt!) to record when the backend is broke. I am
> > > > >confident in my backend design and client VB code (over three years
> > > > >experience writing/designing) but some tables in the database just
> > > > >become corrupt by not allowing record additions. I'll try to add a
> > > > >record myself, one that I know is unique, but still Access gives a
> > > > >message that an existing record already exists in the table.
> > > >
> > > > You sound fairly knowledgable so my commment is something you may already be aware
> > > > of. Are there unique indexes present on fields which aren't the primary index which
> > > > are causing the record additions to not be allowed?
> > > >
> > > > >From
> > > > >reading corruption articles from this newsgroup and the Microsoft
> > > > >website I've noticed that a lot of the corruption issues have to do
> > > > >with hardware and networking equipment such as: faulty NIC's,
> > > > >hibernation of client's PC's, intermittent network connections.
> > > >
> > > > Not at all. The most common problem is the OpLocks problem which is especially
> > > > apparent when an app is converted to Access 2000 or newer or when the first Win 2000
> > > > client is added to a network consisting of a Win NT/2000/2003 server and until now
> > > > Win 95/98/ME clients.
> > > >
> > > > Other problems can be caused by differing SR or Jet versions on clients. See the
> > > > Access Corruptions FAQ at my website.
> > > >
> > > > >So my question to all of you is the following: is the solution ASP?
> > > > >Is the solution to use Access as a backend and use browser forms to
> > > > >interact with the database? I've built three HTML and ASP websites
> > > > >with Access as the backend and have never had any problems. Let me
> > > > >reiterate, no problems at all. There would probably be performance
> > > > >issues with over ten or so users but with less than ten users I think
> > > > >it would be the way to go.
> > > >
> > > > I don't think so because you lose the rich event model of Access, subforms and combo
> > > > boxes become uglier, and it's likely a lot more work.
> > > >
> > > > Tony[/color][/color][/color]