'Record does not exist in list', then 'Record already exists' messages

[TOP]

I'm the network guy here; pretty clueless when it comes to Access and DBs.
I'm not sure the best way to present the problem, so I'm going to throw a
bunch of stuff at you guys and you tell me what further information I need
to provide.

We had an Access DB designed and built for us a number of years ago. Users
are now having trouble entering new 'Producers' into the Project forms. When
they try to enter a name into the Produce field they receive the message
"[Producer name] is not a contact in this list, do you want to add this
contact to the list?" They click Yes and immediately get the message
"[Producer name] already exists with the title Producer. Cannot add a
duplicate contact with this title."

The Project form links to tables in the DB. In Design View I can see which
tables are being referenced. For the Producer field I cannot see a table. It
shows a ruler toolbar with a scrollbar underneath. But when entering data
into the field I am able to select names from the drop-down list, which
contains names from the Contacts table. The Project form also has fields for
Director and Cast, which also link to the contacts table, and those are
working fine as far as I know. (Not all the same names show up in the
drop-down for the Producer, Director, and Cast field. I think this is
because the Contact table has columns for Type (person or company) and Title
(Producer, Director, etc...) and the drop-downs must be filtered for title.)

One other thing, When I look at the Contact table, the highest record number
is 26235. When I look at the Contact form, it says record 1 of 25676.
Looking at the Table, I see the name of the producer they're trying to
enter; he's record 26234. However, an actor is listed as record 26231, and I
have no problem entering him in the Cast field of this project.

I'm also getting a ton of application-defined or object-defined errors when
trying to enter this Producer's name, or close the window after failing.
Most often the error # is 3058, but I've also seen 3022, 2148 and a few
others.

I've tried deleting the record of the producer from the Contacts table, but
that doesn't work, either.

Bottom line: Why would I get the two conflicting messages (record doesn't
exist, duplicate record exists) when trying to enter the new Producer.

Thanks in advance

 

[Klatuu]

First, record numbers have no meaning in Access. Record numbers only apply
to the current open recordset, so depending on filtering and sorting, the
relative position of a record will change. So that is not an issue.

How long since a compact and repair has been done on this database? I would
suggest you try that first.

Access applications are usually installed in two parts. The Front End and
the Back End. The front end contains all the forms, code, querys, reports,
etc and the Back End contains all the data. The Back End resides on a shared
folder or file server and each user has a copy of the Front End on their own
computer. Each of these files has an mdb file extension. The Front End
obtains data from the Back End through a process called Linking. This is
done from the Front End using the Linked Table Manager to tell the
application where the data resides.

That is the correct installation. You will find some cases where everything
is in one large mdb. You will also find cases where it has been properly
split, but evey user is sharing the same Front End. Either of these
configurations is prone to corruption.

Here is what you should do first.

Determine the configuration.
See what file the users are opening to start the application. This will be
either the Front End or the whole database, meaning it has not been split.
Open the application. Often developers will keep users out by manipulating
the available menus, etc. While you open the app, hold down the Shift Key.
This will bypass any start up routines (the majority of the time). Once you
can get into the database select Tools, Database Utilities, Linked Table
Manager. Now you will be able to see the path to the data tables. That will
be the Back End database. If you get a message saying there are no linked
tables, that means the database is not split.

Next, get every user to close their application.
First (after being sure we have a good backup), we will compact and repair
the Back End. Tools, Database Utilities, Compact and Repair Database.

Depending on the size, it may take a while. Note, before you do that, check
the size of the Back End mdb. There is a 2GB size limit to an Access
Database.

Once this completes, Do the same for each user's Front End database. After
that is done, use the Linked table manager to refresh the links. Tools,
Database Utilities, Linked Table Manager. The click Select All and Ok.

Let us know if this helps any.

I'm the network guy here; pretty clueless when it comes to Access and DBs.
I'm not sure the best way to present the problem, so I'm going to throw a
bunch of stuff at you guys and you tell me what further information I need
to provide.

We had an Access DB designed and built for us a number of years ago. Users
are now having trouble entering new 'Producers' into the Project forms. When
they try to enter a name into the Produce field they receive the message
"[Producer name] is not a contact in this list, do you want to add this
contact to the list?" They click Yes and immediately get the message
"[Producer name] already exists with the title Producer. Cannot add a
duplicate contact with this title."

