Problem using SP as record source with Access2002 & SQL Server 7

[Amir]

Hi,

I have a an Access2002 project working with SQL Server 7 (MDAC 2.7).
In that project I have a continuous form, in which I use a simple SELECT SP
as the record source.

1. The problem is that each time I add a new record by typing at the form's
last empty record (the one reserved for adding new records), just as I
finish the typing and the record is added to the DB, I get error #2107 (The
value entered does not meet the validation rule defined for the field or
control).
At this point the new record is not shown in the form until the moment I
click a button which does me.requery, then it IS shown.

The form and the controls in it do not have any restrictions in it's WHERE
clause, nor validation rules. The more weird thing is that after I requery
the form by clicking a control which runs DoCmd.Requery, the record which I
have just added IS shown in the form.

I thought about catching the event which causes it to disappear (e.g. some
kind of event that is after the addition to the DB) and run a docmd.requery
then, but didn't succeed in doing so. That's also a very 'dirty' way of
dealing with that problem, which I prefer not to use.

2. In addition, I sometimes get a #Name? value instead of the records data
in the form. I think it happens after I update a record but i'm not sure (I
can't check that right now since server is at work). That problem is solved
too when I run a docmd.requery.

Record source in the form is just the name of the function: SP_MAIN_FORM and
does not use EXEC to operate. At first I've used parameters in the SP(placed
in the InputParameters). At the beginning I thought that this might cause
the problem, but the problem persists even when I clear the input parameters
and totally remove them also from SP, and removing the whole WHERE clause
which is using them. The records continue to 'disappear and appear after I
requery' even with no WHERE clause nor input parameters.

How can I solve that?
Is there any chance that these problems occur due to the fact that I'm
working with SQL Server 7, and not SQL Server 2000?

Regards,
Amir.

 

[Sylvain Lafontaine]

First, you shouldn't use the prefix sp_ for your stored procedures. This
prefix has a special meaning for SQL-Server and might lead to subtle bugs.

Second, some type of fields like the Bit, the Real (instead of Float) or the
GUID might give you trouble when used with ADP. You should also check for
the presence of triggers. These are a common source of problems for ADP,
too.

A good way to learn more about these kinds of problem is to use the
SQL-Server Profiler; as you will see what ADP is trying to do when inserting
a new record.

Finally, setting the Unique Table property and creating a Resync Command
might solve your problem if you don't find any other solution.

Of course, I assume that you have defined a primary key for all of your
tables (and preferably, single field primary key).

 

[Amir]

Hi,

Is GUID the UniqueIdentifier data type? If so, is there any way of having an
automatic primary key without using Unique Identifier? (Should I allocate
numbers by code?)

I don't think there are any triggers around there since I didn't create any
trigger myself, and it's a new database.

I have set the unique table property, but the Resync Command is empty. What
should I put into the Resync command? Should I put the name of the stored
procedure which I've put in the record source? If so, I can't understand the
difference between the Record Source property and the Resync Command
property (I've read the Access help about it, but still can't understand the
difference).

I remember that such things like I did here used to work without resync
command (but not with continuous forms though, now that I'm thinking about
it, but I don't know if that matters).
Does the fact that I use continuous forms makes the resync command needed?

I have defined a one field primary key for each one of the tables, so that
shouldn't be the problem.

Thanks for your help.
Kind Regards,
Amir.

 

[Sylvain Lafontaine]

The GUID can be used as an automatically allocated UniqueIdentifier but an
integer column with the Identity property set to true would be an easier
way. In my opinion, the GUID should only be used in association with
Replication.

The Resync command is a little tricky; look at the previous posts in this
newsgroup on December 9 «
Switching a Form's Recordsource at RunTime to a SP w/ a Parameter » for a
full description with an example.

 

[Amir]

I've just read that article and also the 'data doesn't satisfy the criteria
in the underlying record source ERROR Message' article.

What I've understood is that I should put the SELECT sentence from the
stored procedure into the resync command (I think I understand the logic:
This is needed so that Access can 'filter' the record itself, without the
need to retrieve all the records from the server just to know 'which record
should be shown and which should not, according to the original SELECT
sentence, which is stored at the server, but Access does not 'know'. Am I
right?).

If so, In case I use Input Parameters, should the SQL sentence I put into
the Resync Command property refer to the Input Parameters property?
(Otherwise, how should Access 'know' which records to show? After all, it
depends on the InputParameters (for example, in case they are part of the
WHERE clause in the stored procedure)).
If the last statement is true, how should I refer to the InputParameters
property in the Resync Command? Would you write a sample SQL command for a
resync command, refering the Input Parameters property? Otherwise, how
should I do this?

Regards,
Amir.

 

[Sylvain Lafontaine]

Create another stored procedure with the same Select statement but with the
exception of the Where clause. This clause should only take into account
the primary key and the stored procedure to have only one argument, the id
for this primary key. The ResyncProperty should then be:

MyStoreProcedure_ForResync ?

ADP will replace the ? with the ID (value of the primary key) of the current
record.

 

[Amir]

Thank you very much!
I'll try that, and I'll be here in about a week and a half to tell you if
succeeded.

Kind Regards,
Amir.

 

[Amir]

OK, I've decided to try this at home with the Northwind database.
I've installed a brand new copy of SQL Server 2000 with the original
Northwind DB, and created the following stored procedure

CREATE PROCEDURE ALL_CUSTOMERS AS
SET NOCOUNT ON
SELECT * FROM dbo.CUSTOMERS
RETURN
GO

In addition, I've created the folowing stored procedure for the resync
command:
CREATE PROCEDURE ALL_CUSTOMERS_RESYNC @CustomerID nchar AS
SET NOCOUNT ON
SELECT * FROM dbo.CUSTOMERS
WHERE dbo.CUSTOMERS.CustomerID = @CustomerID
RETURN
GO

Then I've created a continuous form using Access Wizard, which has the
following properties:
(Default View: Continuous Forms)
Record Source:ALL_CUSTOMERS
Unique Table: CUSTOMERS
Resync Command: ALL_CUSTOMERS_RESYNC ?
InputParameters: (this property is currently blank since I don't have any
parameters in the ALL_CUSTOMERS stored procedure)

The CustomerID field in table Customers is a primary key.

When I open the form and change something in one of the records, then click
on another record for the change to save I get the following error message:
The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record
source.

When I click the help, it says that if I want to still not use a table or
view as the record source (for example, if I want to use stored procedure,
which is my case), I need to upgrade to a newer version of Microsoft SQL
Server.

Now, This is happening when i'm using Access2003 and MS SQL 2000 (No service
packs installed).
At work we are using Access2002 and MS SQL 7!
Do I have to upgrade to SQL Server 2000 with service packs in order for that
to work?

