Record set is not updatable (1 Viewer)

Coldsteel · Aug 31, 2012

Hello,

I am trying to edit my query and keep getting the error Record set is not updatable. Is any one able to assist me with this issue

Here is my sql

SELECT Inv.[ID], Inv.[Area], Inv.[Hosting], Inv.[Type], Inv.[Ser_num]
FROM Lease INNER JOIN Inv
ON Lease.Serial = Inv.Ser_num;

Thanks

Trevor G · Aug 31, 2012

This may be down to the relationships, can you pop a stripped down version into your thread will be more useful to look at it.

MSAccessRookie · Aug 31, 2012

Coldsteel said:
Hello,

I am trying to edit my query and keep getting the error Record set is not updatable. Is any one able to assist me with this issue

Here is my sql

SELECT Inv.[ID], Inv.[Area], Inv.[Hosting], Inv.[Type], Inv.[Ser_num]
FROM Lease INNER JOIN Inv
ON Lease.Serial = Inv.Ser_num;

Thanks

Trevor is right about the issues with relationships. Another thing to consider is whether the Table Inv has any Fields other than [ID], [Area], [Hosting], [Type], and [Ser_num]. If there are additional Fields, and any of them are required, then the Dataset might not be updateable.

-- Rookie

Coldsteel · Sep 1, 2012

Thanks for the help, Here is my database

Coldsteel · Sep 1, 2012

Here it is thanks

Coldsteel · Sep 2, 2012

I have tried to looking on my properties settings but I have not yet found the cause of why access won't let me update my records

vbaInet · Sep 3, 2012

You need to enter some meaningful data so we know what the field represents and how they should relate. The main issue you're having is that Access doesn't understand the relationships which could be a result of your Primary Keys not being well defined.

Coldsteel · Sep 4, 2012

Thanks, for the help. Here is some more data.

Trevor G · Sep 4, 2012

I would advice that you learn about normilazation, the database you have attached has no relationships (in the relationship window), when I explore the design of your tables you are setting the text field for the serial numbers to 255 (to big for a serial number) characters not setting anything as a required field so the relations can only show indeterminate.

I would suggest you look at how to create a relational database, you can find plenty of demos on YouTube.

vbaInet · Sep 4, 2012

Trevor G said:
I would advice that you learn about normilazation, the database you have attached has no relationships (in the relationship window), when I explore the design of your tables you are setting the text field for the serial numbers to 255 (to big for a serial number) characters not setting anything as a required field so the relations can only show indeterminate.

I would suggest you look at how to create a relational database, you can find plenty of demos on YouTube.

Here are some good references:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
http://support.microsoft.com/kb/283878

Coldsteel · Sep 6, 2012

Thanks, for the help and the references.
I have been studying them, and I have a question. Basically I have a excel report I receive from another dept with incomplete data the table I call Lease. The field SerialNum is always missing some data, but it gets updated as the excel sheets come. Basically the lease data has how much I pay on pcs and the Inv table is what I have on hand. What I want is basically as I upload new leases I want to be able to match the 2 tables by serial numbers and to be able to be able to update both as needed with my simple query. I have put a one to many relationship, to help but I cant get how to match the two tables by the serial number constantly and to stop the Record set is not updatable error I keep getting. Here is my new updated database with some sample data. Any more help would be appreciated.

Coldsteel · Sep 6, 2012

I have tried the different ways to create the table relationships but I not had any success allowing the query to be updated is there a bypass feature in access?

vbaInet · Sep 8, 2012

Coldsteel said:
I have tried the different ways to create the table relationships but I not had any success allowing the query to be updated is there a bypass feature in access?

There's no bypass feature of an integral part of relational databases. Your table structure and purpose just needs to be thought through.

Perhaps start by explaining what the tables should be for.

Coldsteel · Sep 8, 2012

Thanks for the help,

Basically, I want to import the data to a tbl called Lease and then compare it to the Inv table by serial numbers and be able to edit both on the query.

Table Lease will always be imported data, it comes form a different dept.
the field SerialNum (will have serial numbers and sometimes may not have anything in the field.)
the field Money (will be the rental cost of the item)

Table Inv is what Inventory we have on hand
the field Area (Is the location of the Item)
the field Hosting (Is the Host site of the Item)
the field Type (The Item Description)
the field Ser_num (Serial Number of the Item, there will always be a serial number on this field)
the field Lease (Is what I tried to make a relationship with the Lease table)

vbaInet · Sep 8, 2012

Then what you want is a subform for Inv and the parent form will be bound to Lease, linked via the Serial number.

Coldsteel · Sep 8, 2012

Thanks,
That's a great idea, I just built a form and subform, is there a way to make it in data sheet format as a continuous line

vbaInet · Sep 8, 2012

You can have the subform in Datasheet View but the parent form has to be a Single Form. If you want to change the subform to appear as a Datasheet, you can do so in the Default View property of the form.

Pat Hartman · Sep 9, 2012

You started a new thread on this topic. Please don't do that or at least have the courtesy to tell us.

Record set is not updatable (1 Viewer)

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

Attachments

Registered User.

AWF VIP

Registered User.

Attachments

Registered User.

AWF VIP

Registered User.

Attachments

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Super Moderator

Similar threads

Users who are viewing this thread