Very Computer

by **Dave Shoa** » Thu, 15 Mar 2001 03:46:17

I am trying to copy four access tables to SQL server tables and I receive
the error:

Run-time error '-2147217871 (80040e31)':
[Microsoft][ODBC SQL Server Driver]Timeout expired

I am using the following code for each table:

Dim adoCon As ADODB.Connection, adoSQLCon As ADODB.Connection
Dim rsSQL As ADODB.Recordset, rsDP As ADODB.Recordset
Dim strCon As String, strSQL As String

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Medstat\translate.mdb;Persist Security Info=False"
Set adoCon = New ADODB.Connection
adoCon.CursorLocation = adUseClient
adoCon.Open strCon

strCon = "Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties='DSN=Hewey;Description=Hewey
;WSID=ISSUPSPEC;DATABASE=DP;Trusted_Connection=Yes'"
Set adoSQLCon = New ADODB.Connection
adoSQLCon.CursorLocation = adUseClient
adoSQLCon.Open strCon

Set rsSQL = New ADODB.Recordset
Set rsDP = New ADODB.Recordset

strSQL = "select * from dental98"
rsSQL.Open strSQL, adoSQLCon, adOpenDynamic, adLockOptimistic

strSQL = "select * from dental_1998"
rsDP.Open strSQL, adoCon, adOpenDynamic, adLockOptimistic

While Not rsDP.EOF
rsSQL.AddNew
rsSQL!field1= rsDP!field1
rsSQL!field2= rsDP!field2
rsSQL.Update
rsDP.MoveNext
Wend

rsSQL.Close
rsDP.Close

adoCon.Close
adoSQLCon.Close

The error occurs on the rsSQL.Update line. How can I find out what is
causing this error or increase the timeout? The tables are quite large, two
have 1 million records each, and two have about 200,000 records. (I get the
error in all four tables.)

Thanks,
Dave

by **SQL Serve** » Wed, 28 Mar 2001 07:38:53

Check out Knowledge Base article Q208386 regarding changing the QueryTimeout
value for ODBC in the registry.

Quote:> I am trying to copy four access tables to SQL server tables and I receive
> the error:

> Run-time error '-2147217871 (80040e31)':
> [Microsoft][ODBC SQL Server Driver]Timeout expired

> I am using the following code for each table:

> Dim adoCon As ADODB.Connection, adoSQLCon As ADODB.Connection
> Dim rsSQL As ADODB.Recordset, rsDP As ADODB.Recordset
> Dim strCon As String, strSQL As String

> strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\Medstat\translate.mdb;Persist Security Info=False"
> Set adoCon = New ADODB.Connection
> adoCon.CursorLocation = adUseClient
> adoCon.Open strCon

> strCon = "Provider=MSDASQL.1;Persist Security Info=False;Extended
> Properties='DSN=Hewey;Description=Hewey
> ;WSID=ISSUPSPEC;DATABASE=DP;Trusted_Connection=Yes'"
> Set adoSQLCon = New ADODB.Connection
> adoSQLCon.CursorLocation = adUseClient
> adoSQLCon.Open strCon

> Set rsSQL = New ADODB.Recordset
> Set rsDP = New ADODB.Recordset

> strSQL = "select * from dental98"
> rsSQL.Open strSQL, adoSQLCon, adOpenDynamic, adLockOptimistic

> strSQL = "select * from dental_1998"
> rsDP.Open strSQL, adoCon, adOpenDynamic, adLockOptimistic

> While Not rsDP.EOF
> rsSQL.AddNew
> rsSQL!field1= rsDP!field1
> rsSQL!field2= rsDP!field2
> rsSQL.Update
> rsDP.MoveNext
> Wend

> rsSQL.Close
> rsDP.Close

> adoCon.Close
> adoSQLCon.Close

> The error occurs on the rsSQL.Update line. How can I find out what is
> causing this error or increase the timeout? The tables are quite large,
two
> have 1 million records each, and two have about 200,000 records. (I get
the
> error in all four tables.)

> Thanks,
> Dave