Adding Records to a Linked BE Table on a MS SQL Server

[Don]

We are in the process of upgrading our database from an Access FE/BE to an
Access FE and MS SQL Server BE. The FE successfully links to the MS SQL
Server BE in that data on the forms is being correctly displayed. However,
I have now run into a problem with some new ADO code that is suppose to add
a record to one of the linked tables in the BE. The 'AddNew' command (see
code snippet below) throws the following error:

Run-time error '-2147467259 (80004005)':
Invalid Operation

Which I am assuming means I can not do the AddNew on the recordset.
However, the test for support of AddNew returns true. What I have turned up
in searches so far point to a permission problems. But I have (supposedly)
full permissions on the MS SQL Server.

Any suggestions will be greatly appreciated!!

Thanks!

Don





Dim cnnDB As ADODB.Connection 'DB Connection
Dim rstSource As ADODB.Recordset 'Details Recordset
Dim bnTest As Boolean

Set cnnDB = CurrentProject.Connection
Set rstSource = New ADODB.Recordset

'tblNotes is a linked table which exists on the MS SQL Server
rstSource.Open "tblNotes", _
cnnDB, adOpenDynamic, _
adLockPessimistic, adCmdTableDirect

bnTest = rstSource.Supports(adAddNew)

rstSource.AddNew

rstSource!LinkID = Me!txtLinkID
rstSource!DateOpened = Now()
rstSource!User = CurrentUser()

 

[Dirk Goldgar]

We are in the process of upgrading our database from an Access FE/BE
to an Access FE and MS SQL Server BE. The FE successfully links to
the MS SQL Server BE in that data on the forms is being correctly
displayed. However, I have now run into a problem with some new ADO
code that is suppose to add a record to one of the linked tables in
the BE. The 'AddNew' command (see code snippet below) throws the
following error:

Run-time error '-2147467259 (80004005)':
Invalid Operation

Which I am assuming means I can not do the AddNew on the recordset.
However, the test for support of AddNew returns true. What I have
turned up in searches so far point to a permission problems. But I
have (supposedly) full permissions on the MS SQL Server.

Any suggestions will be greatly appreciated!!

Thanks!

Don





Dim cnnDB As ADODB.Connection 'DB Connection
Dim rstSource As ADODB.Recordset 'Details Recordset
Dim bnTest As Boolean

Set cnnDB = CurrentProject.Connection
Set rstSource = New ADODB.Recordset

'tblNotes is a linked table which exists on the MS SQL Server
rstSource.Open "tblNotes", _
cnnDB, adOpenDynamic, _
adLockPessimistic, adCmdTableDirect

bnTest = rstSource.Supports(adAddNew)

rstSource.AddNew

rstSource!LinkID = Me!txtLinkID
rstSource!DateOpened = Now()
rstSource!User = CurrentUser()

Does the table, "tblNotes", have a primary key? If not, give it one.

 

[Don]

Does the table, "tblNotes", have a primary key? If not, give it one.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk,

"tblNotes" did not have a primary key, so I added one. Basically a field
called 's_GUID' which I defined as a unique record identifier.
Unfortunately, same result! When I saw your post I thought "Yup, that would
do it!" But it looks like there is something else.

Based on some debug statements, it looks like the recordset is established
okay as I can read data from existing records. Just seems to be adding
records that is the issue. Could it be caused by the form already having a
connection to the same table? But this scenario seemed to work under Access
FE/BE.

Thanks for your comments and any further insights would be greatly
appreciated!

Thanks!

Don

 

[Dirk Goldgar]

"tblNotes" did not have a primary key, so I added one. Basically a
field called 's_GUID' which I defined as a unique record identifier.
Unfortunately, same result! When I saw your post I thought "Yup,
that would do it!" But it looks like there is something else.

Based on some debug statements, it looks like the recordset is
established okay as I can read data from existing records. Just
seems to be adding records that is the issue. Could it be caused by
the form already having a connection to the same table? But this
scenario seemed to work under Access FE/BE.

Thanks for your comments and any further insights would be greatly
appreciated!

I've used code very like that to add records to linked SQL Server
tables, with no problem. My tables have a timestamp field, as
recommended by the Upsizing Wizard; do yours? Does your form have the
table or record locked?

Did you relink the table after adding the primary key? That field name,
"s_GUID" -- it sounds like a replication ID. Maybe you should add an
autonumber field to the table instead of using that one. (Just
wondering.)

 

[Don]

I've used code very like that to add records to linked SQL Server
tables, with no problem. My tables have a timestamp field, as
recommended by the Upsizing Wizard; do yours? Does your form have the
table or record locked?

Did you relink the table after adding the primary key? That field name,
"s_GUID" -- it sounds like a replication ID. Maybe you should add an
autonumber field to the table instead of using that one. (Just
wondering.)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk,

The re-linking did it! Thank you! Thank you!

"s_GUID" was originally a replication field from Access. I just re-used the
name -- in retrospect, probably a bad idea -- in MS SQL Server to create a
unique identifier for each record. To avoid confusion, maybe I will rename
the s_ fields to p_ (for "project" as opposed to 's_' for "system").

Based on some discussions locally, the 'uniqueidentifier' type is a
preferred substitute for the autonumber type in Access for links and
internal identifiers. Sort of makes sense, since it guarantees a unique
identifier. My s_GUID fields are all of type 'uniqueidentifier' with a
default value of newid().

Well, off to see what I can break next!


Again, "THANK YOU!", "THANK YOU!"

Don

 

[Dirk Goldgar]

Based on some discussions locally, the 'uniqueidentifier' type is a
preferred substitute for the autonumber type in Access for links and
internal identifiers. Sort of makes sense, since it guarantees a
unique identifier. My s_GUID fields are all of type
'uniqueidentifier' with a default value of newid().

I don't have an opinion on the subject.

Well, off to see what I can break next!

Again, "THANK YOU!", "THANK YOU!"

You're welcome.