Does table size make a difference in how well Access protects data

[jmillerWV]

I am having a problem that has been going on for several weeks now. I am
slowly trying to figure it out. the problem is that Access 2003 keeps
deleting records after they are entered. Quick view of database: running
Access 2k3 back end and frontends, 12 machines access the DB at any one time,
mixture of Win2k Pro and Win XP pro all patches and service packs installed
for all OS's as well as Access 2k3. Running Client/server set up controlled
by Small Business Server 2k3. database resides on a machine running XP Pro.
when new orders are entered into the system, the DB will tell the dataentry
person that "Update or cancel without add new record." then it delets the
record. My question is this: Does the size (number of fields) in a table have
an affect on the Databases operation?
My main table has 77 fields in it , there are 71 tables in the database
also in the main table there are 295,292 records the size of the database is
1,396,712KB. I am wanting to move to SQL but hesitate until I can solve this
Deleting problem.
Thanks in advance for any assistance. I apologize for the lengthy post.

 

[John W. Vinson]

I am having a problem that has been going on for several weeks now. I am
slowly trying to figure it out. the problem is that Access 2003 keeps
deleting records after they are entered.

I have never ONCE seen an Access database spontaneously delete records.

I've seen index corruption cause records to not be found; this can be
corrected by compacting. I've seen forms and code that delete records -
correctly on the basis of how the code was written but not what the
user/developer intended. I've seen users who were not aware that deleting
records in a query caused records in the table to be deleted... but again,
I've never seen Access deleting records on its own.

Quick view of database: running
Access 2k3 back end and frontends, 12 machines access the DB at any one time,
mixture of Win2k Pro and Win XP pro all patches and service packs installed
for all OS's as well as Access 2k3. Running Client/server set up controlled
by Small Business Server 2k3. database resides on a machine running XP Pro.
when new orders are entered into the system, the DB will tell the dataentry
person that "Update or cancel without add new record." then it delets the
record.

What's the context? What's the rest of the message? Is it from code in a form,
or a native Access message? If it's code, would you be willing to post the
code? Are these the only records being deleted?

My question is this: Does the size (number of fields) in a table have
an affect on the Databases operation?

Not in my experience.

My main table has 77 fields in it , there are 71 tables in the database
also in the main table there are 295,292 records the size of the database is
1,396,712KB.

77 fields is *very* wide, but I've heard of wider tables that worked
correctly. I'm assuming that you've normalized it as well as practical?

I am wanting to move to SQL but hesitate until I can solve this
Deleting problem.

If there is code or some other problem in the frontend causing the deletion,
then moving the data storage to SQL won't help.

Thanks in advance for any assistance. I apologize for the lengthy post.

John W. Vinson [MVP]

 

[Albert D. Kallal]

I am having a problem that has been going on for several weeks now. I am
slowly trying to figure it out. the problem is that Access 2003 keeps
deleting records after they are entered. Quick view of database: running
Access 2k3 back end and frontend

The above sounds correct, so we assume that the front ends are installed on
EACH computer. You might for reliability purposes ensure that it's an mde in
place of a mdb used for the front end.

for all OS's as well as Access 2k3. Running Client/server set up
controlled
by Small Business Server 2k3.

I'm not sure what you mean client server setup here, because access is a
file sharing system. you are not using client and server at this point time.

database resides on a machine running XP Pro.

Now that seems a bit strange, is there any reason why you don't have a
shared folder on the server? You do realize if that user reboots the
machine, has a freeze up or lockup you're going to have data lost at that
instant?

I guess it is a bit strange That you tell me you followed good procedures
and you always install the front end on each computer, and then you place
the back and on a very reliable machine. Now all the sun you're telling me
that you in fact have a small business server, but if not place the M.D. be
filed back and on the server? (I guess I'm a bit of a loss here as to why
the mdb back end is not placed on this machine?)

when new orders are entered into the system, the DB will tell the
dataentry
person that "Update or cancel without add new record." then it delets the
record. My question is this: Does the size (number of fields) in a table
have
an affect on the Databases operation?

I don't think sized to matters much in this case. The range you're talking
about in the 300,000 record range is NOT that larnge at alll.

Also it's not quite clear how users add records into a table. Is there some
code involved, or does the user press some button, or do they just open up a
form that's attached to some big huge table..and then moved to the end to
add records? (if there's some type of code involved, you might wanna take a
look at that).

also was was there any change to the system made that occur to the same time
this problem started to rear its head?

the size of the database is
1,396,712KB.

Now that file size does seem rather large, what is the file size after you
do a compact and repair? (and while we're at this how often do you run a
compact and repair on the back end database anyway?). It looks to me at
this point that this files not been compacted properly. perhaps you're
storing pictures inside of the database, and that's useally a bad
idea...but, that file size seems way to large for that many reocrds...

I am wanting to move to SQL but hesitate until I can solve this
Deleting problem.

I assume you're talking about moving the backend data to SQL server, and
continuing to use MS access as the front end. SQL server certainly more
reliable, but given the size of data the number uses you have a don't think
you're reached the point where you really have to move the back end part to
sql server (however the express editions of SQL server are free these days,
so they're certainly not a cost issue).

 

[jmillerWV]

Thanks for the response, The entire message is "Update or CancelUpdate
Without AddNew or Edit." it is simple a message box with an "OK" button.
After the user selects ok then it will come back and inform them "The Record
is Deleted" and we see "#Deleted" in each of the data fields.

one other thing that may go along with this, is that sometimes (less often
then the complete record delete) contents in 2 memo fields will get
"#deleted". It's not just the fact that the data is deleted but that it
places "#Deleted" into the field or record. If a person delets the info in a
field only the data is missing #Deleted is not entered into the field. Same
way with a record if I or anyone deliberately delete a record then the record
goes away and there is a hole in the ID column (auto numbering and key) but
no #deleted in each field. I compact and repair the db daily after eveyone
has left for the day. If I have to rebuild the database to insert the missing
records the the db gets compacted and repaired several times during the
rebuild process and at the end. I have tried to duplicate the deletions of
records but am unable to get it to happen.
Again thank you for the response.

 

[jmillerWV]

Thank you for your response. To help clarify some info you had a question on
I simple stated the client/server type network to show that we are not
running a simple network (peer to peer) with just file sharing. Yes I have
tried running the db on the SBS server but because of the need to work on it
so much I just thought that until I get the problems solved I would just keep
it on a seperate machine, for my ease of access. There have been no changes
made to the system other than moving the back end to Access 2k3. The db is
compacted and repaired daily after everyone has left. Also whenever I have to
rebuild the db inorder to fill in the missing records, I compact repair
several times while rebuilding and again after I am finished. I am not sure
what you mean by a mde versus a mdb used for the front end?

The file size mentioned is after a complete rebuild and compact and repair.
I have even compacted and reapired it twice in a row. There are no pictures
stored in the db. there are other large tables in our db that we have no
problems with because dataentery people do not enter data into them, these
are controlled by managers and they contain client info, pricing lists, and
other data used by the database for other purposes. Again thanks for the
response and I hope the additional information helps.

 

[Albert D. Kallal]

until I get the problems solved I would just keep
it on a seperate machine, for my ease of access.

ok...sounds good to me....

I am not sure
what you mean by a mde versus a mdb used for the front end?

A mde is a compiled and locked version of ms-access. All versions of access
can create a mde. I talk about mde files here:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[jmillerWV]

Again thank you for your response. I have taken a quick look at the link and
will study it in more depth this evening. Don't worry about my comment on mde
versus mdb as I was thinking of something else at the time I wrote that
comment. Again thanks for your assistance. I will let you know if the link
helps.