Access 2003 and SQL 2005 and replication

[Gijs Beukenoot]

Hi all,

I am fcing a strange thing when using Access with SQL 2005. I guess it
might be related to the ODBC driver but before making dual-code
(supporting both SQL 2000 and SQL 2005 servers), I was wondering if I
am assuming right.

We are having an Access front-end and link to the database using the
ODBC driver. Tables are attached using code :
With p_conConnect '(this is an ADODB.Connection
.ConnectionString = "driver={SQL Server};" & _
"server=" & gsSQLServer & ";" & _
"database=" & p_sDatabase

<checking and enumerating tables from a local table, then looping to
link them all>

sConnect = "ODBC;DSN=ALCA;" & _
"APP=ALCA" & ":" & _
"DATABASE=" & gsSQLDatabase & ";" & _
"Trusted_Connection=Yes;" & _
"TABLE=dbo." & sTableName
Set tdLocal = dbLocal.CreateTableDef(sTableName, _
dbAttachSavePWD, _
"dbo." & xsTableName, _
sConnect)
Call dbLocal.TableDefs.Append(tdLocal)

Now, in the DSN, I am registering (I create that DSN in code) a SQL
Server and also in the ADODB connection, I am using a SQL Server
driver, not the native client. This works fine with SQL 2000. Even in a
replicated environment (clients have a MSDE on their laptop and get a
subscription).
Now imagine the following, a mainform and a subform. Let's say orders
and order details, linked together on orderID. Order ID 100. When I add
a record (manually) on the subform, let's say I add article ID 1,
everything works as expected in SQL 2000. However, when moving to a SQL
2005 environment, including SQL 2005 Express on the clients, I see a
strange behaviour :
I add a record on the subform (manually again), article ID 1 again then
leave the record (move to the previous one from the same order or a new
record), the record I've just entered on screen is replaced by a
completely different record from the database (article ID 7 from order
ID 123). Yes, that is an existing record, but it comes from a
completely different order. Refreshing the form shows me the correct
record again (the ID 1 I've just entered).
This only happens in a replicated scenario. As soon as I remove the
replication stuff and use the database directly, it all works as
expected (moving the recordpointer does not show a different record
(but still article no 1)).

Am I making sense here? Or do I need to clarify a little further?

The only thing that I see here is that I am not using the SQL 2005
native driver but still use the SQL Server driver from SQL 2000.
Yes, I can imagine the question "did you try switching over?" but no, I
didn't test that scenario yet. I was hoping somebody can confirm (or
point me to a completely other direction) that I must use the native
driver. I can't seem to find any related articles (KB or Google) on
this particular problem.

Many thanks in advance

GB

[Mary Chipman [MSFT]]

One thing you can try is to simplify your code. I'd recommend NOT
using a DSN, but simply providing an ODBC connection string and then
using DAO to create and link the TableDef objects.

--Mary

[david@epsomdotcomdotau]

You are using natural primary keys?
And replication adds a replication ID to the table?

"Gijs Beukenoot" <Gijs.DOT.Beukenoot@AT@Wanadoo.DOT.nl> wrote in message
news:mn.30737d71e...@Wanadoo.DOT.nl...

[Gijs Beukenoot]

Yes, all autonumber primary keys set to 'not for replication'
Yes, rowguid. And a timestamp field has been added to the tables.

From david@epsomdotcomdotau :

[david@epsomdotcomdotau]

So it sounds like 2005 jet is getting the RowGuid instead
of the natural primary key as the primary key index for the
linked table.

Remember that jet picks a unique index in alphabetical
order to use as the primary key index of the link. You
have two unique indexes on the table. If you choose the
wrong one, adding data to a subform is likely to break.

So perhaps when you use 2005, the rule for selection of
the unique index is different, and you get the other index.

I think that you would be able to check this by enumerating
the index collection (these selected indexes are not local
indexes, so they never appear in the Access GUI index list,
but I think that they are in the index list when enumerated
through DAO/ADO.)

