Opening two Tables as a Idenitity error

[Tom]

I need to write data to two tables, but when "setting" the BOM table I get
the following error:

"You must use the dbseechanges option with openRecordset when accessing a
SQL Server table that has an Idenitity column."

What does this mean and how do I fix it?
What can i read to further explain the error. (someplace within Access
Language reference?)

Dim MyTable As Recordset
Dim BomTbl As Recordset

Dim MyDB As Database
Dim PNHolder As String
Dim AsyPN As String

Set MyDB = CurrentDb()
Set MyTable = MyDB.OpenRecordset("Products")
Set BomTbl = MyDB.OpenRecordset("Bom") <-- error spot

MyTable.AddNew
MyTable![PartNumber] = PNHolder & "-001"
MyTable![DesignerName] = [SelectDesigner]
MyTable![Status] = "-1"
mytable update

BomTbl.AddNew
BomTbl![PartNumber] = PNHolder & "-001"
BomTbl![DesignerName] = [SelectDesigner]
BomTbl![Status] = "-1"
Bomtable update

MyDB.close

Thanks
Tom

 

[Tom]

Bonnie,
OK, I am dumb.... I just don't get it. I can write to other tables but this
one will not let me write to it using the exact same code! is it because the
"bom" table has an autonumber field as the Key?
my code is quite simple; (see below)
-identify table,
-Add something,
-close.

Thanks for your help
Frustrated and stuck again.

Here's a link I found:

http://weblogs.asp.net/gunnarpeipma...server-table-that-has-an-identity-column.aspx


Bonnie
http://www.dataplus-svc.com

I need to write data to two tables, but when "setting" the BOM table I get
the following error:

"You must use the dbseechanges option with openRecordset when accessing a
SQL Server table that has an Idenitity column."

What does this mean and how do I fix it?
What can i read to further explain the error. (someplace within Access
Language reference?)

Dim MyTable As Recordset
Dim BomTbl As Recordset

Dim MyDB As Database
Dim PNHolder As String
Dim AsyPN As String

Set MyDB = CurrentDb()
Set MyTable = MyDB.OpenRecordset("Products")
Set BomTbl = MyDB.OpenRecordset("Bom") <-- error spot

MyTable.AddNew
MyTable![PartNumber] = PNHolder & "-001"
MyTable![DesignerName] = [SelectDesigner]
MyTable![Status] = "-1"
mytable update

BomTbl.AddNew
BomTbl![PartNumber] = PNHolder & "-001"
BomTbl![DesignerName] = [SelectDesigner]
BomTbl![Status] = "-1"
Bomtable update

MyDB.close

Thanks
Tom

 

[Tom]

Bonnie,

I might see part of the problem, were you trying to have me write an SQL
statement?
I was trying to access the SQL server "directly" and was not useing an SQL
statements or exicute.

This code currently works great. but not when I try it on a table with an
index it fails.
++++++++++++++++++++++++++++++++++++++++++++++++++
Dim MyTable As Recordset
Dim MyDB As Database

Set MyDB = CurrentDb()
--> Set MyTable = MyDB.OpenRecordset("Products")

MyTable.AddNew ' Add
line to table
MyTable![DesignerName] = [SelectDesigner] ' Designers Name
MyTable![Status] = "-1" '
Status = -1
MyTable![SAPOk] = "-1" ' SAPOK
= -1

MyTable.Update
MyTable.Close
MyDB.Close
++++++++++++++++++++++++++++++++++++++++++++++++++++++
If, at the arrows, I inserted;
-->Set mytable = CurrentDb.OpenRecordset("BOM", Options: = dbFailOnError +
dbSeeChanges)

and I get the same 3622 error code