Adding New Records to SQL Server

[JG]

I cannot Add new records to a table, although I can edit
and update a select query fromm the table with no
difficulties.
Within Access VBA
The table is opened thus:

Set rst = ConLabels.OpenRecordset("dbo.LABELS",
dbOpenDynamic, 0, dbOptimistic)

with rst
.addnew
:::::
.update
falls over at the update line, even with just one field
completed, with error no 3146 odbc call failed
Why can I edit and update but not add and update?

Any Ideas anyone

 

[Guest]

I have discovered how to trap the error messages thru KB
article 183278. - No nulls allowed as is the Primary Key.
In Access the 1y key was set by a sequential number
default. How do I obtain this functionality in SQL Server?

 

[Sylvain Lafontaine]

You must set the Identity property to True for the primary key column. The
"Identity" property is the same thing as the Access autonumber feature.

Also, instead of using dbOpenDynamic, you should use dbOpenDynaset in
conjonction with dbSeeChanges for the options (instead of 0); as this will
impose less stress on the server than the dbOpenDynamic type:

Set rst = ConLabels.OpenRecordset("dbo.LABELS", dbOpenDynaset, dbSeeChanges,
dbOptimistic)


S. L.

 

[Guest]

Many Thanks Sylvain
As a new user to these databases I find the documentation
is extensive but often it is difficult to see the wood
for the trees at first glance. Hence the usefulness of
these newsgroups.

JG