Access2003 - sql server 2005, db.execute times out updating sqlserver data

[Roger]

This is the vba code

Set db = CurrentDb
On error goto fErr
…. loop
strSql = "UPDATE tblInvAnalysisWhse" & _
" SET ltDeviation = " & Sqr(dblError /
intPeriods) & _
" WHERE item = '" & Trim(strItem) & "'" & _
" AND location = " & intLocation
dblCount = dblCount + 1
db.Execute strSql, dbFailOnError

fErr:
stop
resume next

db.execute always fails after 1246 iterations, with
- [Microsoft][ODBC SQL Server Driver]Timeout
expired ,ODBC.Database ,0
- ODBC--update on a linked table 'tblInvAnalysisWhse'
failed. ,DAO.Database ,3157
'tblInvAnalysisWhse’ has 107,000+ records, and is a linked sql server
table
Columns item & location are indexed

Strsql on failure is
UPDATE tblInvAnalysisWhse
SET ltDeviation = 9
WHERE item = 'P103 L25348' AND location = 21

If I try to execute the UPDATE statement in sql server, it never
completes (8 mins and running)
There’s nothing in the sql server logs
And my process only has 20 object locks
Until I 'stop' running the ms-access function, and then the sql server
statement completes immediately
Of course, all the locks are gone

so the problem has to do with locks

So what do I need to do, in a vba loop, to free up my sql server locks
after each update statement ?
create a transaction ?
create a passthrough query to do the update ?

[Rich P]

This is a limitation with Access and ODBC against a sql server. You
would be way better off using ADO to perform this operation - you get
way more bandwidth and can circumvent the record locking issue more
effectively because ADO does not hold the connection open continuously
like ODBC and you can set a timeout with ADO.

A more ideal solution would be to perform this type of operation with
ADO.Net -- but that is .Net country.

Classic ADO (ADO) was specifically designed for interfacing com based
apps (VB6, Access, Excel) with sql server (and Oracle, DB2).

My recommendation would be to use ADO for this operation. There may be
some tweakage you could do to make the ODBC setup work better - I don't
know it - but it is tweakage. ADO is straight forward - simple. Here
is a sample:

----------------------------------------

'--make a reference to Microsoft ActiveX Data Object 2.5 (or higher)
library

Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=YourDB;Trusted_Connecti on=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText

Do While Not RS.EOF
cmd.CommandText = "Update tblz Set fld5 = ' & rs(1) & ' Where fld1 =
'ddd'"
cmd.Execute
Loop

----------------------------------------

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Roger]

On Oct 27, 4:57*pm, Rich P <[e-mail]:

This is a limitation with Access and ODBC against a sql server. *You
would be way better off using ADO to perform this operation - you get
way more bandwidth and can circumvent the record locking issue more
effectively because ADO does not hold the connection open continuously
like ODBC and you can set a timeout with ADO.

A more ideal solution would be to perform this type of operation with
ADO.Net -- but that is .Net country.

Classic ADO (ADO) was specifically designed for interfacing com based
apps (VB6, Access, Excel) with sql server (and Oracle, DB2). *

My recommendation would be to use ADO for this operation. *There may be
some tweakage you could do to make the ODBC setup work better - I don't
know it - but it is tweakage. *ADO is straight forward - simple. *Here
is a sample:

----------------------------------------

'--make a reference to Microsoft ActiveX Data Object 2.5 (or higher)
library

Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=YourDB;Trusted_Connecti on=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText

Do While Not RS.EOF
* cmd.CommandText = "Update tblz Set fld5 = ' & rs(1) & ' Where fld1 =
'ddd'"
* *cmd.Execute
Loop

----------------------------------------

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

ok, I tried the ado method proposed, and I get the same error after
1246 'updates'
I also tried, connecting before each update and disconnecting
afterwards

Dim cmd As ADODB.Command