This is only theory, I don't have any experience with 2005
or replication.

(david)

"Gijs Beukenoot" <Gijs.DOT.Beukenoot@AT@Wanadoo.DOT.nl> wrote in message

news:mn.640f7d716...@Wanadoo.DOT.nl...

[Gijs Beukenoot]

From david@epsomdotcomdotau :

> So it sounds like 2005 jet is getting the RowGuid instead
> of the natural primary key as the primary key index for the
> linked table.
>
> Remember that jet picks a unique index in alphabetical
> order to use as the primary key index of the link. You
> have two unique indexes on the table. If you choose the
> wrong one, adding data to a subform is likely to break.
>
> So perhaps when you use 2005, the rule for selection of
> the unique index is different, and you get the other index.
>
> I think that you would be able to check this by enumerating
> the index collection (these selected indexes are not local
> indexes, so they never appear in the Access GUI index list,
> but I think that they are in the index list when enumerated
> through DAO/ADO.)
>
> This is only theory, I don't have any experience with 2005
> or replication.
>
> (david)
>

David.

Sounds reasonable to me. I have no idea t be sure and up to now, didn't
had enough time to try different things. I'm going to test this week
with the 2005 native driver first, perhaps this will solve the problem
'the easy way'...

Thanks

GB

[Andy Magruder]

I am having the exact same problem. My Access forms are linked to SQL 2005
tables that are merge replicated. Spurious numbers appear in the
autonumber/identity fields in my Access form, then the correct number shows
up at some point.

Did the SQL 2005 native drivers help with this? Where does one find them?

A possibly related issue is that @@Indentity in SQL 2005 stored procedures
may well return an identity value from some table in the database haviing
nothing to do with the stored procedure. I had to change @@indentity to
Ident_Current(Table Name) in all our stored procedures.

[Andy Magruder]

On April 5, 2007 I spent 4 hours talking to Microsoft Support. They have not
closed the case, but it looks like a bug in MS-Access that a hotfix will have
to be issued for.

[David W. Fenton]

Andy Magruder <AndyMa...@discussions.microsoft.com> wrote in
news:75A480E2-DDF5-43CB...@microsoft.com:

> I am having the exact same problem. My Access forms are linked to
> SQL 2005 tables that are merge replicated. Spurious numbers appear
> in the autonumber/identity fields in my Access form, then the
> correct number shows up at some point.

I'm no expert on this at all, but are you sure you've recreated your
table links after replicating? Remember that Access table links
retain a lot of metadata that can be outdated. For instance, when I
work with a MySQL back end, I have to delete and recreate the table
links any time I change the structure of the MySQL table it links
to.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Andy Magruder]

I have re-created the table links, both with the older SQL client driver and
the newer native SQL client driver, and it didn't help.

I spent the $245 to call Microsoft support, and it is a bug in Access (the
case is not resolved yet). A run of SQL Profiler proves that Access calls
@@Identity. It's known that when using SQL 2005 you need to use
Ident_Current(tablename). I've tried Access XP (2002) and Access 2007 using
both an MDB and it's new 2007 file format, and they all have the problem.
Note that this happens only when the SQL database is replicated.

[Mattias Jonsson]

You spent $245 to help them figure this out? I don't know whether to
laugh or to cry.

[david@epsomdotcomdotau]

You can only hope that they put this on a faster track
than fixing the broken Jet/ODBC transactions.

"Andy Magruder" <AndyMa...@discussions.microsoft.com> wrote in message
news:370181E8-9F98-4CB1...@microsoft.com...

[Andy Magruder]

Here is a workaround for forms. They must be be sorted by their
autonumber/identity primary key (not always desirable, but now necessary) and
put the following code in each form:

Private Sub Form_AfterInsert()

