ODBC Connection failure

[kiwuji]

I've working getting data from DB2 using VB codes. The code that I wrote works for a while (about 3 - 4 minute), then i get a message that say "ODBC call failed"

If anybody knows how to fix my code to prevent ODBC failure, please HELPPPPP!!!!!!

code:
Public Function TransferMonth() As Boolean
Dim auth As Recordset
Dim loc As Recordset
Dim sql As String
Dim SourceTable As String
Dim DestinTable As String
Dim n_rows As Long
Dim FromDate As Date
Dim ToDate As Date
Dim StartTime As Date


StartTime = Time()
n_rows = 0 'row counter
TransferMonth = True 'Transfer has started

'Initialize variable
FromDate = DateSerial(Year(Date), Month(Date) - 3, 1) 'This will return the first date of current month - 3 month
ToDate = DateSerial(Year(Date), Month(Date), 0) 'This will return the last day of the previous month

SourceTable = "DMKIIS_TIIS_MOVEDETL"
DestinTable = "IisDump2"

'Open Recordset

Set loc = CurrentDb().OpenRecordset(DestinTable, dbOpenDynaset, dbDenyWrite) 'Open Destination Table
sql = "SELECT ACCRUAL_DATE, TC_IND, LE_IND, CLASS_TRAFFIC, SHIP_CITY, FROM " & SourceTable & " WHERE (ACCRUAL_DATE BETWEEN #" & FromDate & "# and #" & ToDate & " #) GROUP BY ACCRUAL_DATE, SHIP_CITY, TC_IND, LE_IND, CLASS_TRAFFIC"

Set auth = CurrentDb().OpenRecordset(sql, dbOpenDynaset, dbSeeChanges) 'Open Source Table

auth.CacheSize = 1200
auth.FillCache
Do While Not auth.EOF
If auth!REV1 <> 0 And Year(auth!ACCRUAL_DATE) = Year(ToDate) Then
loc.AddNew
loc!Month = Month(auth!ACCRUAL_DATE)
loc!SHIP_CITY = auth!SHIP_CITY
.
.
loc.update
end if
auth.MoveNext

n_rows = n_rows + 1
If (n_rows Mod 1200) = 0 Then
auth.CacheStart = auth.Bookmark
auth.FillCache
Debug.Print n_rows
End If
loop
end function

 

[johndweidauer]

I don't see a line of code that tells your module what to connect to. Looks like your using DAO, you would need to either specify an ODBC to use as the connection, or if its in the same database, db.open currentdb()

 

[johndweidauer]

sorry:

set db = currentdb()

 

[kiwuji]

johndweidauer :
SourceTable = &quot;DMKIIS_TIIS_MOVEDETL&quot; is an ODBC Link table statically created in Access using Link manager tool. Does make a difference using DAO verus ADO? (performance?)
Thanks,
Kurz

 

[cmmrfrds]

Your query should be faster and cleaner by using an ADO connection. You will need an OLEDB Provider for DB2.

Possible providers:

http://ourworld.compuserve.com/homepages/Ken_North/oledbVen.htm

For ODBC, check you setup and see if you can change the timeout setting.