Issue updating SQL 2000 DB with "No Count" turned on
-
HI Guys/gals.. I have a developer who has written a bunch of apps that use Access to update a SQL back end.. we are moving them to a controlled environment where we have No Count set on.. this breaks his apps here is an example.. Anyone know any work arounds?
-----
Option Compare Database
Option Explicit
Const strDBConnection = "PROVIDER=SQLOLEDB;DATA SOURCE=QADBSVR;Initial Catalog=TargetDB;Trusted_Connection=Yes"
Sub test_Insert()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rec As New ADODB.Recordset
cn.ConnectionTimeout = 600
cn.CommandTimeout = 600
cn.Open strDBConnection
cn.Execute ("Delete from TartgetDB_Temp")
rec.Open "tmpTransactions", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.Open "TartgetDB_Temp", cn, adOpenDynamic, adLockOptimistic
Do Until rec.EOF
rs.AddNew
rs(0) = rec(0)
rs(1) = rec(1)
rs(2) = rec(2)
rs(3) = rec(3)
rs(4) = rec(4)
rs(5) = rec(5)
rs(6) = rec(6)
rs(7) = rec(7)
rs(8) = rec(8)
rs(9) = rec(9)
rs(10) = rec(10)
rs(11) = rec(11)
rs(12) = rec(12)
rs(13) = rec(13)
rs.Update -- Breaks here
Loop
On Error Resume Next
rec.Close
Set rec = Nothing
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
-
Open up the profiler and run this code. Grad the insert statement sent by access. Then rerun it in query analyser. You should get a more usefull error message.
I would suspect a data conversion error or an update on a identity column / calculated field at this point... but I can't tell without the actual error.
-
Also this would run MUCH MUCH faster :
Insert into DbName.dbo.Tablename (col1, col2, coln) Select Col1, Col2, Coln from Db2.dbo.TableName.
-
Thanks Ninja.. I would have preferred he had done it like that.. but as always.. he doesn't have time to do a rewrite.. So in any case I figured out the issue.. Since he is using ADO and opening a cursor serverside, Access gets confused when the nocount is set to on. So the key was to add in a line in his code setting cursors to run client side. And it worked like magic..Thanks again
-
What rewirte??? Takes 2 minutes to write this and test!
-
lol.. He insists that he would have to rewrite most of his apps if he were to move away from ADO.. He is a silly goose for sure, but we don't have time for him to futz with them at the moment..
-
How can a whole application depend on a single SUB? I mean it's not like we're asking him to change the MAIN() FUNCTION!!!
Anyways as long as it works and no one suffers from this, I can let it go.
For now!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply