Form and subform weirdness when linking to SQL Server 2005 via OD

[jrsmoots]

I'm revising an access app to link to SQL Server 2005 via ODBC.

I have forms that contain subforms. In some cases I have a Master form,
which hosts a subform, that in turn has its own subforms.

In the Access-Only version of this app, subforms were linked using the Link
Child/Master fields in design view. When accessing Access tables (linked),
everything worked fine. I could add new records to the subforms.

Linked to SQL Server 2005 via ODBC, I find that when I add a new record to a
subform, sometimes (not always), the record disappears, and other records
appear, records that are in the underlying table, but are NOT part of the
current data set. Very disturbing. It's like the subform forgets it's filter
(which was set using the Link Master/Child form functionality).

I've also noticed that on a single form, if I try adding a record via code,
using:

'Add new record
DoCmd.GoToRecord , , acNewRec

Sometimes Access will show me a record already in the database, instead of
creating a new record.

Upon further investigation, it appears that Access creates the new blank
record, but the focus doesn't stay on the new record.

This is driving me somewhat batty, since the behaviors are erratic. The
first time a try and add a new record, I get the weird results. The 2nd, 3rd,
4th, behave normally.

So, now I have this workaround:

1. All subforms have SQL Statement as their record source, restricing them
to showing rows that are related to their parent.
2. On the After Update of the subforms, I've added:

me.parent.refresh
me.refresh

To force the subforms parent to update, and then the subform. (refreshing
just the current subform doesn't solve the problem 100%).

The above refresh (two refreshes) is appears to work reliably, but it causes
blinking of the form as the refreshes occur, and messes with using the tab
key to go to a new record on continuous forms.

Has anyone seen this weird behavior, and/or does any one have a more elegant
solution for ensuring that subforms always display the correct data.

On the single forms (no subforms), I've gone ahead and created stored
procedures to add the new record, return the @@Scope_Identity, which I then
use to set the record source of the form, ensuring I only see the one record
I want to see. This seems to work fine, but I would hate to have to do this
for every form in this Access app.

And yes, I know I should move to VB.NET I'm working on that too!!

Thanks in advance!!

 

[Albert D. Kallal]

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.

 

[jrsmoots]

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?