RecordsetNot Updateable

[Mark A. Sam]

John,

The query runs much better now. Thanks for the correction. The properties
of the form are all ok. I have been using it for years. Also it was
updateable prior to me changing the recordsource. It was also updateable
for a while with Kens version. So something else is going on.

God Bless,

Mark

Unless you are working with a HUGE recordset (millions of records) and you
have no indexes on the fields in the joins or in the where clause that
shold not take as long as it does.

Oh, the datediff in the where clause is going to really slow things down
since it will have to do the calculation for every row (record) returned
by the join

Reformulate that so the calculation only takes place ONE time. I think
you will need something like the following which will return records that
have an OrdDate that is after 10 December 2007:

WHERE [OrdDate] > DateAdd('d',-701,Date()))

If the query is updateable and the form is not then you need to determine
why the form won't allow updates.
== Are you sure you are using the query as the forms record source?
== Is the form's Allow Edits property set to Yes? Also check Allow
Additions and Allow Deletions properties if you want to add/delete
records.
== Is the form's Recordset Type set to Dynaset or Dynaset(Inconsistent
Updates)?

== IF none of those apply, is it possible that the form is corrupted? IF
so, check out
Try Allen Browne's article on Recovering from Corruption at:
http://allenbrowne.com/ser-47.html

Also you take a look at Tony Toews' site
http://www.granite.ab.ca/access/corruptmdbs.htm

Jerry Whittle, Microsoft Access MVP has a white paper in a Word document
named Fix Corrupt Access Database towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

I made the adjustment you recommended. The Recordsource SQL is created
through VBA and I made the necessary adjustment and a debug.print to
access the actual recordsource being created:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND ((Customers.Custid) In
(SELECT CustomerAccess.Custid FROM CustomerAccess WHERE
CustomerAccess.UserID = 2)))
ORDER BY [Order Entry Header].ordJob;

The form was not updateable, but I pasted the above statement a query and
it was updateable, however, it took close to 3 minutes to open the
recordset. Ken's version worked for a while then stopped.

God Bless,

Mark

I am guessing that having three tables in your query may have made it
not updateable. Perhaps you can remove the customerAccess table from the
query.

SELECT [Order Entry Header].*
, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers
ON [Order Entry Header].ordCustID = Customers.Custid)
WHERE DateDiff('d',[ordDate],Date())<=701
AND
Customers.Custid In (SELECT
CustomerAccess.Custid FROM CustomerAccess
WHERE CustomerAccess.UserID = 2)
ORDER BY [Order Entry Header].ordJob;

Another possibility is to try you returning the primary keys of all
three tables in the SELECT clause. I am assuming that all three tables
have primary keys.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Mark A. Sam wrote:
Ken,

I imported the tables to the front end set the primary keys and
appropriate indexes and it didn't help. So the rules out an SQL Server
connection issue. Here is the recordsource when the form is open:
Does it look correct?

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=2) AND ((Customers.Custid) In (SELECT
CustomerAccess.Custid FROM CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

I tested this in a query, which returned a not updateable recordset.

Thank you for your input and God Bless,

Mark

I am not familiar with using ACCESS as front-end to SQL Server, so I'm
guessing here. Perhaps something in your connection to the SQL Server
database has changed and this has made your recordsets to that
database nonupdateable?
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Ken,

This is bizarre. Suddenly the recordset (with the revised query) is
not updateable. It has to be something beside the query design. I'm
pretty sure the original query should be been updateable. But this
is among a string of bizarre behaviors I am encountering.

God Bless,

MarkAs


Forgot a . in my original reply to you, Mark. Here is corrected
query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally
contained two tables, [Order Entry Header] and [Customers] with an
inner join. It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit
the records by Customer and UserID. This table simply shows which
users can access which customers. The function getUserID tells
which User who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to
me. These are linked SQL Server tables, so I thought maybe
something in the field properties or indexes may be the problem, so
I imported the [CustomerAccess] table to see if a local table would
fix it, but it didn't.

Any siggestions on how to make this recorset updateable is
apprecated.

God Bless,

Mark A. Sam

 

[Mark A. Sam]

Same result. If I open it as a query, it is updateable, but in the form it
isn't. I'll have to play around with forms and see if the form is
corrupted.

Let's try one more variation:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid)
WHERE (((DateDiff('d',[ordDate],Date()))<=701)
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess
WHERE ((CustomerAccess.UserID)=getUserID()))))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken, Your first version worked for a while then it didn't. John gave me
another variation which is updateable in a query (takes a long time to
open though), but not in a form. A lot of bizarre things are occuring in
this db as well as one of my own. If I said what they were you would all
run me out of town.

 

[Mark A. Sam]

The problem is the form. I used the recordset in a new form and it was ok.
I should have tried that earlier.

Same result. If I open it as a query, it is updateable, but in the form
it isn't. I'll have to play around with forms and see if the form is
corrupted.

Let's try one more variation:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid)
WHERE (((DateDiff('d',[ordDate],Date()))<=701)
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess
WHERE ((CustomerAccess.UserID)=getUserID()))))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken, Your first version worked for a while then it didn't. John gave me
another variation which is updateable in a query (takes a long time to
open though), but not in a form. A lot of bizarre things are occuring in
this db as well as one of my own. If I said what they were you would
all run me out of town.