Linked MS Access table Not Updateable 2

[mcelligott]

I have linked a table from one MS Access 97 database to another MS Access 97 database. Unfortunately the linked table is not updateable in the other database. I have been reading through the forum for any insight. I have made sure that the original table has a primary key and it has several indexes. The database itself does not have a password, it is setup with user-level security. Am I missing something here?

Thank you in advance for any and all anticipated help.

 

[KenReay]

Hi

Have you checked:

User trying to update the table has read/write/create priviledges in the folder where the databse containing the linked table resides?

If you have usr level security that the (Access) logged on user has permissions to update the Table?

The table is an Access (jet) table, rather than a none updateable 'external' type such a text file?

The update is being applied directly to the table, rather than via a query Which is itself not updatable (eg an aggregate query)

If you are opening the table via a recordset in code, you have not openned using setting which make it non updateable?


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mcelligott]

Thank you for your response Ken,

I have tried most of what you have responded with. Others I am not sure.

You wrote:
User trying to update the table has read/write/create priviledges in the
folder where the databse containing the linked table resides?

* Yes I do

You wrote:
If you have usr level security that the (Access) logged on user has permissions to update the Table?

* I should have probably clarified this one. The database the linked table originates from has user-level security. The new database does not have security at all. It uses the system.mdw

You wrote:
The table is an Access (jet) table, rather than a none updateable 'external' type such a text file?

* The originating table is definitely in an Access database

You wrote:
The update is being applied directly to the table, rather than via a query which is itself not updatable (eg an aggregate query)

* It is being applied directly to the table

You wrote:
If you are opening the table via a recordset in code, you have not openned using setting which make it non updateable?

* I'm not opening the table via code.

Any further suggestions on what I can try?

Thanks,

Bob

 

[KenReay]

Hi

Well, I am not an expert on Access security, but if the Security has been correctly applied to the databse in which the linked table resides, I would seriously suspect that is your problem.

Sorry I cannot be of more assistance. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GDGarth]

MCElligot:

You wrote:"* I should have probably clarified this one. The database the linked table originates from has user-level security. The new database does not have security at all. It uses the system.mdw"

There's your problem, I think. When you start up Access without a login, you are logged in as "Admin". Make sure your secured database allows the default "Admin" user to read, write, edit, and delete records.


You really should implement security on the other database, so you can set permissions appropriately.

 

[annsolomon]

Bob, forgive me if this is insulting 'cause it's not meant to be. But you didn't say how/what you were trying to update in the original database tables. Are you trying to update the design (which you can't do) or just the data? If it's just the data, can you give us the specific error message you're getting and tell us a little more about your "original" database. Have you assigned passwords to the users? Do you still have an Admin user account set up?

GDGarth makes a good point here. If your original db still has the Admin user and you've adjusted those permissions that could be causing your grief. Give us some more info and maybe we can help you out a bit more.

 

[mcelligott]

Hi annsolomon,

No offense taken at all. I am attempting to update data only.

The original database still has the admin account in it and still has all the permissions. The error message down in the bottom bar of the Access window states "This recordset is not updatable"

Here is the what I am trying to do. The original database has all the employee information (name, address, phone #, password, etc). The new database will be used to fill out frequently used forms at our facility (alarm violations, vacation request, etc). The employee will fill out the form in the new database and when they want to submit it, it will require them to enter their password (essentially signing the form). I want to give the employee the ability to change their own password at any time. That is the part that is hanging me up. The reason I am using the password field in the table instead of the mdw is because the person that will be maintaining employee information (such as when they get hired or when they resign or are terminated) is not an administrator. I have been asked by management not to give her that kind of security.

Hope that helps clarify the situation.

Thanks for any help you can offer.

Bob

 

[GDGarth]

If you use access security, any user can change their own password whenever they want.

The administrator can delete a user's password, but to change it, they have to log on as that user (in Access '97).

 

[annsolomon]

Bob, a few more questions some of which may not be relevant based on answers to the others:

Are you trying to edit data via a query or one of your forms when you get the "this recordset is not updatable" message? You replied "It is being applied directly to the table" to Ken's question ("The update is being applied directly to the table, rather than via a query which is itself not updatable (eg an aggregate query)&quot and that still has me kinda bugged. Did you mean that literally? If so, do you get the same message if you try to update the table from within the original database?


