MS Access, ODBC, SQL 2005, delays before data appears

3 Dec 2008 8:30 AM

Gijs

Hi all,

We are using MS Access as a front end, SQL Server 2005 as the backend.
Tables are linked using ODBC (they are attached using code). the
cursorlocation is adUseClient.
We do not seem to have any problems but occaisionally we seem to 'miss'
data for a few minutes: Let me illustrate with a simple design:
Table 1: orders
Table 2: orderdetails (with a combo that links to the parts)
Table 3: parts
Form 1: orders, with a subform orderdetails
Form 2: parts
While adding orderdetails using a combo-box, we find that a particular
part is missing. We've build code that allows the user to double-click
the combobox 'parts'. User double-clicks it, opens the parts-form, adds
a record and, when that form is closed, the code refreshes the combo
(me.cmbParts.requery).
For, let me say 99.5% of the time, the record is now in the combobox
and the user can select it. But sometimes, the user comes back into the
combobox and the record is not there. If the user double-clicks it
again (going back to the parts) and search for the recently added part,
it is there.
Manual refresh (me.requery to refresh the whole form) does not make a
difference and even closing the form and re-opening it again doesn't
always solve the issue.
I am not 100% sure what next steps are taken by the user: closing the
application or just leaving it 'alone' for a few minutes (?) eventually
solves the problem but the problem does solve itself; when the user
tries again later, the record is 'suddenly' there.
The problem seems to occur more often on a terminal server environment
where user 1 is in the parts form adding records and user 2 is in the
order-detail form. The second user does not 'see' the newly added
records although the form (orders and it's subform orderdetails) is
closed and re-opened.
The ODBC driver does get the parameter MARS Connection=True. This was
added in order to support transactions (begintrans/commit/rollback) in
code but it turns out that part still doesn't work (the SQL 2000 ODBC
driver works fine, the SQL 2005 driver doesn't); but that's another
story.

I've been looking into several possibilties but so far I haven't been
able to find a cause, let alone find a solution...

Any thoughts here ?

Thanks, GB

3 Dec 2008 10:00 PM

Mary Chipman [MSFT]

One possible approach would be to cache static data for combo boxes in
local tables instead of binding directly to the SQL Server table. When
the form opens, it fetches the data from the server and populates the
local tables. The code to add a new item would be to collect the
information and execute an INSERT statement from VBA code/pass-through
query to the SQL Server table. Then the code would delete the rows in
the local table and re-fetch them from the server by executing a
pass-through/insert query, finally rebinding the combo box to the
refreshed data.

--Mary

On Wed, 03 Dec 2008 09:30:32 +0100, Gijs
<beukenoot.DOT.gijs@a*@gmail.DOT.com> wrote:

Show quoteHide quote

>Hi all,
>
>We are using MS Access as a front end, SQL Server 2005 as the backend.
>Tables are linked using ODBC (they are attached using code). the
>cursorlocation is adUseClient.
>We do not seem to have any problems but occaisionally we seem to 'miss'
>data for a few minutes: Let me illustrate with a simple design:
>Table 1: orders
>Table 2: orderdetails (with a combo that links to the parts)
>Table 3: parts
>Form 1: orders, with a subform orderdetails
>Form 2: parts
>While adding orderdetails using a combo-box, we find that a particular
>part is missing. We've build code that allows the user to double-click
>the combobox 'parts'. User double-clicks it, opens the parts-form, adds
>a record and, when that form is closed, the code refreshes the combo
>(me.cmbParts.requery).
>For, let me say 99.5% of the time, the record is now in the combobox
>and the user can select it. But sometimes, the user comes back into the
>combobox and the record is not there. If the user double-clicks it
>again (going back to the parts) and search for the recently added part,
>it is there.
>Manual refresh (me.requery to refresh the whole form) does not make a
>difference and even closing the form and re-opening it again doesn't
>always solve the issue.
>I am not 100% sure what next steps are taken by the user: closing the
>application or just leaving it 'alone' for a few minutes (?) eventually
>solves the problem but the problem does solve itself; when the user
>tries again later, the record is 'suddenly' there.
>The problem seems to occur more often on a terminal server environment
>where user 1 is in the parts form adding records and user 2 is in the
>order-detail form. The second user does not 'see' the newly added
>records although the form (orders and it's subform orderdetails) is
>closed and re-opened.
>The ODBC driver does get the parameter MARS Connection=True. This was
>added in order to support transactions (begintrans/commit/rollback) in
>code but it turns out that part still doesn't work (the SQL 2000 ODBC
>driver works fine, the SQL 2005 driver doesn't); but that's another
>story.
>
>I've been looking into several possibilties but so far I haven't been
>able to find a cause, let alone find a solution...
>
>Any thoughts here ?
>
>Thanks, GB
>

4 Dec 2008 6:09 AM

Eric Isaacs

I'm just brainstorming here a bit since I can't see your application,
but it might be that the combo box is being requeried before the
insert transaction has committed. Is there any thing such as NOLOCK
in the query that populates the combo box? If there isn't consider
adding it. But NOLOCK is a slippery slope, you will read uncommitted
transactions with it, so you might see stuff that eventually is rolled
back too. Its not something to use for every combo box situation, but
it might be appropriate for this combo box query.

A Me.Requery will requery the main form's recordset, but doesn't
requery the other control recordsets. You should requery the combo
box directly (Me.cmbParts.Requery, as you mentioned) to actually
update the combo box data.

-Eric Isaacs
J Street Technology, Inc.

17 Dec 2008 5:48 PM

Gijs

Thanks for the answers.
@Mary: your answer would require quite a lot programming but it sounds
as the best solution though. Perhaps we will use this for a few test
scenario's and see what the impact will be (on speed for example, we
have forms with (separated over 10 tabs, like 28 different combo's).
@Eric: we're attaching the tables (using a DSN-less ODBC connection)
when the application starts so they are 'treated' by the form as
regular Access tables (I mean, we use Access' SQL statements to
populate the combo's). I don't think Access will understand the NOLOCK
so although the suggestin is good but I am affraid we have to rebuild
the queries to directly use SQL Server instead of using the attached
tables. And no, we're not using any explicit transactions in the code
(that doesn't work as expected with the SQL Native Client). I guess if
there are any, they come from the ODBC driver itself.

Gijs

18 Dec 2008 7:02 PM

Mary Chipman [MSFT]

Here's some additional resources that may shed some light on what's
going on with your application. The first paper on optimizing explains
the interactions between Jet, ODBC and SQLS while the other two have
more examples/samples. --Mary

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

"The Best of Both Worlds--Access MDBs and SQL Server"
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/dp/0672319446

On Wed, 17 Dec 2008 18:48:14 +0100, Gijs
<beukenoot.DOT.gijs@a*@gmail.DOT.com> wrote:

Show quoteHide quote

>Thanks for the answers.
>@Mary: your answer would require quite a lot programming but it sounds
>as the best solution though. Perhaps we will use this for a few test
>scenario's and see what the impact will be (on speed for example, we
>have forms with (separated over 10 tabs, like 28 different combo's).
>@Eric: we're attaching the tables (using a DSN-less ODBC connection)
>when the application starts so they are 'treated' by the form as
>regular Access tables (I mean, we use Access' SQL statements to
>populate the combo's). I don't think Access will understand the NOLOCK
>so although the suggestin is good but I am affraid we have to rebuild
>the queries to directly use SQL Server instead of using the attached
>tables. And no, we're not using any explicit transactions in the code
>(that doesn't work as expected with the SQL Native Client). I guess if
>there are any, they come from the ODBC driver itself.
>
>Gijs
>

18 Dec 2008 8:55 PM

Gijs

Thanks Mary, I'll have a look at these

Mary Chipman [MSFT] :
Show quoteHide quote

> Here's some additional resources that may shed some light on what's
> going on with your application. The first paper on optimizing explains
> the interactions between Jet, ODBC and SQLS while the other two have
> more examples/samples. --Mary
>
> Optimizing Microsoft Office Access Applications Linked to SQL Server
> http://msdn.microsoft.com/en-us/library/bb188204.aspx
>
> "The Best of Both Worlds--Access MDBs and SQL Server"
> http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
>
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/dp/0672319446
>
>