REFRESH, REQUERY OR UPDATE A RECORDSET 1

[Hiccup]

I'm using an Adodc connected to an Access 2K Query. The Query is used to Sort the records on a particular field of a Table; e.g., "LastName" of the "Personnel" Table.

When I exit out of my VB 6 Form that contains the Adodc and the DataGrid, I would like any newly added records to be sorted properly when I access the Form again (without exiting the VB app.) If I exit the VB app and reaccess the Form, the records are sorted properly. I don't want the user to have to exit the VB app in order to see the records sorted properly.

Would I use "Refresh" "Requery" or "Update to accomplish this, or am I completely off base.

Thanks in advance!

 

[Golom]

Either "Refresh" or "Requery" should do it. "Update" changes data in the database which I don't think you are asking to do.

 

[Hiccup]

Thanks for the feedback, Golom!

What I'm trying to accomplish is to make any newly added records sort by last name (within VB)when exiting the screen and then re-opening it without closing out of VB (which will cause it to properly sort the new records.)

Here are the ways I've tried that aren't working for me:

1. Adodc.Recordset.Sort = "Personnel.LastName ASC"

2. Adodc.Refresh

3. Adodc.Requery

4. Adodc.Recordset.Refresh

5. Adodc.Recordset.Requery

6. DataGrid.Refresh

etc.

None of these methods make the records resort when closing the form and then immediately reopening it. I'm missing some code somewhere!

I'm double-clicking the DataGrid to add the code. Should I be adding the code to the Adodc or the Form or what?

 

[CCLINT]

>Adodc.Recordset.Sort
For this, use a ClientSide cursor

 

[Hiccup]

CCLINT,

Can you tell me how to set up the "ClientSide" Cursor for the "Adodc.Recordset.Sort" statement and DataGrid?

I've read up on using Cursors and there seems a myriad of approachs depending on the end results I need. As I mentioned, I would like the Recordset in the DataGrid to sort by the LastName Field when exiting the Form without having to exit the VB app, while maintaining referential data integrity between the Access 2k database "Query-Sorted" Table that the DataGrid and Adodc are based on.

Thanks!

 

[Glasgow]

When you open the recordset, use adUseClient - e.g.

rsMine.Open "SELECT * FROM MyTable", DbConnect, adUseClient, adLockReadOnly

 

[CCLINT]

er..

 

[Hiccup]

Glasgow,

Will that create the "ClientSide" Cursor CCLINT suggested?

Also, does the "adLockReadOnly" at the end of the code make it read only? I want the users to be able to make changes, as needed. Do I just leave the adLockReadOnly off or is there other code I need to add in its place to allow other users to make changes?

Thanks!

 

[Glasgow]

Yes the adUseClient specifies a client side cursor but I'm not sure how this ties in with your data control nor whether Cclint's somewhat brief post suggest that I have missed something!

The adLockReadOnly was just pasted from my own code - you will need to use something like adLockOptimistic.

Have a look at the ADO Open Method in MSDN help.

 

[CCLINT]

Sorry, I wanted you to find the typo that slipped in there
You cannot declare the CursorLocation with-in the recordset OPEN statement.

You need to do this seperately:

rsMine.CursorLocation = adUseClient

The constant adUseClient has a value of three, which is the value for the CursorType of Static, or adOpenStatic, and which is the argument (CursorType) for this position.

So, if the connection uses a ServerSide cursor, then what you get by this would be a static recordset with a server side cursor.

If you were using a Client Side cursor for the connection object, then you would have never noticed the error and everything would work as expected. This is because you would still end up with what you wanted (clientside, static cursor). But, if the connection used a server side cursor (default), then you would have something completetly different.

With the datacontrol there is a property for the Cursor Location, the CursorType and the LockType.

So, what you would use is:
CursorLocation = adUseClient (default)
CursorType = adOpenStatic

And, if you want a read lock then use:
LockType = adLockReadOnly
In the Grid set the AllowUpdate, etc, properties to False.

 

[Glasgow]

I knew that - honest guv! Actually you've pinpointed a possible problem in the code I pasted from - thank you (star for that). I am indeed using client side cursor for the connection so not such a huge deal and fortunately I do it the right way elsewhere but I will have to review all my .Opens just in case.

Sorry Hiccup.

Glad I queried the post!

 

[CCLINT]

You want to hear about some of the "undocumented features" I've posted in the past, which, for some reason, also do not always work right?
Other's call them typos, oversights, or accidents while tired typing

 

[Hiccup]

Thanks everyone for your suggestions, but a friend suggested that I use "Unload frmPERSONNEL" rather than "Me.Hide" which I did. And it worked like a charm. The new record I added was properly sorted when I returned to the form (without exiting my VB app) and it was also added to the connected Table in the Access 2K mbd file.

A no brainer, one liner!