What is/are the key (joined) field name(s)?

What control are you using on the form to allow a user to change their password? (e.g., command button that opens frmChangePassword or a text box where you want the user to be able to overwrite data)

Are the forms in your new/second database built on queries or did you create them directly from the tables?

On your forms, is the join field taken from the linked table or related tables in your new database?

 

[mcelligott]

First, to answer GDGarth - Management does not want the woman that keeps the employees information in the database to be able to change/add/delete anyone from the database security. They are having trust issues with her. But since she does add/update/delete people in the employee information file I figured it would eliminate me from having to add and delete the employees in the database security since she already does it in the employee information file. I figured why duplicate the work she is already doing.

Hi again annsolomon,

I have not tried to edit the password from any queries or forms as yet. I have been attempting to give everyone a password to start with and have been trying to do it directly in the linked table. That is when I get that not updatable message. I have not attempted to give people passwords in the original database.

The primary key in the table is their IBM/Badge #. Always unique. Old #'s are never reused in the system.

I have not connected the field to any forms as yet. Just haven't gotten that far. I was still attempting to give the employees their original password when this problem arose and didn't get to their interface yet.

This is the only table in the second database that is based on the original database. All other tables that will be used will be resident in the new database.

Hope that helps. I would like to thank everyone for all their assistance in this matter. It is greatly appreciated.

Bob

 

[annsolomon]

Hi Bob. Since you can't update data directly in the linked table I wonder if, perhaps, the original database file is set to READ ONLY. Check the file attributes and be sure nobody has opened the original database EXCLUSIVE or READ ONLY.

 

[mcelligott]

Ok, checked the original database and it is not ready only and not set for exclusive.

Note: I just ran the documenter on the linked file and found something interesting. The Def. Updatable is False (I am guessing that means that it can not be updatable).

Please, any more suggestions???????

Thanks,
Bob

 

[annsolomon]

Yup, that's exactly what it means.

Can you update the password field in the original table (not the linked table)? Can you update ANY other fields in the linked table?

Did you check your original database (the back end) to be sure it's not Read Only or some other such thing (File, Database Properties, General tab)?

Do you have the latest copy of Jet and the latest Office service pack?

 

[BKoops]

You may experience a typical MS Access 97 problem. Check this page

http://support.microsoft.com/default.aspx?scid=kb;en-us;165352

if I'm right. I'm currently experiencing the exact same problem and I'm looking for an easy patch.

 

[mcelligott]

I can update any field in the original database but am unable to update any field in the linked table of the new database.

I have checked the file to make sure it is not read only.

To be honest, I really don't know what version of Jet or Office Service pack that we have. I will have to check on that with the IS staff.

BKoops - I read the page that you sent. That seems like a lot of work for something that should already work. I understand why you are looking for an easier patch. I does however sound like the problem I am having. I sure hope that I don't have to resort to that.

Bob

 

[annsolomon]

I just visited the site BKoops referenced. Interesting paper. Is that what happened to you? (i.e., You had a form open with the Recordset Type set to Snapshot?)

I noticed that the date on the paper is 11/15/2000 so perhaps MS has corrected the bug in one of their service releases. (You can find out what version of Office you're running by going to Help, About MS Access on the main menu.) They released service pack 3 a couple of months ago and I'd say it's worth a try. After all, you have nothing to lose at this point right?

Good luck.
Ann

 

[mcelligott]

Just for giggles, I tried linking a table from a different database to the new one. That too has user-level security on it. However, I was able to update the table without a problem. It must have something to do with that particular database. The only difference I can figure out is that I did not create that database (the one I can not update the linked table) and I did create the other database (the one I can update the linked table).

Any further ideas of things I can check for? Pleeeeaaassee.

Thanks,

Bob

 

[annsolomon]

Have you tried this yet? Join the workgroup that is associated with the original database. Then open your second database (the one with the link in it) and see if you can update data in the linked table.

Ann

 

[mcelligott]

Ann,

That's it!!!!! That is the missing piece. I can now update any and all fields in the linked table!!!!

YOU ARE THE BEST. Thanks for all your help.

Bob

 

[annsolomon]

I'm so glad you finally got it. I know from experience how frustrating that situation had to be.

Ann

P.S. Thanks for the star!