dbSeeChanges problem in Access with linked SQL Server table

[gmazza via AccessMonster.com]

Hey there,
I am having a little problem.
I split my Access database called Admin into a SQL Server database.
I have another Access database called QC that I link to a SQL Server table in
the Admin database.
This table has a field that is an Autonumber.
In my QC database, there were no errors in my code BEFORE I linked to a table
in the Admin database. After I linked, I got an error on this line:
Set rst = CurrentDb.OpenRecordset("ErrorLog", dbOpenDynaset, dbAppendOnly)
saying when using an IDENTITY I need to have dbSeeChanges.
I changed my code to this:
Set rst = CurrentDb.OpenRecordset("ErrorLog", dbOpenDynaset, dbAppendOnly +
dbSeeChanges)
and it worked.
However, I now have added an Autonumber to another table in SQL Server and
I'm getting this error in Access again for a different database.
How can I not have this error? The answer can't be I can't have Autonumbers
in SQL
or
I have to add dbSeeChanges every time I open a recordset for a table that has
Autonumber in SQL Server.
Please help. Thanks!!

 

[Sylvain Lafontaine]

You have to add dbSeeChanges every time you open a recordset for a table
that has Autonumber in SQL Server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Debra]

Hey there,
I am having a little problem.
I split my Access database called Admin into a SQL Server database.
I have another Access database called QC that I link to a SQL Server table
in
the Admin database.
This table has a field that is an Autonumber.
In my QC database, there were no errors in my code BEFORE I linked to a
table
in the Admin database. After I linked, I got an error on this line:
Set rst = CurrentDb.OpenRecordset("ErrorLog", dbOpenDynaset, dbAppendOnly)
saying when using an IDENTITY I need to have dbSeeChanges.
I changed my code to this:
Set rst = CurrentDb.OpenRecordset("ErrorLog", dbOpenDynaset, dbAppendOnly
+
dbSeeChanges)
and it worked.
However, I now have added an Autonumber to another table in SQL Server and
I'm getting this error in Access again for a different database.
How can I not have this error? The answer can't be I can't have
Autonumbers
in SQL
or
I have to add dbSeeChanges every time I open a recordset for a table that
has
Autonumber in SQL Server.
Please help. Thanks!!

 

[gmazza via AccessMonster.com]

Just doesn't make sense because as I said, I split my database and have this
problem. If I create a table in SQL Server that has an Autonumber, I don't
receive this error in my code.
So if thats the case then it seems to disprove what you said about having to
add dbSeeChanges.
I don't want to create all my tables in SQL Server though, I want to split my
databases.
Does anyone know of a parameter or something that can be set so I don't have
to add this everywhere I open a recordset?

You have to add dbSeeChanges every time you open a recordset for a table
that has Autonumber in SQL Server.

Hey there,
I am having a little problem.

[quoted text clipped - 23 lines]

Autonumber in SQL Server.
Please help. Thanks!!

 

[Sylvain Lafontaine]

I'm glad to hear that you can append new records to a recordset based on a
SQL-Server table having an identity field without using the dbSeeChanges
option if you first create the table directly on SQL-Server - and I will
check that later - but for the rest, if you get an error from Access, I see
no other option than to add this parameter.

For the possibility of having a global setting for this option, I never
heard anything about this.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Just doesn't make sense because as I said, I split my database and have
this
problem. If I create a table in SQL Server that has an Autonumber, I don't
receive this error in my code.
So if thats the case then it seems to disprove what you said about having
to
add dbSeeChanges.
I don't want to create all my tables in SQL Server though, I want to split
my
databases.
Does anyone know of a parameter or something that can be set so I don't
have
to add this everywhere I open a recordset?

You have to add dbSeeChanges every time you open a recordset for a table
that has Autonumber in SQL Server.

Hey there,
I am having a little problem.

[quoted text clipped - 23 lines]

Autonumber in SQL Server.
Please help. Thanks!!