Hello;
MSAccess 2000, MSsql Server 2000

I have an append query. When I run it, it might run once succesfully.
If I run it again (with new test data), it times out-and keeps timing
out. I have added the following to help resolve this:
1) close db connection
2) re-connect db connection
3) implement 10 second pause
4) increase timeout threshold
These steps have not eliminated the timeout
problem.

If anybody has any ideas on what I can do, please let me know.

Here is a scaled down version of the code:

var declar for
String, ADODB.Connection, Recordset and Database

set statements

cn.Open "DSN=dsnName;" _
& "Server=SERVERNAME;" _
& "Database=TESTDBNAME;" _
& "UID=userid;" _
& "PWD=password;"
cn.Close
Set Mydb = Nothing

Dim PauseTime, Start
PauseTime = 10
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop

set statements (again)

cn.Open "DSN=dsnName;" _
& "Server=SERVERNAME;" _
& "Database=TESTDBNAME;" _
& "UID=userid;" _
& "PWD=password;"

Set rs = Mydb.OpenRecordset("ACCESStbl", dbOpenDynaset)

Mydb.QueryTimeout = 240

If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
custid = rs.Fields("ID")

strSql = "INSERT INTO SQLtbl (fld1, fld2, fld3, fld4, fld5)" & _
"SELECT SQLtb2.fld1, SQLtb2.fld2, 'D' AS TYPE, SQLtb3.fld1,
SQLtb3.fld2 " & _
"FROM SQLtb2 INNER JOIN SQLtb3 ON SQLtb2.fld1 = SQLtb3.fld1 "
& _
"WHERE (((SQLtb2.fld1) = " & "'" & custid & "'" & "));"

cn.Execute strSql

custid = ""
strSql = ""
rs.MoveNext
Loop
End If
cn.Close
Set cn = Nothing

End Function

Tony_E wrote:
You have failed to tell us exactly what is timing out. Typically the error
message indicates the source of the timeout. It never hurts to tell us the
exact text of your error message.

I think I can see why you thought this might help, but it doesn't, so you
should rip out that part of the code now. It has nothing to do with your
problem and will not help.

Again, nothing to do with your problem

If I'm correct in my assumption as to the source of the timeout, you just
think you did.

<snip>
Too late. This should be set before the OpenRecordset statement...
And if I'm correct that it's the ADO command that's timing out, this setting
is for the DAO database object and will have no effect on the ADO
connection.

What you need to be concerned with is the ADO connection's CommandTimeout
setting, which defaults to 30 (seconds). If you set it to 0, it will never
time out which is probably not a good idea. It is better to increase it in
small increments to determine the minimum setting needed.

cn.CommandTimeout=60

Better still is to find out why the query is taking so long. The appropriate
tools are Enterprise Manager, SQL Profiler and Query Analyzer. There is
undoubtedly some blocking (multiple processes competing for the same
locks/resources) going on on your server leading to the timeouts and you
need to discover the cause of that. Do some research on troubleshooting
blocking issues in SQL Server, there is only so much help we can provide in
the newsgroups.

<snip>
When you open a recordset it is already pointing at the first record, so the
above, while not hurting anything, is unnecessary.

<snip>

Unlikely to have anything to do with your problem, but I like to be very
explicit. This string variable contains text that needs to be parsed and
executed, and it does not return records which makes the default ADO
behavior of creating a recordset behind the scenes to receive results a
waste of time and resources. Best Practices include telling ADO what is
being executed and how to handle, or not handle, results of the execution.
Like this:

cn.Execute strSql, , adCmdText + adExecuteNoRecords

How many records are in that DAO recordset? If relatively few (less than
100, say), I would prefer to do something like this:

Dim CustIDList as string: CustIDList =""
'loop through the DAO recordset building a comma-delimited list of custids
Do until rs.eof
custid = rs.Fields("ID")
if CustIDList ="" then
CustIDList ="'" & custid & "'"
else
CustIDList = CustIDList & ",'" & custid & "'"
end if
rs.movenext
Loop
'You can close the DAO recordset now
strSql = "INSERT INTO SQLtbl (fld1, fld2, fld3, fld4, fld5)" & _
"SELECT SQLtb2.fld1, SQLtb2.fld2, 'D' AS TYPE, SQLtb3.fld1,
SQLtb3.fld2 " & _
"FROM SQLtb2 INNER JOIN SQLtb3 ON SQLtb2.fld1 = SQLtb3.fld1 "
& _
"WHERE SQLtb2.fld1 IN (" & CustIDList & ");"
debug.print strSQL
'Open ADO connection now, and execute the single sql statement.

If the DAO recordset contains a lot of records (but not too many - perhaps
less than 1000), I would insert the custids into a column in a sql server
table, either using a linked table with one-at-a-time (singleton) inserts,
or dynamically building a union query inserting the results into a temp
table, and create a join to that table in the sql being executed on the sql
server. Here is an example of the latter:


strSql ="create table #tmp (custid varchar(10));" & _
"insert into #tmp(custid) "
'open DAO recordset
custid = rs.Fields("ID")
strSql = strSql & "SELECT '" & custid & "'"
rs.movenext
Do Until rs.eof
custid = rs.Fields("ID")
strSql = strSql & " UNION ALL SELECT '" & custid & "'"
rs.movenext
Loop
'You can close the DAO recordset now
strSql = strSql & _
"; INSERT INTO SQLtbl (fld1, fld2, fld3, fld4, fld5)" & _
"SELECT SQLtb2.fld1, SQLtb2.fld2, 'D' AS TYPE, " & _
SQLtb3.fld1,SQLtb3.fld2 " & _
"FROM SQLtb2 INNER JOIN SQLtb3 " & _
"ON SQLtb2.fld1 = SQLtb3.fld1 " & _
"JOIN #tmp ON SQLtb2.fld1 = #tmp.custid; " & _
"DROP TABLE #tmp"
debug.print strSQL
'Open ADO connection now, and execute the single sql statement.

If there are more than 1000 custids, I would use TransferDatabase to export
the data to a sql server table and join to it.

On Apr 28, 10:43*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Yes the timeout occurs on cn.Execute strSql

Thanks very much for your valuable input. I will analyze. Right now
just got assigned something else so I will return to this later.

Thanks
Tony