Adding records to a SQL 2000 table with an identity field, in Access 2003

[Kyle O via AccessMonster.com]

I wish I could be calmer while I write this but I'm about ready to start
throwing stuff out of aggrivation. I'm trying to add a record to a SQL table
that has an identity feild (ohh no! That never happens!) through programming,
but nothing I've tried has been successful.

Here's the jist of what I'm trying to do: I have a form in Access 2003 that
grabs data from text boxes and places them in 9 different tables ( all
related by an ID column) which are linked to a SQL Server 2000 database. I
can't combine these tables into one, so don't ask. I want to add a record to
the parent table which contains an ID field that is an identity in SQL Server
2000. I use ADO and I get a run-time error (3251) that says "Operation is
not supported for this type of object" when I go into debug, it highlights
the Open method of my recordset object, which is as follows:

rst.Open "Business Information", CurrentDb.Connection, adOpenDynamic,
adLockOptimistic

Then I tried using ADO again, this time I created a connection object that
points to the SQL server and used that for the connection property of the
Open method. Well, I get a run-time error whenever I try to call the update
and close methods and when I use just the addnew method, it doesn't add a new
record - it does nothing.

So I switched to DAO and gave that a try. Ohh this made me want to pour hot
soup in my eyes. I run the OpenRecordset method of the CurrentDb object and
I get a run-time error (3622): "You must use the dbSeeChanges option with
OpenRecordset when accessing a SQL Server Table that has an IDENTITY column."
Here's the method:

Set rst = CurrentDb.OpenRecordset("Business Information")

So, going with the advice of the compiler, I placed dbSeeChanges as one of
the option arguments in the method call. Guess what? I got the same error...


I have no idea where to go, my patience and tolerance has run out. I can add
records through the table view in Access, but I can't be opening tables
onscreen while people are trying to data enter. If anybody has any advice or
possible solution, please tell me.

-Kyle

 

[Kyle via AccessMonster.com]

Well, I'm going to remove the identity from the parent table and keep it as
just the primary key - this will let me add records. I really don't want to
do this because I have to enter the ID numbers manually then check the
database for any duplicates. It uses more processing time then it should.

If you guys figure out how to add records with out removing the identity,
please tell me

Thanks,
-Kyle

 

[Brendan Reynolds]

In your ADO example, you're mixing ADO and DAO - CurrentDb is a DAO object,
and you can't use it with ADO. You need to use CurrentProject.Connection in
ADO instead of CurrentDb.Connection, as in my example below.

When you tried to use DAO, you probably had the dbSeeChanges argument in the
wrong position. See my example below, noting how dbSeeChanges is passed as
the third argument in the OpenRecordset call.

In case you haven't already encountered it, you also need to remember that
while Jet assigns new values to AutoNumber fields as soon as you begin to
enter data in a new record, SQL Server doesn't assign the new value to the
identity field until you save the record, so any attempt to read that value
before you save the record will raise an error.

Public Sub TestSub()

Dim db As DAO.Database
Dim rstd As DAO.Recordset

Dim rsta As ADODB.Recordset

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM tblTest", , dbSeeChanges)
Debug.Print rstd.Fields(0).Value
rstd.Close

Set rsta = New ADODB.Recordset
rsta.ActiveConnection = CurrentProject.Connection
rsta.Open "SELECT * FROM tblTest"
Debug.Print rsta.Fields(0).Value
rsta.Close

End Sub

 

