Timeout in VB

[Chakey]

I open a connection in an Access module. I then want to create a recordset
and populate it from a view that I am linked to through Access. While the
table behind the view is quite large in both fields and records, the view
limits it to the last 2 months and only the 2 fields I need for my
application. The view also groups and sorts the data by the 2 fields so I
don't have to do that with code.

I have no problem creating the recordset from the main table, but filtering
by my date range takes forever, though it does not timeout. Also there is no
grouping or sorting. However, now I timeout. I have seen in the posts that
there are ways to increase timeout durations, but don't know how to apply the
code to an already open connection when running an SQL query (within VB) to
build the recordset. What do I do?
Thanks for your time.

 

[Ralph]

Assuming you are using adodb?

dim myConn as adodb.connection
set myConn=new adodb.connection
myConn.CursorLocation=adUseServer
myConn.CommandTimeout=0

 

[Chakey]

I still get the timeout error.
Here is my code(I just added the CursorLocation and CommandTimeout lines you
suggested - to no avail):

Dim cnn As New ADODB.Connection
Dim strEmpDays As String
Dim strHoliday As String
Dim strcnn As String
Dim StartDate As Date
Dim EndDate As Date

cnn.CursorLocation = adUseServer
cnn.CommandTimeout = 0

StartDate = DLookup("[StartDate]", "BeginDate_t")
EndDate = DateAdd("d", 13, StartDate)

strcnn = "Data Source='Global_MWP';User ID='Master';Password='master';"
cnn.Open strcnn
strEmpDays = "SELECT * FROM VM_TIME_ATND_HST_2M;"
rstEmpDays.Open strEmpDays, strcnn, adOpenStatic, adLockReadOnly
(Here is where I get the timeout error)

 

[Ralph]

Try
cnn.ConnectionTimout=0

I still get the timeout error.
Here is my code(I just added the CursorLocation and CommandTimeout lines you
suggested - to no avail):

Dim cnn As New ADODB.Connection
Dim strEmpDays As String
Dim strHoliday As String
Dim strcnn As String
Dim StartDate As Date
Dim EndDate As Date

cnn.CursorLocation = adUseServer
cnn.CommandTimeout = 0

StartDate = DLookup("[StartDate]", "BeginDate_t")
EndDate = DateAdd("d", 13, StartDate)

strcnn = "Data Source='Global_MWP';User ID='Master';Password='master';"
cnn.Open strcnn
strEmpDays = "SELECT * FROM VM_TIME_ATND_HST_2M;"
rstEmpDays.Open strEmpDays, strcnn, adOpenStatic, adLockReadOnly
(Here is where I get the timeout error)

Assuming you are using adodb?

dim myConn as adodb.connection
set myConn=new adodb.connection
myConn.CursorLocation=adUseServer
myConn.CommandTimeout=0

 

[Chakey]

I am still having trouble even after switching to cnn.ConnectionTimeout = 0.
I get this message when performing the query in the code I mentioned in my
last post:
"Run-time error '-2147217871 (80040e31)':
[Pervasive][ODBC Client Interface][LNA][Pervasive}{ODBC Engine
Interface]S1T00Timeout expired."

Pervasive is our database engine.
I am stumped.

Try
cnn.ConnectionTimout=0

I still get the timeout error.
Here is my code(I just added the CursorLocation and CommandTimeout lines you
suggested - to no avail):

Dim cnn As New ADODB.Connection
Dim strEmpDays As String
Dim strHoliday As String
Dim strcnn As String
Dim StartDate As Date
Dim EndDate As Date

cnn.CursorLocation = adUseServer
cnn.CommandTimeout = 0

StartDate = DLookup("[StartDate]", "BeginDate_t")
EndDate = DateAdd("d", 13, StartDate)

strcnn = "Data Source='Global_MWP';User ID='Master';Password='master';"
cnn.Open strcnn
strEmpDays = "SELECT * FROM VM_TIME_ATND_HST_2M;"
rstEmpDays.Open strEmpDays, strcnn, adOpenStatic, adLockReadOnly
(Here is where I get the timeout error)

Assuming you are using adodb?

dim myConn as adodb.connection
set myConn=new adodb.connection
myConn.CursorLocation=adUseServer
myConn.CommandTimeout=0


:

I open a connection in an Access module. I then want to create a recordset
and populate it from a view that I am linked to through Access. While the
table behind the view is quite large in both fields and records, the view
limits it to the last 2 months and only the 2 fields I need for my
application. The view also groups and sorts the data by the 2 fields so I
don't have to do that with code.

I have no problem creating the recordset from the main table, but filtering
by my date range takes forever, though it does not timeout. Also there is no
grouping or sorting. However, now I timeout. I have seen in the posts that
there are ways to increase timeout durations, but don't know how to apply the
code to an already open connection when running an SQL query (within VB) to
build the recordset. What do I do?
Thanks for your time.

 

[Ralph]

Try this link:
http://www.tek-tips.com/viewthread.cfm?qid=1077071&page=1

I am still having trouble even after switching to cnn.ConnectionTimeout = 0.
I get this message when performing the query in the code I mentioned in my
last post:
"Run-time error '-2147217871 (80040e31)':
[Pervasive][ODBC Client Interface][LNA][Pervasive}{ODBC Engine
Interface]S1T00Timeout expired."

Pervasive is our database engine.
I am stumped.

Try
cnn.ConnectionTimout=0

I still get the timeout error.
Here is my code(I just added the CursorLocation and CommandTimeout lines you
suggested - to no avail):

Dim cnn As New ADODB.Connection
Dim strEmpDays As String
Dim strHoliday As String
Dim strcnn As String
Dim StartDate As Date
Dim EndDate As Date

cnn.CursorLocation = adUseServer
cnn.CommandTimeout = 0

StartDate = DLookup("[StartDate]", "BeginDate_t")
EndDate = DateAdd("d", 13, StartDate)

strcnn = "Data Source='Global_MWP';User ID='Master';Password='master';"
cnn.Open strcnn
strEmpDays = "SELECT * FROM VM_TIME_ATND_HST_2M;"
rstEmpDays.Open strEmpDays, strcnn, adOpenStatic, adLockReadOnly
(Here is where I get the timeout error)

:

Assuming you are using adodb?

dim myConn as adodb.connection
set myConn=new adodb.connection
myConn.CursorLocation=adUseServer
myConn.CommandTimeout=0


:

I open a connection in an Access module. I then want to create a recordset
and populate it from a view that I am linked to through Access. While the
table behind the view is quite large in both fields and records, the view
limits it to the last 2 months and only the 2 fields I need for my
application. The view also groups and sorts the data by the 2 fields so I
don't have to do that with code.

I have no problem creating the recordset from the main table, but filtering
by my date range takes forever, though it does not timeout. Also there is no
grouping or sorting. However, now I timeout. I have seen in the posts that
there are ways to increase timeout durations, but don't know how to apply the
code to an already open connection when running an SQL query (within VB) to
build the recordset. What do I do?
Thanks for your time.