Access 2007 Unable to update linked SQL 2005 table 1

[dpelizzari]

I have two SQL 2005 tables, ISD SACD and ISD Lease, both tables have a primary key assigned (all bit values have been set to 0, and do not allow nulls). I have created a form using a join between these two tables in Access. I am prepopulating two fields in ISD Lease to ensure the join will work properly (serial number and model number, which are used in join). I have full access to both tables, but I am beating my head against a wall when I try to update any of the fields. I get the ever elusive "recordset is not updateable" error. It would be delightful to be able to update this information, HELP?!

 

[dhookom]

Did you set the default of the bit fields to 0?

What is the exact relationship?

Why are you editing a parent and child table in the same record source rather than using a form and subform setup?

Duane
Hook'D on Access
MS Access MVP

 

[dpelizzari]

in my first statement, I stated that all bit fields were set to zero, and did not allow nulls. The relationship between the two tables is a one to one relationship. The first table (ISD SACD) is rebuilt twice a week to ensure data integrity, so the data is flushed and recreated. The second table (ISD Lease) was created so we could track changes (this table is not flushed), this is the only table being updated via the Access form. I attempted to use a form and subform, but my understanding of that process is it requires a one to many relationship, which I do not have. I was thinking this morning that possibly I could use an SQL view instead, link the tables in SQL and update via Access.

 

[PHV]

Have a look here:

http://support.microsoft.com/kb/148581

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

dpelizzari,
I do understand that you set all bit fields to zero. You never mentioned setting the default to zero. There is a difference.

Main forms and subforms don't have to be one to many. They can be one to one.

Duane
Hook'D on Access
MS Access MVP

 

[dpelizzari]

sorry, dhookom, I should have been clearer. I am going to review what PHV posted (when I get time to blink).

 

[dpelizzari]

PHV, thank you for the article, what I gleaned from it is that I need to cerate a specific join: "You must specify an explicit INNER or OUTER JOIN between the tables. Joins created implicitly in the WHERE clause of the SELECT statement are not updatable." I think this is where my failure lies. Would I just simply add a join to the beginning of the SQL statement then?

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpelizzari]

I was just about to post it, PHV, since I do have a join statement see below:

SELECT [dbo_ISD SACD1].epend_dt, [dbo_ISD SACD1].epasset_status, [dbo_ISD SACD1].[rStatus*], [dbo_ISD SACD1].rSite, dbo_ISD_Lease2.Luser, [dbo_ISD SACD1].smsComputerName, [dbo_ISD SACD1].smsLastLoggedOnUserName, [dbo_ISD SACD1].SMSBiosManufacturer, [dbo_ISD SACD1].[smsSerial Number], [dbo_ISD SACD1].smsModel, [dbo_ISD SACD1].OS, [dbo_ISD SACD1].SP, [dbo_ISD SACD1].ADOU, [dbo_ISD SACD1].adname, [dbo_ISD SACD1].adtype, [dbo_ISD SACD1].addescription, [dbo_ISD SACD1].[rSerial Number], [dbo_ISD SACD1].[rPart Number], [dbo_ISD SACD1].epend_dt, [dbo_ISD SACD1].epprod_desc, [dbo_ISD SACD1].epserial_id, [dbo_ISD SACD1].epallocation, [dbo_ISD SACD1].eptotal_item_rent, [dbo_ISD SACD1].epasset_status, [dbo_ISD SACD1].epprod_id, [dbo_ISD SACD1].rSite, [dbo_ISD SACD1].[rName*], [dbo_ISD SACD1].[rDRN Number], [dbo_ISD SACD1].[rLease Expiry], [dbo_ISD SACD1].[rStatus*], [dbo_ISD SACD1].[rDisposal Date+], [dbo_ISD SACD1].[rOwnership Type], [dbo_ISD SACD1].rzassetuserloginName, [dbo_ISD SACD1].[rNotes Log], [dbo_ISD SACD1].Department, [dbo_ISD SACD1].AssetType, [dbo_ISD SACD1].[System Role], dbo_ISD_Lease2.Date_updated, dbo_ISD_Lease2.Needed, dbo_ISD_Lease2.Condition, dbo_ISD_Lease2.Ticket_num, [dbo_ISD SACD1].AssetType, dbo_ISD_Lease2.Ticket_Created, dbo_ISD_Lease2.Ticket_Error, [dbo_ISD SACD1].region, [dbo_ISD SACD1].BU, dbo_ISD_Lease2.[re-lease_cost], dbo_ISD_Lease2.Luser, [dbo_ISD SACD1].Leasor, dbo_ISD_Lease2.Notes, dbo_ISD_Lease2.epserial_id, dbo_ISD_Lease2.epprod_id
FROM dbo_ISD_Lease2 INNER JOIN [dbo_ISD SACD1] ON (dbo_ISD_Lease2.epprod_id = [dbo_ISD SACD1].epprod_id) AND (dbo_ISD_Lease2.epserial_id = [dbo_ISD SACD1].epserial_id)
WHERE ((([dbo_ISD SACD1].epend_dt)>=[Forms]![Parameters]![StartDate] And ([dbo_ISD SACD1].epend_dt)<=[Forms]![Parameters]![EndDate]) AND (([dbo_ISD SACD1].epasset_status)<>"inactive") AND (([dbo_ISD SACD1].rSite) Like Nz([Forms]![Parameters]![Site Code],"*")) AND (([dbo_ISD SACD1].epserial_id) Is Not Null) AND (([dbo_ISD SACD1].epprod_id) Is Not Null));

 

[PHV]

Does access recognize both primary keys ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpelizzari]

yes, Access shows the PK on both linked tables.

 

[PHV]

To be sure, the PK in both tables is on (epserial_id,epprod_id), don't it ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpelizzari]

the primary keys on both tables are ID, which is a unique sequential number, different in both tables. Should the PK be changed to epserial_id & epprod_id?

 

[PHV]

You talked about an one-to-one relationship.
Usually such relation implies a common PK.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpelizzari]

the two tables to not share a common PK. This could be accomplished by concatenating the serial_id field and prod_id fields (sadly, the vendor doesn't believe in a unique serial number for their hardware, so we need to manufacture one using the serial number and product ID.)

 

[PHV]

You don't need to concatenate anything.
Simply use a composite index.

primary keys on both tables are ID
So, what happens if you add both ID in the SELECT list ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpelizzari]

I added both ID fields, and get the same results when attempting to update. note, the ID fields are not equal.

 

[PHV]

So, I'd at least create an unique index on(epserial_id,epprod_id) for both tables and then refresh the linked tables.

I still don't understand why you didn't make the PK common for a 1-to-1 relation ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpelizzari]

The reason the current primary keys aren't a 1-1 is that the ID field. You did however lead me somewhere else. I found that in the table ISD SACD1, there are null values in those two fields. Is it possible that since I am trying to join on those tables, and they are null, this is causing the recordset to not be updateable?

 

[PHV]

Apart ID, does access recognize another unique indices for both tables ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886