ODBC DAO Faster than ADO?!?

[RobS23]

I'm trying, successfully in most cases, to extract data from systems dependent on a system DSN. Oddly if I use a simple query file and run this through excel I have no problems at all.

However, when wrapping the query into an application using the following very innocuous code I often have a timeout problem.

Would it be quicker to use DAO?


Dim adoConn As New ADODB.Connection
Dim adoRS As New ADODB.Recordset


sqltext = "SELECT datatable.purchase_date, etc etc "')"

'Open DSN Connection
Set adoOutfile = fso1.CreateTextFile("C:\ Query.txt", True)


adoOutfile.Write (sqltext)
adoOutfile.Close


adoConn.Open "DSN=mydsn;DATABASE=mydatabase;UID=myuid;"

adoRS.Source = sqltext

Set adoRS.ActiveConnection = adoConn

adoRS.Open


If I'm missing something please do let me know!!!

 

[Golom]

Am I reading this correctly?

You are creating a TextStream file (adoOutFile) and writing the SQL statement for a query to that file.

You are then independently opening an ADO recordset using the same SQL.

Does the TextStream (adoOutFile) have anything to do with the recordset (adoRS)? What's timing out? The TextStream or the Recordset?

What DBMS are you using? (Access?, SQL Server?, ...)

 

[RobS23]

Ah that's just some debugging. The results of the recordset get written to the file once the data is retrieved. Nothing to do with the timeout probs.

 

[BobRodes]

Personally, I wouldn't use ADO's ODBC connection, I'd use its OLE DB connection, as in

adoConn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mydsn"

This assumes that you're using SQL Server as your back end, and is a more direct way of accessing the SQL Server back end. Perhaps it will solve your timeout issues.

I wouldn't use DAO. DAO is really optimized for Jet; at the time that ODBC came out, RDO was considered the optimal choice for it.

HTH

Bob

 

[RobS23]

Thanks Fellas.

I'm going to have to run a few tests. It's a mumps type db with a "relational mapping" sat on top. So we'll have to see.