The Project form links to tables in the DB. In Design View I can see which
tables are being referenced. For the Producer field I cannot see a table. It
shows a ruler toolbar with a scrollbar underneath. But when entering data
into the field I am able to select names from the drop-down list, which
contains names from the Contacts table. The Project form also has fields for
Director and Cast, which also link to the contacts table, and those are
working fine as far as I know. (Not all the same names show up in the
drop-down for the Producer, Director, and Cast field. I think this is
because the Contact table has columns for Type (person or company) and Title
(Producer, Director, etc...) and the drop-downs must be filtered for title.)

One other thing, When I look at the Contact table, the highest record number
is 26235. When I look at the Contact form, it says record 1 of 25676.
Looking at the Table, I see the name of the producer they're trying to
enter; he's record 26234. However, an actor is listed as record 26231, and I
have no problem entering him in the Cast field of this project.

I'm also getting a ton of application-defined or object-defined errors when
trying to enter this Producer's name, or close the window after failing.
Most often the error # is 3058, but I've also seen 3022, 2148 and a few
others.

I've tried deleting the record of the producer from the Contacts table, but
that doesn't work, either.

Bottom line: Why would I get the two conflicting messages (record doesn't
exist, duplicate record exists) when trying to enter the new Producer.

Thanks in advance

 

[TOP]

Klatuu,

Thanks for the response. The problem with adding a new producer to the form
was reported to me over a month ago. That night I did a compact/repair on
the database (which exhausted my store of DB knowledge) the way you
suggested: I had everyone log off, then compacted and repaired. I told
everyone (about 15 regular users) to give it a 10-minute test-run the next
day and report any problems--specifically trying to add producers to the
project form. Yesterday someone told me they "still can't add producers." So
while it's been a month since I last compacted/repaired, I did it the day
the problem was reported, but it didn't help.

It looks like you guessed correctly that our application was built poorly
: ) From your description I'd say it was split, but everyone is sharing the
same Front End. All the tables are listed in the Linked Table Manager, and
are linked to the DB, but users create a shortcut of the MDB from the shared
folder and copy that to their desktops to access the DB. (There is one
companydata.mdb and one company.mdb as the front end.)

This being the case, is it necessary or even possible to compact and repair
each user's front end? I did the compact/repair from a workstation, and
since everybody's using the same front end, I figured it would apply for
everyone?

Thanks again

First, record numbers have no meaning in Access. Record numbers only
apply
to the current open recordset, so depending on filtering and sorting, the
relative position of a record will change. So that is not an issue.

How long since a compact and repair has been done on this database? I
would
suggest you try that first.

Access applications are usually installed in two parts. The Front End and
the Back End. The front end contains all the forms, code, querys,
reports,
etc and the Back End contains all the data. The Back End resides on a
shared
folder or file server and each user has a copy of the Front End on their
own
computer. Each of these files has an mdb file extension. The Front End
obtains data from the Back End through a process called Linking. This is
done from the Front End using the Linked Table Manager to tell the
application where the data resides.

That is the correct installation. You will find some cases where
everything
is in one large mdb. You will also find cases where it has been properly
split, but evey user is sharing the same Front End. Either of these
configurations is prone to corruption.

Here is what you should do first.

Determine the configuration.
See what file the users are opening to start the application. This will
be
either the Front End or the whole database, meaning it has not been split.
Open the application. Often developers will keep users out by
manipulating
the available menus, etc. While you open the app, hold down the Shift
Key.
This will bypass any start up routines (the majority of the time). Once
you
can get into the database select Tools, Database Utilities, Linked Table
Manager. Now you will be able to see the path to the data tables. That
will
be the Back End database. If you get a message saying there are no linked
tables, that means the database is not split.

Next, get every user to close their application.
First (after being sure we have a good backup), we will compact and repair
the Back End. Tools, Database Utilities, Compact and Repair Database.

Depending on the size, it may take a while. Note, before you do that,
check
the size of the Back End mdb. There is a 2GB size limit to an Access
Database.