[Kyle O'Bryan via AccessMonster.com]

Thanks for the response,

Doh! Well that explains the ADO problem... I always used CurrentProject
before, I don't know why I decided to us CurrentDb this time, lol. Must have
been a mishap due to frustration. Thanks for pointing it out!

As for DAO, yes I put it in the right place, the third argument or the
Options argument. The compiler would return a different run-time error if I
placed it anywere else.

I knew about the SQL Server limitations and I used the update method before I
read the Identity column. In my troubleshooting code, I commented out the
other code and left just the addnew and close methods (with exception of DAO
which I included the Update method). The following is the method I used for
ADO for Parent Table:

Dim rst as New ADODB.Recordset
Dim intID as Integer

rst.Open "Business Information", CurrentProject.Connection, dbOpenDynamic,
dbLockOptimistic
With rst
.AddNew
.Update
End With
intID = rst.Fields("ID")
rst.clost
set rst = Nothing

and now I'm getting a "Call Failed" on the update method... All I can think
of is the new record, when appended, is voilating a rule in SQL (such as
placing a Null value in a field that doesn't accept nulls) but the Identity
column which is suppose to be an autonumber is the only field that doesn't
accept nulls... I'm not reading the data before it's saved - any Ideas?

The DAO code still doesn't work, same thing as before with and without
dbSeeChanges.

In your ADO example, you're mixing ADO and DAO - CurrentDb is a DAO object,
and you can't use it with ADO. You need to use CurrentProject.Connection in
ADO instead of CurrentDb.Connection, as in my example below.

When you tried to use DAO, you probably had the dbSeeChanges argument in the
wrong position. See my example below, noting how dbSeeChanges is passed as
the third argument in the OpenRecordset call.

In case you haven't already encountered it, you also need to remember that
while Jet assigns new values to AutoNumber fields as soon as you begin to
enter data in a new record, SQL Server doesn't assign the new value to the
identity field until you save the record, so any attempt to read that value
before you save the record will raise an error.

Public Sub TestSub()

Dim db As DAO.Database
Dim rstd As DAO.Recordset

Dim rsta As ADODB.Recordset

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM tblTest", , dbSeeChanges)
Debug.Print rstd.Fields(0).Value
rstd.Close

Set rsta = New ADODB.Recordset
rsta.ActiveConnection = CurrentProject.Connection
rsta.Open "SELECT * FROM tblTest"
Debug.Print rsta.Fields(0).Value
rsta.Close

End Sub

I wish I could be calmer while I write this but I'm about ready to start
throwing stuff out of aggrivation. I'm trying to add a record to a SQL

[quoted text clipped - 51 lines]

-Kyle

 

[Brendan Reynolds]

You're still mixing ADO and DAO - dbOpenDynamic and dbLockOptimistic are DAO
constants.

--
Brendan Reynolds (MVP)

Thanks for the response,

Doh! Well that explains the ADO problem... I always used CurrentProject
before, I don't know why I decided to us CurrentDb this time, lol. Must
have
been a mishap due to frustration. Thanks for pointing it out!

As for DAO, yes I put it in the right place, the third argument or the
Options argument. The compiler would return a different run-time error if
I
placed it anywere else.

I knew about the SQL Server limitations and I used the update method
before I
read the Identity column. In my troubleshooting code, I commented out the
other code and left just the addnew and close methods (with exception of
DAO
which I included the Update method). The following is the method I used
for
ADO for Parent Table:

Dim rst as New ADODB.Recordset
Dim intID as Integer

rst.Open "Business Information", CurrentProject.Connection, dbOpenDynamic,
dbLockOptimistic
With rst
.AddNew
.Update
End With
intID = rst.Fields("ID")
rst.clost
set rst = Nothing

and now I'm getting a "Call Failed" on the update method... All I can
think
of is the new record, when appended, is voilating a rule in SQL (such as
placing a Null value in a field that doesn't accept nulls) but the
Identity
column which is suppose to be an autonumber is the only field that doesn't
accept nulls... I'm not reading the data before it's saved - any Ideas?

The DAO code still doesn't work, same thing as before with and without
dbSeeChanges.

In your ADO example, you're mixing ADO and DAO - CurrentDb is a DAO
object,
and you can't use it with ADO. You need to use CurrentProject.Connection
in
ADO instead of CurrentDb.Connection, as in my example below.

When you tried to use DAO, you probably had the dbSeeChanges argument in
the
wrong position. See my example below, noting how dbSeeChanges is passed as
the third argument in the OpenRecordset call.

In case you haven't already encountered it, you also need to remember that
while Jet assigns new values to AutoNumber fields as soon as you begin to
enter data in a new record, SQL Server doesn't assign the new value to the
identity field until you save the record, so any attempt to read that
value
before you save the record will raise an error.

Public Sub TestSub()

Dim db As DAO.Database
Dim rstd As DAO.Recordset

Dim rsta As ADODB.Recordset

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM tblTest", , dbSeeChanges)
Debug.Print rstd.Fields(0).Value
rstd.Close

Set rsta = New ADODB.Recordset
rsta.ActiveConnection = CurrentProject.Connection
rsta.Open "SELECT * FROM tblTest"
Debug.Print rsta.Fields(0).Value
rsta.Close

End Sub

I wish I could be calmer while I write this but I'm about ready to start
throwing stuff out of aggrivation. I'm trying to add a record to a SQL

[quoted text clipped - 51 lines]

-Kyle

 

[Kyle O'Bryan via AccessMonster.com]

that was a typo, the constants I used were adOpenDynamic and adLockOptimistic

You're still mixing ADO and DAO - dbOpenDynamic and dbLockOptimistic are DAO
constants.

Thanks for the response,

[quoted text clipped - 84 lines]

 

[Tim Ferguson]

I have a form in Access 2003 that
grabs data from text boxes and places them in 9 different tables ( all
related by an ID column) which are linked to a SQL Server 2000
database.

In ado it's something like:

' need a single connection in order to use @@identity;
' i.e. don't use the the currentproject() function each time
set conn= currentproject().connection

' update the parent table first
adoSQL = "insert into parenttable (field2, field3) " & _
"values(N'" & textbox2 & "', " & textbox3 & ")"
conn.execute adoSQL

' get the new identity value
adoSQL = "select @@identity as newvalue"
set rs = new adodb.recordset
' can't remember the arguments off hand, but you get the
' picture...
rs.open adoSQL, conn, adoreadonly
dwNewRecordID = rs!newvalue
rs.close

' now update the other tables
adoSQL = "insert into othertable (recordID, field2) " & _
"values (" &dwNewRecordID & ", N'" & textbox4 & "')"
conn.execute adoSQL, etc, etc


If you use the DAO AddNew... Update methods you can get the identity
value straight out of the new record like this

' don't want to lock any existing records..
jetSQL = "SELECT * FROM ParentTable WHERE FALSE"
set rs = db.OpenRecordset(jetSQL, dbOpenDynaset)

' begin the update
rs.AddNew
' touch the empty record
rs!RequiredField = textbox2
' now the identity will be there to read
dwNewRecordID = rs!RecordID
' and finish the update
rs.Update
rs.Close

' now you can use the dwNewRecordID anywhere else


Hope that helps


Tim F

 

[Kyle O'Bryan via AccessMonster.com]

I see what you're doing with the ADO code and it looks promising, I'm going
to give it a try. BTW, I instantiate the recordset object in the declaration
statement for simplicity's sake. If that will cause an error, stop me now
before I start to code, lol.

As for your DAO code, you're doing the same thing I did just a different way.
I have tried opening a recordset with a SQL statement and I still get the
same error telling me to supply the dbSeeChanges constant in the options
argument when opening a table with an Identity, even though I coded it in
correctly. This is the code I used for DAO:

Dim rst as New DAO.recordset
Dim intID as Integer

set rst = CurrentDb.OpenRecordset("ParentTable",,dbSeeChanges)
With rst
.AddNew
.Update
End With
intID = rst.Fields("ID")
rst.close
set rst = Nothing

 

[Tim Ferguson]

I have tried opening a recordset with a SQL statement and I still get the
same error telling me to supply the dbSeeChanges constant in the options
argument when opening a table with an Identity,

set rst = CurrentDb.OpenRecordset("ParentTable",,dbSeeChanges)

I've never met this error message but I never leave the options blank
either. And I never ever ever open table-type dynasets.

Try setting all the argements correctly and see if that gets rid of the
error message.

Best wishes


Tim F

 

[Kyle O'Bryan via AccessMonster.com]

Already tried setting options manually, same thing. Also, they're linked
tables so it doesn't o0pen a Table-Type Recordset, it opens a Dynaset.

 

[Brendan Reynolds]

When I attempted to reproduce the problem before, I mistakenly used a table
that didn't have any text or ntext fields. When I use a table that does, I
can reproduce the error ...

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM dbo_Employees", ,
dbSeeChanges)
Debug.Print rstd.Fields(0).Value
rstd.Close

This causes the 'must use dbSeeChanges' error, even though I am already
using it. However, explicitly specifying dbOpenDynaset did fix the problem
for me ...

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM dbo_Employees", _
dbOpenDynaset, dbSeeChanges)
Debug.Print rstd.Fields(0).Value
rstd.Close

The above ran without error. I tested both with the SQL statement as above,
and using the table name, with the same result.

 

[Kyle O'Bryan via AccessMonster.com]

Got it! Doesn't produce the error when I include dbOpenDynaset argument like
you suggested, problem solved!

Now I'm having an issue with the table not incrementing when a new record is
adding, producing an ODBC call failed. This error should be well within my
grasp though.

Thanks a ton for all your help guys!

When I attempted to reproduce the problem before, I mistakenly used a table
that didn't have any text or ntext fields. When I use a table that does, I
can reproduce the error ...

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM dbo_Employees", ,
dbSeeChanges)
Debug.Print rstd.Fields(0).Value
rstd.Close

This causes the 'must use dbSeeChanges' error, even though I am already
using it. However, explicitly specifying dbOpenDynaset did fix the problem
for me ...

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM dbo_Employees", _
dbOpenDynaset, dbSeeChanges)
Debug.Print rstd.Fields(0).Value
rstd.Close

The above ran without error. I tested both with the SQL statement as above,
and using the table name, with the same result.

Already tried setting options manually, same thing. Also, they're linked
tables so it doesn't o0pen a Table-Type Recordset, it opens a Dynaset.

[quoted text clipped - 15 lines]

 

[Kyle O'Bryan via AccessMonster.com]

Got it! Doesn't produce the error when I include dbOpenDynaset argument like
you suggested, problem solved!

Now I'm having an issue with the table not incrementing when a new record is
adding, producing an ODBC call failed. This error should be well within my
grasp though.

Thanks a ton for all your help guys!