Re: Why is this Not Updatable?

What is happening is that when I pull us my form Forwarders which is has the
record source as the sql query I posted earlier called [Customer By Number]
and try to update any data I get an error message in the bottom left side of
the screen that says "This Record Set is not updatable"

It does not appear that I have any relationships set up in my Access front
end and I know I have not done any relationships on the sql server or if this
is even possible. As I said I am pretty new at this and I am taking over
from someone's elses work.

I would imagine my next step would be to try to recreate the relationships
that are in the Access 2 K production front end in the Access 2K test front
end that I am working on now and having the problem?



"Chris2" wrote:

>
> "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.
>
>
>
.

Loading