Once this completes, Do the same for each user's Front End database.
After
that is done, use the Linked table manager to refresh the links. Tools,
Database Utilities, Linked Table Manager. The click Select All and Ok.

Let us know if this helps any.

I'm the network guy here; pretty clueless when it comes to Access and
DBs.
I'm not sure the best way to present the problem, so I'm going to throw a
bunch of stuff at you guys and you tell me what further information I
need
to provide.

We had an Access DB designed and built for us a number of years ago.
Users
are now having trouble entering new 'Producers' into the Project forms.
When
they try to enter a name into the Produce field they receive the message
"[Producer name] is not a contact in this list, do you want to add this
contact to the list?" They click Yes and immediately get the message
"[Producer name] already exists with the title Producer. Cannot add a
duplicate contact with this title."

The Project form links to tables in the DB. In Design View I can see
which
tables are being referenced. For the Producer field I cannot see a table.
It
shows a ruler toolbar with a scrollbar underneath. But when entering data
into the field I am able to select names from the drop-down list, which
contains names from the Contacts table. The Project form also has fields
for
Director and Cast, which also link to the contacts table, and those are
working fine as far as I know. (Not all the same names show up in the
drop-down for the Producer, Director, and Cast field. I think this is
because the Contact table has columns for Type (person or company) and
Title
(Producer, Director, etc...) and the drop-downs must be filtered for
title.)

One other thing, When I look at the Contact table, the highest record
number
is 26235. When I look at the Contact form, it says record 1 of 25676.
Looking at the Table, I see the name of the producer they're trying to
enter; he's record 26234. However, an actor is listed as record 26231,
and I
have no problem entering him in the Cast field of this project.

I'm also getting a ton of application-defined or object-defined errors
when
trying to enter this Producer's name, or close the window after failing.
Most often the error # is 3058, but I've also seen 3022, 2148 and a few
others.

I've tried deleting the record of the producer from the Contacts table,
but
that doesn't work, either.

Bottom line: Why would I get the two conflicting messages (record doesn't
exist, duplicate record exists) when trying to enter the new Producer.

Thanks in advance

 

[Klatuu]

If they are just using the Shortcut to open the front end on the server, then
a compact and repair on that mdb will take care of everyone.
How many users are sharing the front end and what is the estimated maximum
concurrent users? I am wondering if some record locking is going on. Could
be that a user is leaving a record open and another user can't do an update.
Doesn't have to be the same record if the locking scheme is not set
correctly, just has to be on the same page. I would look into how record
locking is set up.

Shared front ends have a tendancy to blot. I would suggest you turn on
Compact On Close. (Tools, Options, General tab, about 2/3 down on left side).
This will cause the front end to compact when it is closed. If multipe
users have it open, it will not do the compact until the last user closes it.
Compact on Close doesn't seem to work on back ends. You have to manually
compact them. The aren't really "opened" in the pure sense of the word.

It would be advisable, if you can, to have each person have copy of the
front end on their own desk top. This will reduce network traffic.

It is odd that only producers can't be entered, though. You might suspect a
coding problem.

One other thing you might try on both mdbs is a decompile. The decompile is
non supported and should be used with caution. Here is a site with info on
that:

http://www.granite.ab.ca/access/decompile.htm

Now, if it is not a record locking, code, or bloating problem, it could be
you have some corruption going on in your database. Here is a way to fix
that.

Create a new mdb. Copy every object in the old database into the new
database. If the back end is done correctly, there should be nothing but
tables in it. Try using the new database to see if you get better results.

If you get through all this and are still having problems, I suggest you
engage the services of a good Access professional to help resolve your
problem.

Post back and let me know how it goes or if you have more questions.

Klatuu,

Thanks for the response. The problem with adding a new producer to the form
was reported to me over a month ago. That night I did a compact/repair on
the database (which exhausted my store of DB knowledge) the way you
suggested: I had everyone log off, then compacted and repaired. I told
everyone (about 15 regular users) to give it a 10-minute test-run the next
day and report any problems--specifically trying to add producers to the
project form. Yesterday someone told me they "still can't add producers." So
while it's been a month since I last compacted/repaired, I did it the day
the problem was reported, but it didn't help.

