Re: Why is this Not Updatable?


"cvegas" <cvegas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0EDBF3AF-62F7-48DC-B4A8-22A97EBB85D2@xxxxxxxxxxxxxxxx
> I'm not quite sure what you are trying to tell me as I am pretty
new at this
> but I can tell you that the BillToId in the tblCustomer is not
the primary
> key. We have many customers that could be billed to the billing
address that
> is stored in the BillTo table where the BillToID is the primary
key.

cvegas,

Thanks for the update, I understand somewhat better now.

>
> This form is set up to display the Customers actual loacation,
city state,
> etc, etc and the Bill To Information such as where we send the
invoices,
> which might be a different location.

>From what I understand ("We have many customers that could be billed
to the billing address that is stored in the BillTo"), different
customers may have the same "Bill To Infomration".

Or:

tblCustomers
CusNum, BillToID
1, 1
2, 1
3, 1
4, 2
5, 2
6, 2


Example (SQL Server script):

-- Tables:

-- Please forgive the dates appended to the table names.

-- I am establishing the tables in SQL Server.

-- Note: I have added a foreign key constraint between the BillToID
columns.

CREATE TABLE tblBillToInfo_20051224_1
(BillToID INTEGER
,CONSTRAINT pk_tblBillToInfo_20051224_1
PRIMARY KEY (BillToID)
)
go

CREATE TABLE tblCustomers_20051224_1
(CusNum INTEGER
,BillToID INTEGER
,CONSTRAINT pk_tblCustomers_20051224_1
PRIMARY KEY (CusNum)
,CONSTRAINT
fk_tblCustomers_20051224_1_tblBillToInfo_20051224_1_BillToID
FOREIGN KEY (BillToID)
REFERENCES tblBillToInfo_20051224_1 (BillToID)
)
go

-- Sample Data:

INSERT INTO tblBillToInfo_20051224_1
(BillToID)
SELECT 1 UNION ALL
SELECT 2
go

INSERT INTO tblCustomers_20051224_1
(CusNum, BillToID)
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 5, 2 UNION ALL
SELECT 6, 2
go

-- End SQL Server script.


MS Access Linked Tables:

I linked the new SQL Server tables (menus, File > Get External Data
> Linked Tables > etc.).

When I selected both tables, MS Access 2000 successfully detected
the primary keys on the SQL Server tables.

When I added both tables to the relationship window, it did not
display the SQL Server foreign key constraint as an MS Access
relationship. I thought this might be a problem, however it turned
out differently (see below).


Query:

An abbeviated version of the originally posted query:

SELECT C1.CusNum
,C1.BillToID
,B1.BillToID
FROM dbo_tblCustomers_20051224_1 AS C1
INNER JOIN
dbo_tblBillToInfo_20051224_1 AS B1
ON C1.BillToId = B1.BillToID
ORDER BY C1.CusNum;

When I open this query in Data*** View, I can update CusNum and
C1.BillToID manually.

When I skip back to SQL Server, and run "SELECT * FROM
tblCustomers_20051224_1", it shows the changes to either column.


I apologize, but when I try to replicate your situation, I find that
I the query is updateable (I cannot update B1.BillToID, of course,
but that is another story).


You said, originally, that the query you posted was not updateable,
and I suspect that I am not doing exactly what you were doing.

What *exactly* was going on when this error happened and what
*exactly* was the entire text of the error message in question?

---------------

(Note: Remember that in my examples I am making assumptions about
the nature of your primary and foreign key constraints, and this
also may be causing what I am achieving to be different from what
you are achieiving.)


Sincerely,

Chris O.


.