Can and Can't insert using DAO on SQL Server linked table

[Mark A. Sam]

I am having a problem inserting using DAO, on linked SQL Server tables.

Here is an example:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Order Entry ST Materials", dbOpenDynaset,
dbSeeChanges)

rst.AddNew
rst![prodmatType] = "Test"
rst.Update
Set rst = Nothing


Initially I received an ODBC Call Failed error. Then I set the permissions
to this table for the public user to explicitly allow Select, Insert, and
Update.

This then allowed the update until I closed and retried. This is a test
procedure. I tried on my working procedure which worked once, then I
received an error on the next try. Afterwards the test procedure failed
givng me an error message,

ODBC--insert on a linked table 'Order Entry ST Maetrials' failed.

If seems when I play around with the permissions using Enterprise manager,
it will allow temporary success.

ADO works fine (or seems to), but but being unfamiliar with it would
necessitate a big job to change my code to that. I need to work this issue
out and any ideas would be appreciated.

I am running Enterprise Edition on my local drive and have all permissions,
however, I do not show up on the individual table permissions, only public
does. I gave Public explicit permissions to Select, Update and Insert.


Thanks and God Bless,

Mark A. Sam

 

[david epsom dot com dot au]

insert this code:

Dim daoerr As DAO.Error
For Each daoerr In DBEngine.Errors
MsgBox daoerr.Description
Next daoerr

this will give you a much better idea what is going on.

(david)

 

[Ron Weiner]

Take a look at the Primary Key field on the Sql Table. Assuming that you
intended this field to be an "AutoNumber" type make sure that you set the
fields Identity property to True. Otherwise you are responsible to insert a
unique key when you append a new record.

Ron W

Thank you David,

This told me that it is trying to insert a duplicate key when I am inserting
a new record. At least I know it isn't a permissions issue.

God Bless,

Mark

insert this code:

Dim daoerr As DAO.Error
For Each daoerr In DBEngine.Errors
MsgBox daoerr.Description
Next daoerr

this will give you a much better idea what is going on.

(david)

I am having a problem inserting using DAO, on linked SQL Server tables.

Here is an example:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Order Entry ST Materials", dbOpenDynaset,
dbSeeChanges)

rst.AddNew
rst![prodmatType] = "Test"
rst.Update
Set rst = Nothing


Initially I received an ODBC Call Failed error. Then I set the permissions
to this table for the public user to explicitly allow Select, Insert, and
Update.

This then allowed the update until I closed and retried. This is a test
procedure. I tried on my working procedure which worked once, then I
received an error on the next try. Afterwards the test procedure failed
givng me an error message,

ODBC--insert on a linked table 'Order Entry ST Maetrials' failed.

If seems when I play around with the permissions using Enterprise manager,
it will allow temporary success.

ADO works fine (or seems to), but but being unfamiliar with it would
necessitate a big job to change my code to that. I need to work this issue
out and any ideas would be appreciated.

I am running Enterprise Edition on my local drive and have all permissions,
however, I do not show up on the individual table permissions, only public
does. I gave Public explicit permissions to Select, Update and Insert.


Thanks and God Bless,

Mark A. Sam

 

[Mark A. Sam]

Ron,

The identity is set to True. I added this field as an Autonumber and
primary key before I upsized. It autoincrments fine. The problem seems to
be that between the AddNew method and the Update there is a value for this
field rather then being null. The value is an existing number, but not the
value of the first record. I have no idea why it is holding the value, but
the problem seems to arise after I delete records from the main form.

I am trouble shooting this to see how to avoid it, but it doesn't seem as
though this should happen. After the insert of a new record, I would expect
this value to be null until after the Update.

God Bless,

Mark

Take a look at the Primary Key field on the Sql Table. Assuming that you
intended this field to be an "AutoNumber" type make sure that you set the
fields Identity property to True. Otherwise you are responsible to insert a
unique key when you append a new record.

Ron W

Thank you David,

This told me that it is trying to insert a duplicate key when I am inserting
a new record. At least I know it isn't a permissions issue.

God Bless,

Mark

insert this code:

Dim daoerr As DAO.Error
For Each daoerr In DBEngine.Errors
MsgBox daoerr.Description
Next daoerr

this will give you a much better idea what is going on.

(david)

I am having a problem inserting using DAO, on linked SQL Server tables.

Here is an example:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Order Entry ST Materials",
dbOpenDynaset,
dbSeeChanges)

rst.AddNew
rst![prodmatType] = "Test"
rst.Update
Set rst = Nothing


