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

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" <agamy@xxxxxxxxxxxx> wrote in message
news:%23a0RE5eDGHA.1028@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:uxIa0lYDGHA.2956@xxxxxxxxxxxxxxxxxxxxxxx
>> 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.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: http://cerbermail.com/?QugbLEWINF
>>
>>
>> "Amir" <agamy@xxxxxxxxxxxx> wrote in message
>> news:uFFi2RYDGHA.1476@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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" <sylvain aei ca (fill the blanks, no spam please)>
>>> wrote in message news:OoSbc$XDGHA.140@xxxxxxxxxxxxxxxxxxxxxxx
>>>> 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.
>>>>
>>>>
>>>> --
>>>> Sylvain Lafontaine, ing.
>>>> MVP - Technologies Virtual-PC
>>>> E-mail: http://cerbermail.com/?QugbLEWINF
>>>>
>>>>
>>>> "Amir" <agamy@xxxxxxxxxxxx> wrote in message
>>>> news:eEAR43XDGHA.984@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> 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" <sylvain aei ca (fill the blanks, no spam
>>>>> please)> wrote in message
>>>>> news:O1Jw2VXDGHA.1384@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>> 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).
>>>>>>
>>>>>> --
>>>>>> Sylvain Lafontaine, ing.
>>>>>> MVP - Technologies Virtual-PC
>>>>>> E-mail: http://cerbermail.com/?QugbLEWINF
>>>>>>
>>>>>>
>>>>>> "Amir" <agamy@xxxxxxxxxxxx> wrote in message
>>>>>> news:%23UEiiZRDGHA.3992@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>> 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.
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.