Corrupted Database Record

[Jill]

I have a project tracking database used by 50+ people. The database
has been running for the last six months and has been corrupted 4
times. I have learned to backup the database often, but it still is
very annoying. The portion of the database that others have access to
is a form where they enter several memo fields of information. They
also hyperlink a word document into this form (this field is a
hyperlink data type). I cannot be sure, but I believe the database
gets corrupted when people are trying to hyperlink.

Once the database is corrupted, I can usually go in using the shift key
and see that just one record is all funky numbers and Chinese
characters. In the hyperlink field it says #Error and when I hold my
cursor over the field it pops up an error message that says "invalid
argument". Also when I try to delete the record it says "search key
was not found in any record" and it will not delete it.

I usually can copy and past this record from by backup database, but
this person then has to retype all their information into the database.

Any suggestions? By the way, I do very little VB Programming so I need
a simple answer (if possible).

Thanks

 

[dbahooker]

move your database to an Access Data Project; MDB is too unreliable for
real world use.

Not by a single user and a single record!

-Aaron
ADP Nationalist

 

[Jill]

Thanks for the advise. Is there an easy way to do this?

 

[John Vinson]

Any suggestions? By the way, I do very little VB Programming so I need
a simple answer (if possible).

The first suggestion is - SPLIT YOUR DATABASE!

Having a single database open by multiple users across a network is
not a good idea. Instead, use Tools... Database Utilities... Database
Splitter Wizard to split the database into a "backend" containing only
the tables, and a "frontend" containing the Forms, Queries, Reports,
etc. Each user gets their own copy of the frontend (linked to the
tables in the backend); if it should get corrupted, no problem - just
delete it and give them another copy.

See http://www.granite.ab.ca/access - Tony has excellent FAQ's on
database splitting and on corruption.

John W. Vinson[MVP]

 

[dbahooker]

John;

she needs a solution not a bandaid

yeah.. you can simply upsize your database; I believe that it's unders
tools, database utilities.. you will upsize it to SQL Server; there are
2 different formats to choose from:

a) MDB with linked tables
b) ADP

I highly reccomend that you just use ADP.. you might have to re-write /
tweak a good portion of your queries; though.. try to mimic the mdb
format at first-- exactly to the smallest detail; and it should only
take a day or so..

I know I could transform most MDB apps into an ADP in a couple of hours
or a day; if you need to automate; then you can do something lke this
on the MDB side.. issues are that the debug window doesn't keep an
unlimited amount of text; so it would work better to write it to a text
file; but the same general theory applies--

a) try to convert every query to a view; you can even automate adding
clauses that if the qdf.sql contains 'update' or 'delete' or 'insert'
then it will skip trying to create a view out of this. if you have an
order by you may need to use 'select top 100 percent' but I reccomend
not having order by statements in a view

b) if you have a sql statement with update, delete, insert; then you
need to create a stored procedure out of this sql statement. certain
restrictions may apply.

c) store your variables in a single table rather than binding them to
forms; it's a better simpler solution anyways

Public Sub ListQueries
Dim qdf as dao.querydef
For each qdf in currentdb.querydefs
debug.print "create view " & qdf.name & " as " &
debug.print qdf.sql
debug.print "go"
next qdf

For each qdf in currentdb.querydefs
debug.print "create procedure " & qdf.name & " as " &
debug.print qdf.sql
debug.print "go"
next qdf

End Sub

 

[dbahooker]

and you want to upsize to sql 2000; not 2005!

2000 has a free solution called msde 2.0 you can find it at the
download location for SQL 2000 SP4.. you may need to use a DOS command
to install it; kinda depends on which version you find.. for example; I
believe that the version of msde that ships on the office disk itself;
i believe that comes with a decent installer.. but the standalone
version is what you need to use-- because of security concerns


-Aaron

 

[dbahooker]

so fucking what you wimp?

Do you even know what it takes?

It takes like ZERO EFFORT.

I mean seriously here.. migrating between 2000 and 2005 is as simple as
detach / attach or backup restore.

And for the record; they are talking about ON THE SERVER NOT ON THE
DESKTOP... and if your server is running Windows XP / Vista you're
pretty much hosed anyways.

use a high-end desktop for now; plan on moving to Access 2007 at some
time in the next couple of years...

-Aaron

 

[aaron.kempf]

RUNNING SQL 2000 and MSDE aren't supported on Windows Vista.

with the SQL Native Client; you can access sql 2000 or sql 2005
servers; so for the next 10 years you should be able to connect to SQL
2000

-Aaron

 

[aaron.kempf]

it's just about time that you stop spewing IRRELEVENT information in
hopes of connning people into usign your crappy ass database

MDB is for lepers and retards.