Initially I received an ODBC Call Failed error. Then I set the
permissions
to this table for the public user to explicitly allow Select,

Insert,
and

Update.

This then allowed the update until I closed and retried. This is a test
procedure. I tried on my working procedure which worked once, then I
received an error on the next try. Afterwards the test procedure failed
givng me an error message,

ODBC--insert on a linked table 'Order Entry ST Maetrials' failed.

If seems when I play around with the permissions using Enterprise manager,
it will allow temporary success.

ADO works fine (or seems to), but but being unfamiliar with it would
necessitate a big job to change my code to that. I need to work this
issue
out and any ideas would be appreciated.

I am running Enterprise Edition on my local drive and have all
permissions,
however, I do not show up on the individual table permissions, only public
does. I gave Public explicit permissions to Select, Update and Insert.


Thanks and God Bless,

Mark A. Sam

 

[Mark A. Sam]

I retract my last post. I did a test and found out that my observation
wasn't correct as being the problem.

I just tried inserting a record maually into the table and receive this
error:

ODBC- insert on a linked table "Order Entry ST Materials failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be
specified for identity column in the table 'Order Entry ST Materials' when
IDENTITY_INSERT is set to ON. (#545)


So sometimes it increments automatically and sometimes not, which seems to
be the issue here.


God Bless,

Mark A. Sam

 

[Mark A. Sam]

I retract my last post. I did a test and found out that my observation
wasn't correct as being the problem.

I just tried inserting a record maually into the table and receive this
error:

ODBC- insert on a linked table "Order Entry ST Materials failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be
specified for identity column in the table 'Order Entry ST Materials' when
IDENTITY_INSERT is set to ON. (#545)



God Bless,

Mark A. Sam

 

[david epsom dot com dot au]

Using JET 4, an append or insert that attempts to write a value
into the Identity field will set Identity Insert on. The insert
will succeed, and after that the autonumber won't work properly
until the Identity Insert is turned off again.

There is also a property that controls if JET tries to write
to all fields, or just to fields that have changed.

"Initialization Settings for Jet-connected ODBC Databases"
"PreparedInsert: An indicator of whether to use a prepared INSERT statement
that inserts data in all columns. Values are 0 (use a custom INSERT
statement that inserts only non-Null values) and 1 (use a prepared INSERT
statement). The default is 0 (values are of type REG_DWORD).Using prepared
INSERT statements can cause Nulls to overwrite server defaults and can cause
triggers to execute on columns that weren't inserted explicitly."


I suspect that you are writing a 0 to the Identity field: after that
it is not autonumber until Identity Insert is turned off again, and
you get an error the second time because you are writing another 0
(duplicate index) to the identity field. I don't know if you are
writing the 0 because of (a) coding error (b) JET bug (c) Non-default
value of PreparedInsert or (d) other.

You can use a pass-through query to reset the Identity Insert Property
"set identity_insert DBO.tblYC_YieldCurve off;"

Also, you may find that inserting into a different table may reset
this property: or you may find that inserting into a table may
prevent identity insert on other tables.

I am not aware of any documentation of this behaviour.
I am not aware of any documentation of the change between Jet 3 and 4.
I don't know if this varies between different releases of Jet 4.

If you find any documentation, or reach any conclusions, please let me know!

(david)

 

[Mark A. Sam]

David,

Thank you for your reply. This isn't behaving as you describe, becuase
sometimes I am able to insert (especially on my test procedure) over and
over without an issue.

I added this particular Autonumber field to allow updates to the table after
upsizing, otherwise it has no purpose. There is no chance that I am writing
it and it doesn't have a default. I can add records to the table when it is
opened in the Enterprise manager, even when I can't insert through code, and
it will increment.

I will try the passthrough query. I modifed it for my table... set
identity_insert DBO.[Order Entry St Materials] off;
Is that correct? Testing didn't give an error. I'll let you know how it
went.

Thanks again and God Bless,

Mark

 

[Synergy]

I resolved this issue, but writing a function to delivering the next ID
number (to the Identity Field) and assigning it before executing my DAO
Update method.

This would suggest that Identity Insert was engaged on that table, but there
was no problem populating other subtables with Identity Fields.

God Bless,

Mark A. Sam

 

[david epsom dot com dot au]

I think/thought that Identity Insert is supposed to be on
for only one table at a time in the database. This would
suggest that there was some odd behaviour that for some
reason occasionally set identity_insert on this one table.

thanks for your continued feedback

(david)

 

[Mark A. Sam]

David,

It is what I thought also. I don't think Identity Insert is the issue here,
possibly the error message is wrong.

God Bless,

Mark