Access closes unexpectedly when creating new record in form over network

[Toby Young]

I have a MS Access DB on my computer which allows other
users to access database over a network. I do not
experience this problem on my computer, but on other's
computers, when they go to add a new record to a form,
access quits unexpectedly and prompts the user to back-up
the database. They do not get the same problem if the
data is added directly to the underlying table. I use the
built-in security feature in MS Access to prevent some
users from making changes.

The form contains 2 subforms and about 40 fields. I am
able to create a new record programatically using a query
run from another form to create the record.

The problem is on their computers only and is not
restricted to one single user account.

Any help would be greatly appreciated.

 

[Ronald Dodge]

A series of steps I would suggest to help resolve this issue.

First, if you haven't already, you need to split out your DB file to 2 DB
files, one as your Front End (FE), which has everything except for the
tables and data, and a Back End (BE), which has all of your tables and data
in it. This particular step is very highly suggested, cause with multiple
users in the same DB file, it can lead to corruption.

Second, be sure you have a working copy and a development copy of both, the
FE and BE DBs.

Third, Convert your working FE DB copy from MDB to MDE

Fourth, Save the FE DB file to a shared network file location that everyone
can get to

Fifth, Have a copy of the file copied onto each of the local PCs for those
users that will be using the FE DB file. Data will still transfer as needed
via the Linked Tables which is automatically setup, when you use the Split
DB utility under Tools>Database Utilities>Database Splitter...

Users would then open their own local copy of this FE DB file, which should
avoid a lot of the corruption type issues that would other wise take place
by multiple users opening up the same DB files. They should all still be
linked to the BE DB which is fine and shouldn't cause any issues so as long
as it does not exceed the 255 concurrent user limit.

If your forms are *UNBOUND*, then this chances are means, you will have more
work involved, thus one reason why I started setting up my DB files
separately right from the start as I can't use bound forms due to the
validation process of Access and most of my users are predominantly mouse
users with limited Keyboard usage.

 

[Toby Young]

Thank you for your help. I have implemented the changes
and I still get the same problem. I removed the
autonumber field from the form and now I don't have any
problem.

I use the autonumber field to link several underlying
tables which provide the subforms for this form. Should I
have set the database up differently.

I use autonumber from the parent table to link the two
child tables using long integer on these two tables.

Any help would be greatly appreciated.

 

[Ronald Dodge]

I haven't ran into this type of issue, but then I haven't went into full
blown test mode either, so this is something that I may have to deal with at
a later point of time. I have done some testings already with regards to
using the Long Integer format and AutoNumber, of which for the most part, I
haven't had any issues with it at this time. I don't know what more to
mention other than check to be sure that all of the references on their
systems are the same as what's on your system (goto VBA Editor,
Tools>References, and check the list in there to be sure that all checked
items are matching). Look for other differences between the users on their
systems and what's on your system. Have you tried to use your permissions
on their systems? There's a lot of various testings depending on what it is
that you looking for, that needs to be done to resolve issues.

However, given the issue that I have with bound forms not being so user
friendly for mouse users with the strict data validation checks (note, this
is not the same as Security issues, but they do overlap) that I require, I
have my forms unbound and using DAO to do all of the DB manipulation on the
BE DBs. In this DB setting up process that I'm going through, I have for
the must part used the following set of guidelines. There's some minor
differences in how I approached this cause of my customers being internal
customers, but the general steps still applies.

Seven Step Design Process of a DBMS



1) The Overall Design - From Concept to Reality

· Interview Users

· List Tasks

· Conceptualize The Design

· Create A Prototype For Customer To Get A Feel For

2) Report Design - Placing Your Fields

3) Data Design - What Fields Do You Have?

· Determine Information Of Each Report Individually

· Combine Data

4) Table Design And Relationships - Database Normalization

· 1NF - Eliminate Repeating Groups

· 2NF - Eliminate Redundant Data (Tables based on defined Primary
Key)

· 3NF - Eliminate Columns (Fields) not Dependent on the Key

· 4NF - Isolate Independent Multiple Relationships (Many to Many
Relationships)*

· 5NF - Isolate Semantically Related Multiple Relationships (X Many
Relationships)*

· Setup relationships based on Primary and Secondary Keys

5) Field Design (Validation)

· Design Field Properties

· Design Data Entry Rules

· Design Lookup Tables

· Create Test Data

6) Form Design - Input

· Design Data-Entry Screens

7) Automation Design - Menus

· Setup Command Buttons

· Setup Menus

· Setup Switchboard

· Setup Error-Checking Procedures

· Setup other miscellaneous automations



*For Examples of 4NF and 5NF, goto
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm8.html



Along with this, I also have a particular model mapped out as I'm in the
process of creating an MRP type system. The Guideline that I pasted into
this message above, I summarized from the book, "Access 2002 Bible" by Irwin
and Prague. That book hits up on a lot of the general type stuff for DB
programming, though I have a separate book for VBA programming in Access
2002.