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

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


.

Loading