Do While Not RS.EOF
set cmd = new adodb.command
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=YourDB;Trusted_Connecti on=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText
cmd.CommandText = "Update tblz Set fld5 = ' & rs(1) & ' Where fld1
=
'ddd'"
cmd.Execute
set cmd = nothing
Loop

and it still fails after 1269 passes...

what am I missing ?
ps. I tried both encapsulating the updates in a 'transaction' as well
as using a passthrough query... same problem, same number of passes

[Roger]

On Oct 28, 12:58*pm, Roger <[e-mail]:

On Oct 27, 4:57*pm, Rich P <[e-mail]:

This is a limitation with Access and ODBC against a sql server. *You
would be way better off using ADO to perform this operation - you get
way more bandwidth and can circumvent the record locking issue more
effectively because ADO does not hold the connection open continuously
like ODBC and you can set a timeout with ADO.

A more ideal solution would be to perform this type of operation with
ADO.Net -- but that is .Net country.

Classic ADO (ADO) was specifically designed for interfacing com based
apps (VB6, Access, Excel) with sql server (and Oracle, DB2). *

My recommendation would be to use ADO for this operation. *There may be
some tweakage you could do to make the ODBC setup work better - I don't
know it - but it is tweakage. *ADO is straight forward - simple. *Here
is a sample:

----------------------------------------

'--make a reference to Microsoft ActiveX Data Object 2.5 (or higher)
library

Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=YourDB;Trusted_Connecti on=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText

Do While Not RS.EOF
* cmd.CommandText = "Update tblz Set fld5 = ' & rs(1) & ' Where fld1 =
'ddd'"
* *cmd.Execute
Loop

----------------------------------------

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

ok, I tried the ado method proposed, and I get the same error after
1246 'updates'
I also tried, connecting before each update and disconnecting
afterwards

Dim cmd As ADODB.Command

*Do While Not RS.EOF
* *set cmd = new adodb.command
* *cmd.ActiveConnection = "Provider=SQLOLEDB; Data
* *Source=yourServer;Database=YourDB;Trusted_Connect ion=Yes"
* *cmd.ActiveConnection.CursorLocation = adUseClient
* *cmd.CommandTimeout = 600
* *cmd.CommandType = adCmdText
* *cmd.CommandText = "Update tblz Set fld5 = ' & rs(1) & ' Where fld1
=
*'ddd'"
* * cmd.Execute
* * set cmd = nothing
*Loop

and it still fails after 1269 passes...

what am I missing ?

ps. *I tried both encapsulating the updates in a 'transaction' as well
as using a passthrough query... same problem, same number of passes- Hidequoted text -

- Show quoted text -

went back to using a passthrough query but changed the function to
populate the passthrough with 500 update statements at a time before
executing it

and it fails with timeout on the third execute (1001 - 1500th
updates)..
so why can't sql server process more than 1269 updates
and why does 'stopping' code execution allow the passthrough to then
execute successfully ?

[Rich P]

Hi Roger,

Note: I don't think the problem was with ODBC after all. The problem
may be with the data at a certain point in your procedure.

Try this: go back to the ADO loop (or ODBC - don't think it matters at
this point) - add a loop counter so that you know at what iteration of
the loop you have the problem. I think the problem is with the
parameter(s) you are passing or the row you are trying to update at that
iteration. You want to isolate that row (or params you are passing).
Once you have clearly identified that row or param - use a where clause
to exclude it and see if your procedure runs.

Another thought would be to try running your procedure from a starting
point after the point where you are encountering your problem. If the
procedure runs OK after that point - then there is a problem with the
data - most likely some null/nulling issue where the query is trying to
guess what to do but there is nothing there for it to do anything with
at that point (I will take a guess that maybe at the point whwere the
timeout occurs the problem may be in your where clause - not accounting
for somethign at some row).
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Roger]

On Oct 29, 9:18*am, Rich P <[e-mail]:

Hi Roger,

