#Error in records (can't edit)

[sharpnova]

There is a table in a database I'm maintaining.

One of the fields is a Text field. When I look at the datasheet view, i see the value #Error for every entry

When I select the field to try to edit it, I get the following error popup:

"Reserved error (-1517); there is no message for this error."


There are currently users using the database. But I don't see how this could have corrupted all the values in the table. The table is only accessed via a record source for a dropdown. And this is only a read access.

 

[vbaInet]

Is this a linked table?

Have you tried moving the database objects into a new database shell?

How are your users sharing the db?

 

[sharpnova]

Yes it's a linked table.

I can't move around the database objects since the database is currently in use and therefore not allowing me to make any changes.

This is why I'm trying to figure out the problem so that when the users are not using the application I can make fixes that will stick.

It's a database on a shared drive which many users use.

The table is a simple list of values that populate a dropdown list as its record source.

 

[vbaInet]

It's a database on a shared drive which many users use.

This is where your problem stems from.

You should have a Back End (i.e. just your tables) and Front Ends on each user's machine.

It's causing corruption and doing weird things to your database. You will need to read about Splitting a database.

 

[sharpnova]

I don't believe this is the case. We have dozens of databases that work this way. The backend and frontend are split into two databases. But the frontend is shared for multiple users to use at once.

There are dozens of tables in this database and this is the only table that has this issue.

I agree using one database as a shared front end is not ideal, but it is not the reason this is happening.

Any other ideas?

 

[boblarson]

I don't believe this is the case.

Sorry to break the news to you but it is the case.

We have dozens of databases that work this way. The backend and frontend are split into two databases. But the frontend is shared for multiple users to use at once.

That you haven't had problems until now is simply just a COINCIDENCE. The way you have described is not a "best practices" way of doing things. You should read this:

to understand more of why it is important
http://www.kallal.ca/Articles/split/index.htm

There are dozens of tables in this database and this is the only table that has this issue.

There is at least one CORRUPT record in your table. The means to remove it is this:

1. Save a copy of the database tables in case something else goes wrong.
2. Run Compact and Repair
3. This should clear the PRIMARY KEY of the record so that there is a blank. Scan down the table until you find this record.
4. Select that record and delete it.
5. You will need to re-enter that data.

I agree using one database as a shared front end is not ideal, but it is not the reason this is happening.

And your qualifications for stating such a ridiculous claim are???

I would like to point out that vbaInet is correct and you would be hard pressed to find any Access MVP who would disagree with him. And I know several of them have intense knowledge of the actual inner workings of Access.

Any other ideas?

Yeah, fix it and then give each user their OWN COPY of the frontend.

 

[sharpnova]

Again. I agree having one shared front end is not ideal. But having multiple users use the database was not the issue.

I'm by no means an expert and when it comes to Access am far less knowledgeable than pretty much anyone on these forums. But there was just no reason for the shared use to effect the table this way.

I think people can get entrenched in an original point of view in a way that stops reasonable discourse in its tracks. I tried to explain that your original (and further pushed) diagnosis of the problem was invalid, but that wasn't listened to because you were too hung up on the "good practices" side of things.

This is a fairly sleek/minimalist database and this particular lookup table was setup exactly like a few others that were operating fine.

The actual issue was this:

On the form the control was locked and inactive. After swapping these, the issue was still present. (This was at a time when no users were in the database)

When I relinked the table, the fields that said #Error, were restored to their proper values. The database was usable with no problems for anyone after this fix.

I now grant permission to a moderator to mark this thread as **RESOLVED** or however that is done here.

 

[vbaInet]

When I relinked the table, the fields that said #Error, were restored to their proper values. The database was usable with no problems for anyone after this fix.

I was expecting that to be a TEMPORARY fix but I can guarantee you that at some point in the future (be it near or far away), the same or bigger problem will happen.

Have a read of that link by Albert Kallal (MVP) because it explains why what you've been advised is necessary.

It's not the first time we've seen this happen and it only happens when the set-up is the same or similar to yours.

Good luck with the rest of your project!

 

[nanscombe]

I don't remember coming across that sort of issue in 9 years of deploying Access FE/BE databases across over thirty Novel Netware / CITRIX servers.

As far as I remember, our company wouldn't allow us to deliver anything like that to individual PCs, everything had to centralised and locked down.

I guess we must have been lucky.

 

[sharpnova]

I hope we get the time in the future to enhance our databases to be deployed properly.

Maintaining other people's applications is so much worse than generating one from scratch. (When they're written poorly)