this is the vba,

Dim rs As DAO.Recordset
Dim wrk As DAO.Workspace

Set wrk = DBEngine.Workspaces(0)
Set db = wrk.Databases(0)
wrk.BeginTrans
On Error GoTo fRoll
strSql = "SELECT a.handoverId, a.lineNbr" & _
" FROM tblSlsDetail as a LEFT JOIN" & _
" baandb_ttdsls901228 as b ON a.handoverId =
b.t_hand" & _
" AND a.lineNbr = b.t_pono"
& _
" WHERE a.handoverId = " & lngHandoverId & _
" AND nz(b.t_ssta, 0) <> " & cProcessed
Set rs = db.OpenRecordset(strSql)
While (Not (rs.EOF))
db.execute "update ...."
db.execute "delete ...." (x 11)
rs.moveNext
wend

the rs.movenext fails, with "ODBC--call failed."
all the tables are linked to sql server
if I only execute 8 delete statements I have no problem, in
production, I can see 100s of statements
my temp folder is on a drive with GB's of spaces
is there a registry entry to increase the number of statements ?
or should I just use BEGIN TRANSACTION in sql server ?

Historically, ODBC has been problematic for performing action queries
(Inserts/Updates/Deletes) on tables contained in a Sql Server DB from
Access. There are workarounds, but I find the easiest technique is to
use ADO when manipulating Sql Server objects from Access. Here is a
routine you could try out

note: you need to make a reference in a code module from Tools/Rerences
to

Microsoft ActiveX Data Objects 2.5 (or higher) Library

Sub ActionQueriesOnSqlServer()
Dim cmd As New ADODB.Command, j As Long

'--if you are using Windows authentication you can use
'--this connection string
'cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=YourDB;Trusted_Connecti on=Yes"

'--if you are using Sql Server authentication then use
'--this connection string
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=YourServer;Database=YourDB;UID=steve;passwo rd=tiger;"

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText

cmd.CommandText = "Delete tblx Where ID = " & txtID
cmd.Execute j, , adExecuteNoRecords
Debug.Print j & " records Deleted"

cmd.CommandText = "Update tblx seled fld1 = 'test' Where ID = " &
txtID
cmd.Execute j, , adExecuteNoRecords
Debug.Print j & " records Updated"

cmd.ActiveConnection.Close

End Sub

Rich

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

On Jun 8, 3:35*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:
looks good, but I just remembered, some tables are on one sql server
and other tables are on a second sql server
and both need to be updated within the same transaction, can I do that
with ADO ?

On Tue, 8 Jun 2010 15:09:33 -0700 (PDT), Roger
<lesperancer (AT) natpro (DOT) com> wrote:

I would recommend you implement the transaction in a stored procedure.
You would create a Linked Server so the main SqlServer knows about the
other one, and you can write queries using a 4-part name:
select * from myLinkedServer.myDatabase.dbo.myTable

Implement your transaction in the stored procedure, and make sure
MSDTC = Distributed Transaction Coordinator is running, and you will
have the full protection of transactions.

-Tom.
Microsoft Access MVP

On Jun 8, 8:12*pm, Tom van Stiphout <tom7744.no.s... (AT) cox (DOT) net> wrote:
thanks, but if I use ADO with this connection string
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
CurrentDb.name & ";"
cnn.CursorLocation = adUseClient
cnn.CommandTimeout = 600

I can work with the existing linked tables without worrying about
which server(s) are used, be it development or production

There is no limit on the number of statements you can execute.

The problems come from the way SQL Server locks tables
(which has changed since 1997), and on the way Access
caches connections (which probably is not your problem
with Access 97).

In any case, Jet DAO/SQL Server transactions are very much
broken in all later versions of Access/Jet against all later versions
of SQL Server, so this DAO/ODBC code is a dead end in
that respect.

(david)


"Roger" <lesperancer (AT) natpro (DOT) com> wrote