Re: Form and subform weirdness when linking to SQL Server 2005 vi

Tech-Archive recommends: Fix windows errors by optimizing your registry


Thanks for the quick response!

All the tables have:

1. A primary key: incrementing (integer) identity column
2. timestamp field
3. row GUID (this is for the replication)

Some additional information:

I'm using Access 2003. The file format is 2002/2003.

The SQL Server database is configured with Merge Replication (2 databases
are particpating). I get to wondering if the SQL Server triggers that get
added for merge replication are messing with things in Access. I haven't
added any triggers of my own.

I didn't notice these problems in Access when developing on a workstation
(connecting to SQL Server on one of our servers). However, when I started
developing running a remote desktop session on a Win Terminal Server, the
weirdness became very apparent. I wonder if Terminal Server is causing the
problem?


"Albert D. Kallal" wrote:

Several things case these problems:

First make sure all of these tables have a primary key. MS access and a jet
back end works fine when you don't have a primary key in the child table,
but with sql server, the table will be read only, and you not be able to add
child records.

The second thing to make sure is that all of the tables on the SQL server
side have a time stamp field (access and the odbc driver use this time stamp
field to figure out if and when the records been updated, if you don't have
the time stamp field, then I find a sub forms often work very poorly if at
all.

If you do the above two things, then usually don't have to change anything
at all with your form + sub-forms to make them work. in other words with
zero modifications, simply using the link master child settings in the sub
form, the whole thing should work.

So just make sure each and every table has a primary key. As mentioned in
access those child tables in sub forms don't need a primary key and they
will work just fine.

However, when you move to SQL server make sure each and every table has a
primary key.

Each table also having a time stamp column really helps here also.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx




.

Relevant Pages

  • Re: conflict causing delete at publisher
    ... This looks like a classic case of a primary key violation. ... it updated subsequently at the subscriber due to an insert operation. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... If I keep the primary key, ... >select packet_time,packet_data from packet where ... >have the clustered index (either on PACKET_TIME or on ...
    (microsoft.public.sqlserver.programming)
  • A pk is *both* a physical and a logical object.
    ... Primary key is a logical constraint. ... but SQL Server does not allow you to create a unique or primary ... constraints) by indexing is purely *platform dependent*. ... PKs are part of the ANSI SQL standard *for db implementations*. ...
    (comp.databases.theory)
  • Re: Re-Seed in SQL Server
    ... Add a new field to the table that holds the Primary Key. ... or something like that and make it an Autonumber field and a Primary Key. ... If the table is already on the SQL Server you could try the below. ... that your Autonumber field in the Access Database has gotten corrupted. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: implement BOTH peer-to-peer replication & merge replication
    ... If a table "A" has an auto increment field as the Primary key, ... SQL Server DB. ... What peer to peer will do which merge will not is allow all ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)