Spit on anyone that still uses MDB in the year 2006; it is absolutely
unacceptable.
ADP development is easier/faster.
ADP execution is faster / more scalable

Do you really think that you're going to scare this person away just
because you don't understand the difference between a SERVER and a
DESKTOP?

-Aaron

 

[aaron.kempf]

grown up?

I just won't put up with a crappy ass engine like MDB.

Since 'Access Data Projects' are the heir-apparent to the MS Access
title; why don't you kids go play in the alt.nostalgia.90s channel

-Aaron

 

[Jill]

Joseph-

Thanks for the simple (and polite) response. I have split my database
and will give it a try. Hopefully I will not have any more corrupt
records, but only time will tell.

Jill

Thanks for the advise. Is there an easy way to do this?

It seems Aaron is off his meds again today. I suggest you ignore him
98.5% of the time. He has his own problems.

As for your problem, I am going to guess that your database is shared on
a LAN. That brings up two issues. First Access does require a good LAN to
function properly. It becomes unstable with the LAN is marginal and it is
shared without being split.

When an Access database is shared each user should have their own front
end linked to the back end that is located on the server.

What parts of the database will be on the "server" and will be called the
Back end database from now on and which parts will be on each user's machine
and will be called the front ends. The back end should hold all data that
is shared and may be changed by the users. It should also contain all or
most data that more than one user will need access to and may be changed by
you from time to time. Most other data that does not change or that will
only be used by that particular user should be on the Back end databases on
the users machines.

For example you may have all the sales made by a unit on the back end
along with the price list. The sales may been to be shared by everyone so
they all know what has been done or pending. The price list may not be a
field they will change, but you may need to change to assure everyone has
the same current price available.

Each individual machine may have something about your company like
addresses that does not change or even product descriptions etc. You may
want each user to be able to store personal information about customers like
their kids names or shared information about sports teams or you may want to
put this on the server so everyone will have this information.

This is an art form and a science to get this part of the planning
designed and will be an ongoing job and should include the users in the
planning.

Access works best if it does not need to move a lot of information over
the LAN which means static data is best kept on the front end databases.
Also kept on the front end machines will be most forms, reports queries etc.
This will allow the whole system to work faster and in some cases allow for
customization of some forms reports etc.

This may seem like a lot of work and off the point of the question you
were asking, but it is very important that this part of the job be done
first and right.

Next is the mechanics of setting up the back end on the server, dumping
in the data and putting the front end copies on each user's machines and
assuring that the links work. Access has a built in database splitter that
may make this part of the job (moving from a single database with all the
data and forms etc. to two databases a front end and a back end.) easier.
Look under the Tools menu for it.

You may also want to look into user level security to protect the
database and data before you finish.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can easily lock
you out of your database, which might require the paid services of a
professional to help you get back in.

Practice on some copies to make sure you know what you are doing.

Splitting a database can be a big job, but done right everyone will
thank you and wonder how they did their jobs without it.

Note: back ups become more important here. If you LAN does not support
automatic backups you should provide a method of backing up the data, even
if that means you do it manually.

 

[aaron.kempf]

you need a solution not a bandaid.

hope you like playing russian roulette with your data; that is what
this is all about.
I don't trust MDB; it has FAILED too many times to by practical,
reliable, or useful.

It's not secure; it's not reliable. It's not extensible.

-Aaron

Joseph-

Thanks for the simple (and polite) response. I have split my database
and will give it a try. Hopefully I will not have any more corrupt
records, but only time will tell.

Jill

Thanks for the advise. Is there an easy way to do this?

It seems Aaron is off his meds again today. I suggest you ignore him
98.5% of the time. He has his own problems.

As for your problem, I am going to guess that your database is shared on
a LAN. That brings up two issues. First Access does require a good LAN to
function properly. It becomes unstable with the LAN is marginal and it is
shared without being split.

When an Access database is shared each user should have their own front
end linked to the back end that is located on the server.

What parts of the database will be on the "server" and will be called the
Back end database from now on and which parts will be on each user's machine
and will be called the front ends. The back end should hold all data that
is shared and may be changed by the users. It should also contain all or
most data that more than one user will need access to and may be changed by
you from time to time. Most other data that does not change or that will
only be used by that particular user should be on the Back end databases on
the users machines.

For example you may have all the sales made by a unit on the back end
along with the price list. The sales may been to be shared by everyone so
they all know what has been done or pending. The price list may not be a
field they will change, but you may need to change to assure everyone has
the same current price available.

Each individual machine may have something about your company like
addresses that does not change or even product descriptions etc. You may
want each user to be able to store personal information about customers like
their kids names or shared information about sports teams or you may want to
put this on the server so everyone will have this information.

This is an art form and a science to get this part of the planning
designed and will be an ongoing job and should include the users in the
planning.