It looks like you guessed correctly that our application was built poorly
: ) From your description I'd say it was split, but everyone is sharing the
same Front End. All the tables are listed in the Linked Table Manager, and
are linked to the DB, but users create a shortcut of the MDB from the shared
folder and copy that to their desktops to access the DB. (There is one
companydata.mdb and one company.mdb as the front end.)

This being the case, is it necessary or even possible to compact and repair
each user's front end? I did the compact/repair from a workstation, and
since everybody's using the same front end, I figured it would apply for
everyone?

Thanks again

First, record numbers have no meaning in Access. Record numbers only
apply
to the current open recordset, so depending on filtering and sorting, the
relative position of a record will change. So that is not an issue.

How long since a compact and repair has been done on this database? I
would
suggest you try that first.

Access applications are usually installed in two parts. The Front End and
the Back End. The front end contains all the forms, code, querys,
reports,
etc and the Back End contains all the data. The Back End resides on a
shared
folder or file server and each user has a copy of the Front End on their
own
computer. Each of these files has an mdb file extension. The Front End
obtains data from the Back End through a process called Linking. This is
done from the Front End using the Linked Table Manager to tell the
application where the data resides.

That is the correct installation. You will find some cases where
everything
is in one large mdb. You will also find cases where it has been properly
split, but evey user is sharing the same Front End. Either of these
configurations is prone to corruption.

Here is what you should do first.

Determine the configuration.
See what file the users are opening to start the application. This will
be
either the Front End or the whole database, meaning it has not been split.
Open the application. Often developers will keep users out by
manipulating
the available menus, etc. While you open the app, hold down the Shift
Key.
This will bypass any start up routines (the majority of the time). Once
you
can get into the database select Tools, Database Utilities, Linked Table
Manager. Now you will be able to see the path to the data tables. That
will
be the Back End database. If you get a message saying there are no linked
tables, that means the database is not split.

Next, get every user to close their application.
First (after being sure we have a good backup), we will compact and repair
the Back End. Tools, Database Utilities, Compact and Repair Database.

Depending on the size, it may take a while. Note, before you do that,
check
the size of the Back End mdb. There is a 2GB size limit to an Access
Database.

Once this completes, Do the same for each user's Front End database.
After
that is done, use the Linked table manager to refresh the links. Tools,
Database Utilities, Linked Table Manager. The click Select All and Ok.

Let us know if this helps any.

I'm the network guy here; pretty clueless when it comes to Access and
DBs.
I'm not sure the best way to present the problem, so I'm going to throw a
bunch of stuff at you guys and you tell me what further information I
need
to provide.

We had an Access DB designed and built for us a number of years ago.
Users
are now having trouble entering new 'Producers' into the Project forms.
When
they try to enter a name into the Produce field they receive the message
"[Producer name] is not a contact in this list, do you want to add this
contact to the list?" They click Yes and immediately get the message
"[Producer name] already exists with the title Producer. Cannot add a
duplicate contact with this title."

The Project form links to tables in the DB. In Design View I can see
which
tables are being referenced. For the Producer field I cannot see a table.
It
shows a ruler toolbar with a scrollbar underneath. But when entering data
into the field I am able to select names from the drop-down list, which
contains names from the Contacts table. The Project form also has fields
for
Director and Cast, which also link to the contacts table, and those are
working fine as far as I know. (Not all the same names show up in the
drop-down for the Producer, Director, and Cast field. I think this is
because the Contact table has columns for Type (person or company) and
Title
(Producer, Director, etc...) and the drop-downs must be filtered for
title.)

One other thing, When I look at the Contact table, the highest record
number
is 26235. When I look at the Contact form, it says record 1 of 25676.
Looking at the Table, I see the name of the producer they're trying to
enter; he's record 26234. However, an actor is listed as record 26231,
and I
have no problem entering him in the Cast field of this project.

I'm also getting a ton of application-defined or object-defined errors
when
trying to enter this Producer's name, or close the window after failing.
Most often the error # is 3058, but I've also seen 3022, 2148 and a few
others.

I've tried deleting the record of the producer from the Contacts table,
but
that doesn't work, either.

Bottom line: Why would I get the two conflicting messages (record doesn't
exist, duplicate record exists) when trying to enter the new Producer.

Thanks in advance