' Compensate for the fact that Access calls @@Identity and returns a bogus
' Autonumber/identity when a new record is inserted from a replicated
SQL2005 database
Me.Requery
DoCmd.GoToRecord , , acLast

End Sub

If you sort the form descending, change acLast to acFirst.

[Andy Magruder]

For forms, a workaround is to sort the form by the autonumber/identity

primary key (not always desirable, but now necessary) and put the following

code in each:

Private Sub Form_AfterInsert()

' Compensate for the fact that Access calls @@Identity and returns a bogus
' Autonumber/identity when a new record is inserted from a replicated
SQL2005 database
Me.Requery
DoCmd.GoToRecord , , acLast

End Sub

If you sort descending, change acLast to acFirst.

Adding records directly to a query or datasheet view of the table will still
have the problem.

[Aaron Kempf]

David;

I really thougtht that you claimed that JET was BUGFREE?

you are referring specifically to replication, right/

Why would you say that Jet Replication is BUG FREE 'oh except this little
bug'

STFU kid; MS hasn't fixed MDB bugs in a decade

they've graduated to Access Data Projects

<david@epsomdotcomdotau> wrote in message
news:ORf0FhNg...@TK2MSFTNGP03.phx.gbl...

[David W. Fenton]

"Aaron Kempf" <ake...@dol.wa.gov> wrote in
news:Oa$StCRiH...@TK2MSFTNGP05.phx.gbl:

> I really thougtht that you claimed that JET was BUGFREE?

Provide a messsage ID for a post where I said anything that anyone
would reasonably interpret as equivalent to "jet is bug-free" -- you
can't, because I've never asserted any such thing.

In short, you're just making it up -- you're a serial fabricator.

Put more bluntly:

You're a liar.

> you are referring specifically to replication, right/
>
> Why would you say that Jet Replication is BUG FREE 'oh except this
> little bug'

I didn't say that, and you cannot find any place that I've ever said
it.

> STFU kid; MS hasn't fixed MDB bugs in a decade
>
> they've graduated to Access Data Projects

This proves how much of an idiot you are. ADPs have *nothing* to do
with Jet replication, as they can't be used with anything but SQL
Server.

And, of course, as I've repeatedly posted in reply to your erroneous
assertions about ADPs, Microsoft is deprecating ADPs in favor of
MDBs.

That is FACT.

Funny how you never directly respond to the quotations I have
repeatedly provided from MS's most recent documentation on the
subject. The reason for that is, of course, that you can't, because
it proves that EVERYTHING YOU SAY IS WRONG.

[Aaron Kempf]

MS _NEVER_ fixes bugs in Access

get real kid

"Andy Magruder" <AndyMa...@discussions.microsoft.com> wrote in message

news:341AAAAF-1AC3-4471...@microsoft.com...

[Aaron Kempf]

David, David, David

Seriously-- didn't we leave this in the 90s:

For instance, when I
work with a MySQL back end, I have to delete and recreate the table
links any time I change the structure of the MySQL table it links
to.

ADP doesn't have any of these problems!
Why do you promote MDB crap that needs continual work arounds to WORK
CORRECTLY?

ADP = PLUG AND PLAY

"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9911D7221FABAf9...@127.0.0.1...

[Aaron Kempf]

David

you always run around like a dipshit, talking about how Jet Replication is
100 % reliable

you're full of crap kid

MDB is stupid and anyone that uses it should be FIRED and then SPIT UPON

"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message

news:Xns991FD3CD6A9A6f9...@127.0.0.1...

[David W. Fenton]

"Aaron Kempf" <ake...@dol.wa.gov> wrote in

news:u3LPtbdj...@TK2MSFTNGP06.phx.gbl:

> you always run around like a dipshit, talking about how Jet
> Replication is 100 % reliable

You keep claiming this over and over and over and over again.

Yet, every time I ask you to provide a citation for where I said
this, all I hear is:

<crickets>

You are a liar, Aaron.