i use vb 6 classic for my project.

my conn:
my sql open:

this operation i very very slow!!!!

note:
CONTAB field is indexed
TEST_CONTAB is dimensioned as string

is correct to set the connection cursor to AdUserserver????

This:
is useless when connecting to an Acces (.mdb) database. adUseServer only works with some (i.e. not all) Database Servers. When working with an Access database and Jet, everything happens on the client side. See: adUseServer versus adUseClient - Microsoft Access / VBA

I you're looking for performances, and as far as an Access database is concerned and that your application would allow it (i.e. you do not need ADO specific features that have no equivalent in DAO), I would use the DAO library instead of the ADODB library.

For performance topics in general, see also:Improving MDAC Application Performance

TKS for reply .... but u suggest me tu use DAO, in this case, or not?

probably best to ask this in the VB forum, its not really an Access question
FWIW
I would experiment with the maxlocks perfile 1.5million seems a heck of a lot and I suspect each lock may consume soem resources

I doubt changing the cursor location to server will make any difference on a JET database

Yes I do. I know from experience that using the DAO library is usually (not always) faster than using ADODB on the same Access database. Things would be different if the database would reside on a SQL Server and you could use a "true" Server-Side cursor.

DAO is optimized for access. That being said, ADO isn't THAT much slower than DAO, and shouldn't be the cause of a very slow Select statement.

Instead, try using use a static connection, in conjunction with your read-only lock. (and, if you are going to iterate through the whole recordset once, use a forward-only recordset.

In any event, the RST0.CursorLocation = adUseClient overrides the connection's CursorLocation, which would be used when a cursor location is not explicitly defined (or when using the command object.)

And, as others have said, reduce the number of file locks.

If you're going to be iterating through all the records in the recordset, be sure to use explicit instantiation (as you have done,) use the WITH/END WITH construct to reduce the number of object validations, and to use integer field numbers or names (rs.Field(0).Value or rs.Fields"FieldName").Value,) as a field reference, instead of the rs.Fields!FieldName convention.

You can also add indexes on the fly, and use the recordset.filter property to pare down your data to a more manageable set. This approach is HUGELY faster than iterating through the entire recordset, searching for a value.

Finally, IF you can open the data file exclusively, you may have better response time. Before opening the connection, set it's mode to exclusive access, as follows:

One other point: IF the database resides on a different machine (you are accessing it over the network) database access is much slower than if the file is located on a local drive. Inserts are particularly slow: in one case, I had several hundred thousand inserts that needed to be made: over the network, the inserts were taking place at about 3 per second; When I temporarily moved the file to my computer, the insert rate was approximately 350 per second... As I understand it, one of the major bottlenecks is the poor response time associated with setting file locks on a mapped, network drive. Another is that, since the Access connection and recordset always reside on the client computer, a large portion (if not all) of the entire database file must be copied from the server location to the client computer for any operation.

An alternative approach here might be to use an asynchronous database connection/recordset. Declare the database objects With Events. This provides you with much finer-grain monitoring and control of ADO. Although your code will necessarily be more complex, it will allow you to begin data operations when only a portion of the data has actually arrived at the recordset. (you just need to make sure, if you haven't received the .FetchComplete event, that you aren't trying to operate on records that you haven't yet received in the recordset.) And, you can monitor/display the transferred record count as the recordset is populated. (This can eliminate the appearance (to the user) of a locked program.)

Ref http://www.xtremevbtalk.com/showthread.php?t=266305

Tks loquin.

All i have understand only this piece of explaination not is clear...for me naturally

"In any event, the RST0.CursorLocation = adUseClient overrides the connection's CursorLocation, which would be used when a cursor location is not explicitly defined (or when using the command object.)"

can you post an example to correct my code based your suggestin about cursor of RST0...

And.. you relally suggest me to use DAO instaed ADO, or not?

could also be an indexing (or lack of issue)

might help if you put your query into a suitable query browser and ran explain / show queryplan to understand where the time is being consumed

Ok...

note:

1) i have indexed all relavent filed with wehre clausole
2) changed my con with:
Set CONN3 = New ADODB.Connection
With CONN3
'.CursorLocation = adUseServer
.Mode = adModeShareExclusive
If TEST_UTENTE = "" Then
.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\REPORT_\DATABASE\" & MIO_DB & "-TEST.mdb;Persist Security Info=False"
Else
.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\9425\SAL21\REPORT_\DATABASE\" & MIO_DB & "-_TEST.mdb;Persist Security Info=False"
End If
End With

3) cahnged my open method for recordset with:

Public Function ExecuteSQL(SQL)

If Not RST0 Is Nothing Then
If RST0.State = 1 Then
RST0.Close
End If
Set RST0 = Nothing
End If

Set RST0 = New ADODB.Recordset

With RST0
.CursorLocation = adUseClient
'.CacheSize = 50
'DoEvents
'OK
'DoEvents
.Open SQL, CONN3, adOpenStatic, adLockReadOnly, adCmdText
'OK
'.Open SQL, CONN3, adOpenDynamic, adLockOptimistic, adCmdText
'.Open SQL, CONN3, adOpenForwardOnly, adLockReadOnly, adCmdText
'.Open SQL, CONN3, adOpen Static, adLockOptimistic, adCmdText
'.Open SQL, CONN3, adOpenStatic, adLockBatchOptimistic, adCmdText
'.Open SQL, CONN3, adOpenForwardOnly, adLockOptimistic, adCmdText

End With

End Function

on open recodset nwo have a dubt to use:

.Open SQL, CONN3, adOpenStatic, adLockReadOnly, adCmdText

or

.Open SQL, CONN3, adOpenForwardOnly, adLockReadOnly, adCmdText

help me please

When you create a recordset object, the recordset has has it's own properties for, among others, the cursor location. But, if you haven't explicitly set the recordset's .cursorlocation property prior to it's first use, the recordset will use the connection's .cursorlocation property by default. However, IF you explicitly set the rs.Cursorlocation property, it uses what you specify.

So since, in your code, you explicitly stateYour recordset uses a client-side cursor, no matter WHAT you specify for the connection.

No, I'm not. (I believe Sinndho was suggesting that you consider it, though.)

IMO, since there's generally such a small improvement in performance when using DAO rather than ADO, it's more important for me to use ADO, as it allows a more common interface across the 4 databases that I work with regularly (Oracle, SQL Server, PostgreSQL, and Access.) If all you will ever work with is MS Access, then YOU should consider it. But, just the fact that ADO usage is so similar across so many database platforms, and with many non-database data sources (Excel files, text files, even websites!,) it makes more sense, IMO, to concentrate on it.

ADO has the flexibility to be 'tuned' to fit your needs. But, in order to use that flexibility to your advantage, you need to understand

1. what your ADO options are
2. what the strengths/weaknesses of each option are
3. How your data usage best fits the available options

To learn about the first two items on this list, I would strongly suggest that you locate a copy of "Serious ADO: Universal Data Access with Visual Basic" by Rob MacDonald. It's readily available at Amazon or Half.Com. And, since it's been out of print for over 10 years, it's gotten pretty cheap (I just bought another copy for home,) but it's by far the best VB6/ADO programming book I've come across.

You haven't said what you are doing with the recordset after you open it, nor approximately how many records are in the table in question. How you're using the data makes a difference in the approach you should take in fetching the data.

sorry me for:
"You haven't said what you are doing with the recordset after you open it, nor approximately how many records are in the table in question. How you're using the data makes a difference in the approach you should take in fetching the data."

i have approx 1.400.xxx records and 21 fileds in table in question

where is the time going that makes the connection "slooow"

my immediate suspiscion is whether you have a proper indexing strategy
what happesn if you indexc column DT

DT is already indexed...

1.4 million records with 21 fields is going to take a long time to load into memory. Since you have a client-based cursor, all 1.4 million record pointers will be loaded into ADO before you can see anything. A static recordset loads the data, a keyset recordset loads record pointers, then fetches the data when the record is accessed by the client.

If you don't need all the fields, or all the records, try to limit the data you are extracting with a WHERE clause in your SQL, and by listing the fields you need, explicitly.

i.e.
Is the .mdb file located on another computer or on a server share? If so, that will make accessing the data incredibly slow. Access inherently has performance issues when manipulating a datafile on another computer. There's lots of overhead there. Once when inserting records into a remote file, I was getting about 3 inserts per second over the network. When I copied the target file to my local PC, insertion rates jumped to over 300 per second.

What are you actually doing with the data once it's loaded into the recordset?

One other approach would be to declare the recordset objects WithEvents. This way, you could start working with the recordset as soon as data starts loading (essentially, a separate thread is invoked to load the data into the recordset.) This approach is also known as Asynchronous ADO. additional events are available when you declare the ADO objects WithEvents.

There is more complexity required at the client application programming end; you should add code to indicate the data download status and percent complete, and you'll need to make sure your error handling code can handle you bumping into the recordset EOF without killing the app. When loading the data asynchronously, the .EOF is a 'moving target.'