Access works best if it does not need to move a lot of information over
the LAN which means static data is best kept on the front end databases.
Also kept on the front end machines will be most forms, reports queries etc.
This will allow the whole system to work faster and in some cases allow for
customization of some forms reports etc.

This may seem like a lot of work and off the point of the question you
were asking, but it is very important that this part of the job be done
first and right.

Next is the mechanics of setting up the back end on the server, dumping
in the data and putting the front end copies on each user's machines and
assuring that the links work. Access has a built in database splitter that
may make this part of the job (moving from a single database with all the
data and forms etc. to two databases a front end and a back end.) easier.
Look under the Tools menu for it.

You may also want to look into user level security to protect the
database and data before you finish.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can easily lock
you out of your database, which might require the paid services of a
professional to help you get back in.

Practice on some copies to make sure you know what you are doing.

Splitting a database can be a big job, but done right everyone will
thank you and wonder how they did their jobs without it.

Note: back ups become more important here. If you LAN does not support
automatic backups you should provide a method of backing up the data, even
if that means you do it manually.

 

[Tom Wickerath]

Hi Jill,

In addition to John Vinson's suggestions, here is a document that you may
find helpful:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

I would consider moving any memo, hyperlink and OLE Object data types to a
separate table, related 1:1. The reason I say this can be found in the
following KB article:

http://support.microsoft.com/kb/275561
See the subtitle: "Record-level locking".

"Also, record-level locking is not enabled for Memo data types."


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[David W. Fenton]

I have split my database
and will give it a try. Hopefully I will not have any more
corrupt records, but only time will tell.

Memos/OLE data pointers can still get corrupted in a split
environment if you've got bad versions of Access/Jet and/or bad
network connections and/or unreliable PCs.

 

[David W. Fenton]

Thanks for the advise. Is there an easy way to do this?

Ignore the "advice." Aaron is a lunatic who has one answer to every
problem.

ADPs are deprecated by Microsoft, anyway, and aren't being enhanced
any longer. Why? Because MS discovered that the concept doesn't work
very well. Or, at least, it doesn't work any better than an MDB.

 

[aaron.kempf]

ROFL

why stick to an obsolete database format?

seriously?

why should she invest in something that is completely obsolete-- in the
year 2006?

MDB has been dead for a decade, kids

-Aaron

 

[Tom Wickerath]

Hi Jill,

I do not see your original question in the MS web portal:
http://www.microsoft.com/office/com...cess&mid=f39f0ec1-be5f-4f8a-8c0c-dfd2f316636e

However, based on some replies that I see, you may find this document helpful:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

Consider breaking any memo, hyperlink or OLE Object data types out to a
separate table, related 1:1. The reason I make this statement is due to this
quote:

"Also, record-level locking is not enabled for Memo data types."

which you can find in this KB article:
http://support.microsoft.com/kb/275561
See the section titled: "Record-level locking".


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[larrylinsonjr]

Granny;

moving from SQL 2000 to SQL 2005.. it doesn't require testing; it
doesn't require anything. Attach / detach, or backup / restore. It's
not like it's going to be buggy-- there AREN'T ANY PROBLEMS moving from
2000 to 2005-- so it's a complete non-issue.

I still disagree with the basic tenant of your argument... I haven't
seen ANYTHING that says that SQL 2000 _DATA_ can't be connected to
using a Vista desktop. Yeah; we can't run MSDE on a Vista desktop; big
friggin deal... lol

I personally have used Vista to connect to SQL 2000 data.. and I sure
haven't had a drop of a problem with it.

So why don't you give more justification for your argument?

You still don't understand the difference between a SERVER and a
DESKTOP

-Larry Linson, Jr

 

[John Vinson]

Granny;

moving from SQL 2000 to SQL 2005.. it doesn't require testing; it
doesn't require anything. Attach / detach, or backup / restore. It's
not like it's going to be buggy-- there AREN'T ANY PROBLEMS moving from
2000 to 2005-- so it's a complete non-issue.

That's pretty despicable, Aaron.

Note to readers: this is Aaron Kempf, not Larry Linson.


John W. Vinson[MVP]

 

[aaron.kempf]

it's pretty despicable that you're promoting a database engine THAT
DOESNT WORK WELL ENOUGH FOR A SINGLE RECORD AND A SINGLE USER.

Did my post threaten the life of anyone?
NO

Do your crappy ass database systems threaten LIVES?

they sure do


What happens when the little MDB that you wrote for the ambulance
company; what happens when you have unexplained locking errors and a
data entry clerk doesn't know how to counteract record locking
messages?

What happens?

You stupid MDB script kids are the ones that are pretty despicable
Fat lazy retards are too mentally challenged to learn Access Data
Projects.

Punk ass kids spreading mis-information


-Aaron