DataGrid, Jet, Serverside cursor

[CCLINT]

Normally, when using a DataGrid, or any other complex data bound object with an ADO recordset created using the JET OLEDB Data provider, you needed to use a Client Side Cursor in order for data to be displayed in the grid.

This is because the JET OLEDB provider does not support the DBPROP_LITERALIDENTITY property.

However, there is a method to get it to use a Server side cursor. Jet needs to implement IRowsetIdentity.

You need to set your connection, cursorlocation and type along with setting the JET IRowsetIdentity property prior to opening the recordset.
Please note that the IRowsetIdentity property needs to be set after the connection is set, otherwise there will not be a connection to the JET provider through ADO in order to set retrieve/set any properties:

With rsADO
.CursorLocation = adUseServer
Set .ActiveConnection = conn
.CursorType = adOpenKeyset
.Properties("IRowsetIdentity&quot = True
.Source = "SELECT * FROM myTable"
.Open
End With

Set DataGrid1.DataSource = rsADO


I just thought that this may make some people happy.... [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[thornmastr]

Happy enough to strongly suggest you move it to the FAQs section so that when this comes up again next month, someone can point the questioner to the FAQ rather than to a temporary page which may already have been reclaimed. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[CCLINT]

Thanks. I will think about doing that.

This makes such a large difference in the amount of time it takes for the data to be loaded into a DataGrid with JET especially when the MDB is local. (50,000 records 1-3 seconds! depending on the system, as opposed to 1 to x minutes using a client side cursor - connection time not considered).
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}