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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


.

Relevant Pages

  • Re: Problem using SP as record source with Access2002 & SQL Server 7
    ... The GUID can be used as an automatically allocated UniqueIdentifier but an ... The Resync command is a little tricky; look at the previous posts in this ... > understand the difference between the Record Source property and the ... >> tables (and preferably, single field primary key). ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Problem using SP as record source with Access2002 & SQL Server 7
    ... in the underlying record source ERROR Message' article. ... the Resync Command property refer to the Input Parameters property? ... the GUID should only be used in association with ... >> an automatic primary key without using Unique Identifier? ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Problem using SP as record source with Access2002 & SQL Server 7
    ... Create another stored procedure with the same Select statement but with the ... This clause should only take into account ... the primary key and the stored procedure to have only one argument, ... > the Resync Command property refer to the Input Parameters property? ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Detail Field in Access Report not showing correct name of data
    ... > Add the "yet another table" to your report's record source. ... > MS Access MVP ... >> textual name of the field on my Access report. ... >> the Primary Key and the name of the field. ...
    (microsoft.public.access.reports)
  • What is the good way to design this form
    ... I have a form that is bound to a table as a record source. ... table mostly includes a primary key and foreign keys from other tables. ... links to tblMajor and minorID links to tblMinor. ...
    (microsoft.public.access.forms)