Updatable Query

[cvegas]

I moved the backend to a SQL server and now this is not updatable from my
Access 2K front end.

Can this be made updatable?

SELECT dbo_tblOrders.[POD Sent], dbo_tblCustomers.Company,
dbo_tblOrders.Custref, dbo_tblOrders.[Notify With POD], dbo_tblOrders.OrderID
FROM (dbo_tblOrders INNER JOIN dbo_tblCustomers ON dbo_tblOrders.CusNum =
dbo_tblCustomers.CusNum) INNER JOIN dbo_tblBillToInfo ON
dbo_tblCustomers.BillToId = dbo_tblBillToInfo.BillToID
ORDER BY dbo_tblOrders.[POD Sent] DESC , dbo_tblCustomers.Company;

I want to be able to delete the data in the POD Sent field.

Thanks in advance for any light you may be able to shed on this for me.

 

[John Spencer]

Did you add a field of the type TIMESTAMP to the table when you moved it to SQL
server? If not, do so.

Then relink the table and try the update. (Actually, you may not need to do the
relink, but it doesn't hurt to do so.)

 

[cvegas]

Originally I did not have a timestamp field in any of the tables I imported
into SQL
It appears that this is now working but the following query is not updatable
even after I added a timestamp field to both tables, deleted the link and
relinked them.

I apparently am missing something when it comes to access using assess table
rather than sql tables when it comes to this.

Your help with this is truly appreciated.


SELECT dbo_tblCustomers.CusNum, dbo_tblCustomers.Active,
dbo_tblCustomers.LastUpdate, dbo_tblBillToInfo.Active,
dbo_tblCustomers.Company, dbo_tblCustomers.Address, dbo_tblCustomers.Suite,
dbo_tblCustomers.City, dbo_tblCustomers.State, dbo_tblCustomers.RateSet,
dbo_tblCustomers.Zip, dbo_tblCustomers.Contact, dbo_tblCustomers.[Contact
EMail], dbo_tblCustomers.[Toll Free], dbo_tblCustomers.Phone,
dbo_tblCustomers.Fax, dbo_tblCustomers.[Additional Info],
dbo_tblCustomers.[Credit Hold], dbo_tblCustomers.[POD EMail],
dbo_tblCustomers.[Same Location], dbo_tblCustomers.BillToId,
dbo_tblBillToInfo.BillToID, dbo_tblBillToInfo.Company,
dbo_tblBillToInfo.Address, dbo_tblBillToInfo.Suite, dbo_tblBillToInfo.City,
dbo_tblBillToInfo.State, dbo_tblBillToInfo.Zip, dbo_tblBillToInfo.[Toll
Free], dbo_tblBillToInfo.Phone, dbo_tblBillToInfo.Fax,
dbo_tblBillToInfo.[Billing Contact], dbo_tblBillToInfo.[Contact EMail],
dbo_tblBillToInfo.[Billing Info], dbo_tblCustomers.APCode,
dbo_tblCustomers.NeedsAuth, dbo_tblCustomers.NotificationPref
FROM dbo_tblCustomers INNER JOIN dbo_tblBillToInfo ON
dbo_tblCustomers.BillToId = dbo_tblBillToInfo.BillToID
WHERE (((dbo_tblCustomers.Active)=1))
ORDER BY dbo_tblCustomers.CusNum;

I was trying to update the customers fax number and it tells me that this in
not updatable?

Did you add a field of the type TIMESTAMP to the table when you moved it to SQL
server? If not, do so.

Then relink the table and try the update. (Actually, you may not need to do the
relink, but it doesn't hurt to do so.)

I moved the backend to a SQL server and now this is not updatable from my
Access 2K front end.

Can this be made updatable?

SELECT dbo_tblOrders.[POD Sent], dbo_tblCustomers.Company,
dbo_tblOrders.Custref, dbo_tblOrders.[Notify With POD], dbo_tblOrders.OrderID
FROM (dbo_tblOrders INNER JOIN dbo_tblCustomers ON dbo_tblOrders.CusNum =
dbo_tblCustomers.CusNum) INNER JOIN dbo_tblBillToInfo ON
dbo_tblCustomers.BillToId = dbo_tblBillToInfo.BillToID
ORDER BY dbo_tblOrders.[POD Sent] DESC , dbo_tblCustomers.Company;

I want to be able to delete the data in the POD Sent field.

Thanks in advance for any light you may be able to shed on this for me.

 

[John Spencer]

Is there a primary key defined in the tables?

Is the primary key included in the query?

For testing purposes, try the query without dbo_tblBillToInfo table and see
if you can update the data in the query. If that fails, thne you have a
starting point. If it works, then we can tentatively eliminate this table
as being the source of this problem.

Originally I did not have a timestamp field in any of the tables I
imported
into SQL
It appears that this is now working but the following query is not
updatable
even after I added a timestamp field to both tables, deleted the link and
relinked them.

I apparently am missing something when it comes to access using assess
table
rather than sql tables when it comes to this.

Your help with this is truly appreciated.


SELECT dbo_tblCustomers.CusNum, dbo_tblCustomers.Active,
dbo_tblCustomers.LastUpdate, dbo_tblBillToInfo.Active,
dbo_tblCustomers.Company, dbo_tblCustomers.Address,
dbo_tblCustomers.Suite,
dbo_tblCustomers.City, dbo_tblCustomers.State, dbo_tblCustomers.RateSet,
dbo_tblCustomers.Zip, dbo_tblCustomers.Contact, dbo_tblCustomers.[Contact
EMail], dbo_tblCustomers.[Toll Free], dbo_tblCustomers.Phone,
dbo_tblCustomers.Fax, dbo_tblCustomers.[Additional Info],
dbo_tblCustomers.[Credit Hold], dbo_tblCustomers.[POD EMail],
dbo_tblCustomers.[Same Location], dbo_tblCustomers.BillToId,
dbo_tblBillToInfo.BillToID, dbo_tblBillToInfo.Company,
dbo_tblBillToInfo.Address, dbo_tblBillToInfo.Suite,
dbo_tblBillToInfo.City,
dbo_tblBillToInfo.State, dbo_tblBillToInfo.Zip, dbo_tblBillToInfo.[Toll
Free], dbo_tblBillToInfo.Phone, dbo_tblBillToInfo.Fax,
dbo_tblBillToInfo.[Billing Contact], dbo_tblBillToInfo.[Contact EMail],
dbo_tblBillToInfo.[Billing Info], dbo_tblCustomers.APCode,
dbo_tblCustomers.NeedsAuth, dbo_tblCustomers.NotificationPref
FROM dbo_tblCustomers INNER JOIN dbo_tblBillToInfo ON
dbo_tblCustomers.BillToId = dbo_tblBillToInfo.BillToID
WHERE (((dbo_tblCustomers.Active)=1))
ORDER BY dbo_tblCustomers.CusNum;

I was trying to update the customers fax number and it tells me that this
in
not updatable?

Did you add a field of the type TIMESTAMP to the table when you moved it
to SQL
server? If not, do so.

Then relink the table and try the update. (Actually, you may not need to
do the
relink, but it doesn't hurt to do so.)

I moved the backend to a SQL server and now this is not updatable from
my
Access 2K front end.

Can this be made updatable?

SELECT dbo_tblOrders.[POD Sent], dbo_tblCustomers.Company,
dbo_tblOrders.Custref, dbo_tblOrders.[Notify With POD],
dbo_tblOrders.OrderID
FROM (dbo_tblOrders INNER JOIN dbo_tblCustomers ON dbo_tblOrders.CusNum
=
dbo_tblCustomers.CusNum) INNER JOIN dbo_tblBillToInfo ON
dbo_tblCustomers.BillToId = dbo_tblBillToInfo.BillToID
ORDER BY dbo_tblOrders.[POD Sent] DESC , dbo_tblCustomers.Company;

I want to be able to delete the data in the POD Sent field.

Thanks in advance for any light you may be able to shed on this for me.

 

[cvegas]

Deleting the BillToInfo table solved the problem. I might have just used an
existing query on this form as I'm not sure why the BillTo info was even
necessary here.

I better go back and re create this query and rename it something else, for
this form, just in case I use it somewhere else.

Thanks for all the help

Is there a primary key defined in the tables?

Is the primary key included in the query?

For testing purposes, try the query without dbo_tblBillToInfo table and see
if you can update the data in the query. If that fails, thne you have a
starting point. If it works, then we can tentatively eliminate this table
as being the source of this problem.

Originally I did not have a timestamp field in any of the tables I
imported
into SQL
It appears that this is now working but the following query is not
updatable
even after I added a timestamp field to both tables, deleted the link and
relinked them.

I apparently am missing something when it comes to access using assess
table
rather than sql tables when it comes to this.

Your help with this is truly appreciated.


SELECT dbo_tblCustomers.CusNum, dbo_tblCustomers.Active,
dbo_tblCustomers.LastUpdate, dbo_tblBillToInfo.Active,
dbo_tblCustomers.Company, dbo_tblCustomers.Address,
dbo_tblCustomers.Suite,
dbo_tblCustomers.City, dbo_tblCustomers.State, dbo_tblCustomers.RateSet,
dbo_tblCustomers.Zip, dbo_tblCustomers.Contact, dbo_tblCustomers.[Contact
EMail], dbo_tblCustomers.[Toll Free], dbo_tblCustomers.Phone,
dbo_tblCustomers.Fax, dbo_tblCustomers.[Additional Info],
dbo_tblCustomers.[Credit Hold], dbo_tblCustomers.[POD EMail],
dbo_tblCustomers.[Same Location], dbo_tblCustomers.BillToId,
dbo_tblBillToInfo.BillToID, dbo_tblBillToInfo.Company,
dbo_tblBillToInfo.Address, dbo_tblBillToInfo.Suite,
dbo_tblBillToInfo.City,
dbo_tblBillToInfo.State, dbo_tblBillToInfo.Zip, dbo_tblBillToInfo.[Toll
Free], dbo_tblBillToInfo.Phone, dbo_tblBillToInfo.Fax,
dbo_tblBillToInfo.[Billing Contact], dbo_tblBillToInfo.[Contact EMail],
dbo_tblBillToInfo.[Billing Info], dbo_tblCustomers.APCode,
dbo_tblCustomers.NeedsAuth, dbo_tblCustomers.NotificationPref
FROM dbo_tblCustomers INNER JOIN dbo_tblBillToInfo ON
dbo_tblCustomers.BillToId = dbo_tblBillToInfo.BillToID
WHERE (((dbo_tblCustomers.Active)=1))
ORDER BY dbo_tblCustomers.CusNum;

I was trying to update the customers fax number and it tells me that this
in
not updatable?

Did you add a field of the type TIMESTAMP to the table when you moved it
to SQL
server? If not, do so.

Then relink the table and try the update. (Actually, you may not need to
do the
relink, but it doesn't hurt to do so.)

cvegas wrote:

I moved the backend to a SQL server and now this is not updatable from
my
Access 2K front end.

Can this be made updatable?

SELECT dbo_tblOrders.[POD Sent], dbo_tblCustomers.Company,
dbo_tblOrders.Custref, dbo_tblOrders.[Notify With POD],
dbo_tblOrders.OrderID
FROM (dbo_tblOrders INNER JOIN dbo_tblCustomers ON dbo_tblOrders.CusNum
=
dbo_tblCustomers.CusNum) INNER JOIN dbo_tblBillToInfo ON
dbo_tblCustomers.BillToId = dbo_tblBillToInfo.BillToID
ORDER BY dbo_tblOrders.[POD Sent] DESC , dbo_tblCustomers.Company;

I want to be able to delete the data in the POD Sent field.

Thanks in advance for any light you may be able to shed on this for me.