Regards,
Amir.

 

[Amir]

OK, I think that's working now. I've changed the Resync Command property
from:
ALL_CUSTOMERS_RESYNC ?

To:
"(Call ALL_CUSTOMERS_RESYNC (?))"

Could that be?

 

[Sylvain Lafontaine]

The most likely explanation is that you forgot to refresh the
SP/Views/Functions window by using the File -> Refresh when this window is
displayed or to close/reopen the application (same effect as the refresh
command).

Instead of a resync command, you could also try adding a TimeStamp field in
your table (and again, don't forget to refresh the Tables window).

Another possibility would be to use the canonical form for ODBC:

{ call ALL_CUSTOMERS_RESYNC ? } or
{ call ALL_CUSTOMERS_RESYNC (?) }

but I'm don't remember if this will work with (all?) OLEDB providers.

The only way to be sure is to use the SQL-Server profiler (or to add
debugging code such as adding a column to the queries that will return a
different value in the case of the resync command).

 

[Sylvain Lafontaine]

For SQL-Server 2000, it's also a good idea to install the latest service
pack.

 

[Amir]

Hi Sylvain,
I don't understand: what is the purpose of the methods you've mentioned?
(I mean, I think it's already working. Am I doing anything wrong?)

Thank you very much,
Amir.

 

[Sylvain Lafontaine]

If you create something or make a change to an already existing table or a
SP without refreshing the appropriate window or restarting the application,
ADP won't notice that something has changed and will give you
bad/false/strange results.

 

[Amir]

'bad/false/strange' results - Do you mean cases like when a user changes a
record to a state where it shouldn't more be shown in the current form (due
to a WHERE clause, for example), but it is still shown since the user hasn't
yet clicked the control which runs a docmd.requery command?
Such cases are OK in my Access project as long as that record disappears
when the user clicks the 'requery' control.
In addition, in my kind of Access project, there are no actions in the form
that the user can choose to do (for example by clicking one of the
controls), which are relying on having certain values in the records'
fields, which could become 'impossible' to do due to changes in those
records, if you know what I mean (This could have been a problem to try to
engage such actions on records which are shown in the form since the user
hasn't yet clicked the 'requery' button, but their actual new values prevent
doing these kind of actions, but as I mentioned, I think that this is
impossible in my specific Access project).
(I hope I'm not wrong with that...)

I appreciate your help very much!

Kind Regards and a happy new year!
Amir.

 

[Sylvain Lafontaine]

No, I'm not talking about these cases.

Old results that are still displayed because the underlying record has not
been requeried is not a strange result by itself.

Happy New Year to you, too! (and to everyone else!)

 

[Amir]

What do you mean by odd results?

No, I'm not talking about these cases.

Old results that are still displayed because the underlying record has not
been requeried is not a strange result by itself.

Happy New Year to you, too! (and to everyone else!)

 

[Sylvain Lafontaine]

Not « odd », « old ».

 

[Amir]

I meant the 'strange' word in your previous post
What kind of bad/false/strange results might be given?

Regards,
Amir.

 

[Sylvain Lafontaine]

Not finding the Resync Command that you have just created could be the first
example.

 

[Amir]

This explains why I was keeping getting messages about not finding the
resync command, followed by messages like 'This record has been changed by
another user since you started editing it', similar to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;282382
http://support.microsoft.com/kb/295225/

I've solved that by using unbound instead of bound controls, and by using
stored procedures with SQL INSERT INTO and UPDATE sentences instead (and
using seperate forms for inserting new records an updating them, which is a
bit less comfortable). I think it works OK, but that required lots of work
and a bit less comfortable because of the use of seperate forms.
Now I understand that maybe I could have solved that by just placing
DoCmd.Requery and DoCmd.Refresh commands in the AfterUpdate event of the
form (even though I havn't tried that). Anyway, I think I won't make all the
way back now..