DSN Connection Faster than DSN-less??

[drost]

I am in the process of converting a program from a DSN connection using the Workspace object to ADO - DSN-less. I have the program completed, but with ADO, it runs much slower. Can anyone give me any tweaking advice?

Here's my connection:

With conServer
.ConnectionString = "Provider=SQLOLEDB;" & _
&quot;INITIAL CATALOG=<DBName>;&quot; & _
&quot;USER ID=<UID>;&quot; & _
&quot;PASSWORD=<PWD>;&quot; & _
&quot;DATA SOURCE=<Datasource>&quot;
.Open
End With

Here's my recordset:

Set rstServer = New ADODB.Recordset
With rstServer
.ActiveConnection = conServer
.CursorLocation = adUseServer
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
End With

The program is for reporting and runs multiple small sql statements that each return a count, so the recordset of each is a single record.

Thanks,

drost

 

[shmiller]

Maybe try
.CursorLocation = adUseClient

 

[drost]

Thanks, shmiller.

I've tried that and it doesn't help. It may even be slightly slower, which would make sense since it's running from the server instead of loading a 'copy' to the local system.

Thanks for your post.

drost