Note: *I don't think the problem was with ODBC after all. *The problem
may be with the data at a certain point in your procedure.

Try this: *go back to the ADO loop (or ODBC - don't think it matters at
this point) - add a loop counter so that you know at what iteration of
the loop you have the problem. *I think the problem is with the
parameter(s) you are passing or the row you are trying to update at that
iteration. *You want to isolate that row (or params you are passing).
Once you have clearly identified that row or param - use a where clause
to exclude it and see if your procedure runs. *

Another thought would be to try running your procedure from a starting
point after the point where you are encountering your problem. *If the
procedure runs OK after that point - then there is a problem with the
data - most likely some null/nulling issue where the query is trying to
guess what to do but there is nothing there for it to do anything with
at that point (I will take a guess that maybe at the point whwere the
timeout occurs the problem may be in your where clause - not accounting
for somethign at some row).

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

since I was creating a passthrough query, with 500 update statements
and it failed on the 3rd pass, I copied all 1500 update queries into
one sql server statement

it runs without a problem

since I already had a counter has you suggested, and it indicates that
the failure occurs on the 1269th iteration, and since sql server can
process all 1500 statements, I'm guess the problem is elsewhere

but the same function works fine in access97, accessing the same sql
server 2005 tables....

so I'm going to try an adodb recordset instead of a dao recordset in
this loop
Do While Not RS.EOF
cmd.CommandText = "Update tblz Set fld5 = ' & rs(1) & ' Where fld1
=
'ddd'"
cmd.Execute
Loop

[Roger]

On Oct 29, 9:44*am, Roger <[e-mail]:

On Oct 29, 9:18*am, Rich P <[e-mail]:

Hi Roger,

Note: *I don't think the problem was with ODBC after all. *The problem
may be with the data at a certain point in your procedure.

Try this: *go back to the ADO loop (or ODBC - don't think it matters at
this point) - add a loop counter so that you know at what iteration of
the loop you have the problem. *I think the problem is with the
parameter(s) you are passing or the row you are trying to update at that
iteration. *You want to isolate that row (or params you are passing).
Once you have clearly identified that row or param - use a where clause
to exclude it and see if your procedure runs. *

Another thought would be to try running your procedure from a starting
point after the point where you are encountering your problem. *If the
procedure runs OK after that point - then there is a problem with the
data - most likely some null/nulling issue where the query is trying to
guess what to do but there is nothing there for it to do anything with
at that point (I will take a guess that maybe at the point whwere the
timeout occurs the problem may be in your where clause - not accounting
for somethign at some row).

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

since I was creating a passthrough query, with 500 update statements
and it failed on the 3rd pass, I copied all 1500 update queries into
one sql server statement

it runs without a problem

since I already had a counter has you suggested, and it indicates that
the failure occurs on the 1269th iteration, and since sql server can
process all 1500 statements, I'm guess the problem is elsewhere

but the same function works fine in access97, accessing the same sql
server 2005 tables....

so I'm going to try an adodb recordset instead of a dao recordset in
this loop
Do While Not RS.EOF
* cmd.CommandText = "Update tblz Set fld5 = ' & rs(1) & ' Where fld1
=
'ddd'"
* *cmd.Execute
Loop- Hide quoted text -

- Show quoted text -

changing RS to an ADODB recordset solves the problem, I'm just not
sure why

[Rich P]

Not to knock ODBC, but I have consistently had less problems and way
more performance using ADO (ADODB) against the sql server from Access
(and Excel). But if you will be interfacing with sql server on a
consistent basis (like from now forward) I would recommend stepping up
to ADO.Net. ADODB supercedes ODBC, and ADO.Net supercedes ADODB. The
improvements between ADO.Net and ADODB is more substantial than the
improvements between ADODB and ODBC. To Date: nothing commercial can
outperform ADO.Net against any current RDBMS (Sql server, Oracle of the
ones I know of).

Rich

*** Sent via Developersdex http://www.developersdex.com ***