Code in form 1
rsTest1.Open "tblTest1", con, adOpenDynamic, adLockOptimistic

Code in form 2
rsTest1.Open "tblTest1", con, adOpenDynamic, adLockOptimistic

and form1 and form2 is loading together but do not show form2

form1.show
form2.hide

when i update database recordset (in form1) it doesn't effect database recordset 2. but i want it effect and i want it to be fast database application. so i don want to reopen rsTest1 or rsTest1.Requery in form2.

Note: adOpenDynamic is cursor that can let user to see anything when other user update,change,delete or add to database.


[Edited by luckyboy on 12-10-2005 at 08:52 AM GMT]

____________________________
Please help out.

Try to read this article about cursor and i hope it helps.Types of Cursors

[Edited by TJ_01 on 12-10-2005 at 03:37 AM GMT]

____________________________
Im JAMES  

Hi,
How your cursor responds will differ according to the provider to which you have connected, and whether or not you are using a server-sie cursor library or a client-side cursor library.
To check if you really have opened a Dynamic cursor set a boolean variable and get the result of :-

Dim blIsDynamic As Boolean blIsDynamic = rstTest1.Supports(adMovePrevious) MsgBox blIsDynamic

If this returns True then you should be seeing the changes, but if it returns False then your Cursor Library does not support the Dynamic cursor. In this case try specifically setting the cursor location to -
rstTest1.CursorLocation = adUseClient
Do this BEFORE invoking the "Open" command.

As an alternative, why not set the rstTest1 Recordset Object as a Public Variable and open it in your "Sub Main" - then it doesn't matter which Form is referencing the Recordset - they all get the same one!




____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Thank u for advice. it help me alot with this . but i just use this for a while and if i declare public it will waste memory.  

____________________________
Please help out.

Hi,
The Public Variable does not need to consume memory whilst you are not using it. Provided you do not "Set" the variable until you actually want it, and then "Set rstTest1 = Nothing" as soon as you have finished with it, that will free up the allocated memory.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

hi,
but AdOpenDynamic it's not support RecordCount. help me i need recordcount. and i need AdOpenDynamic. do you have way?

____________________________
Please help out.

Hello.
Wherever, put and run a piece of code like this:

' this variable has to be declared in the scope you need to view it ' so put this declaration in the appropriate location Public rsCount As Long ' you can run this code whenever you need to store the recordcount into the public variable Public Sub GetRecordCount() ' assumes that you already have an open connection, named cn     Dim rs As ADODB.Recordset     Dim sql As String     ' open a temporary recordset to get the record count     Set rs = New ADODB.Recordset     sql = "SELECT COUNT(*) FROM MyTable;"     rs.CursorLocation = adUseServer     ' open the recordset. It returns a single record,     ' with a single field containing the number     rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText     ' assign the recordcount to the public variable     rsCount = rs.Fields(0).Value     ' close and destroy the temporary recordset     rs.Close     Set rs = Nothing End Sub

Hope you get the  concept.

Anyway, we have spoken already about this topic, so you can make a search on the forum. And at the moment JLRodgers is speaking about it in this other thread.
Hope it's helpful

[Edited by yronium on 18-10-2005 at 12:27 PM GMT]

____________________________
Real Programmer can count up to 1024 on his fingers

Hi,
AdOpenDynamic DOES support RecordCount - at least it does with MSJet and SQL Server databases. Are you connecting to another type of provider, and if so have you tried declaring the cursor location as "adUseClient" as that should work.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hello GeoffS.

It depends also on location of cursors. If you execute your queries on the server the record count is not always avaliable. My code shows how to get the recordcount even in that case, and in order to get only the recordcount we can even open a ForwardOnly cursor and then close it suddenly.

And more, with this approach you store the recordcount in a public variable, and then you can work with a disconnected recordset, as you got the recordcount value avaliable for all the session. Remember that each time you read the rs.RecordCount property you raise the WillMove and MoveComplete events and you gotta take an eye on it if you have some instructons there, as otherwise you will get unexpected results and/or a lack of performances.

About the adOpenDynamic option, we already discussed cursors in this topic of a couple of months ago.

Following I paste a chapter from F. Balena's "Programming Visual Basic 6", that's the VB bible of many developers worldwide (I got myself two copies!!)

Working with cursors A cursor is a set of records that represent the results of a query. Cursors can contain the actual data or just pointers to records in the database, but the mechanism that retrieves the data is transparent to the programmer. You can specify where the cursor should be created (on the client or on the server workstation), the cursor type, and the locking option. The CursorLocation property specifies where the cursor is to be created. This property can have one of two values: 2-adUseServer or 3-adUseClient. The value of this property is inherited from the Connection object and can be changed only for closed Recordsets. When you're working with the OLE DB Provider for ODBC Drivers and for SQL Server, the default cursor is a forward-only cursor created on the server. (This type of cursor is also the most efficient.) You need to switch to client-side cursors if you want to create dissociated Recordsets and use an optimistic batch update strategy. Client-side cursors are often a good choice when you have a DataGrid control or another complex control that is bound to the Recordset. In all other cases, server-side cursors are often preferable because they offer better performance and additional cursor types. The CursorType property tells which type of cursor should be created and is one of the following constants: 0-adOpenForwardOnly, 1-adOpenKeyset, 2-adOpenDynamic, or 3-adOpenStatic. Server-side cursors support all these options, whereas client-side cursors support only 3-adOpenStatic. But if you use a different setting for a client-side cursor, a static cursor is automatically created without raising an error. A forward-only cursor is the default for server-side cursors and is available only for server-side cursors. As I just mentioned, this type of cursor is the most efficient, especially if you set LockType = adReadOnly and CacheSize = 1. Many programmers and authors refer to this last type of cursor as a "noncursor." In The Hitchhiker's Guide to Visual Basic and SQL Server, William R. Vaughn defines this as a "fire-hose" cursor, to emphasize how fast it is at tossing data to the client application. You don't have to do anything special to use this cursor (or noncursor) because it's the default for ADO. You can navigate a forward-only Recordset only by using the MoveNext method. If you want to get the best performance for an application that needs to update records, you should do all your updates through SQL commands or stored procedures. Dynamic cursors consist of a set of bookmarks to the actual data in the data source. Any time the client requests a record, ADO uses the bookmark to read the current value, which means that the application always reads the latest value stored by other users. Dynamic cursors are automatically updated when other users add or delete a record or change any record already in the Recordset. Not surprisingly, these cursors are the most expensive cursors in terms of performance and LAN traffic because any time you move to another record a trip to the server is required to retrieve the current values. You can always update data and perform all kinds of navigational methods on dynamic Recordsets, including using bookmarks if the provider supports them. This type of cursor is available only as server-side cursors. NOTE -------------------------------------------------------------------------------- Microsoft Jet Engine doesn't support dynamic cursors, so if you attempt to open dynamic cursors with the Jet OLE DB Provider you always get a keyset. Keyset cursors are similar to dynamic cursors, but they don't include records added by other users. (Changes other users have made to records in the Recordset are visible, though.) You can read and modify all the records in the cursor, but you get an error if you access a record that another user has deleted. The keyset cursor is available only as a server-side cursor. Static cursors create a fully scrollable snapshot of all the records identified by the Source property, and they are the only type possible for client-side cursors. Because a static cursor is actually a copy of the data coming from the database, changes that other users make aren't visible. Whereas these cursors are less efficient than forward-only cursors and increase the workload on the computer where they reside, their performance is reasonable and they're a good choice, especially when the Recordset doesn't include too many records. A static cursor is usually the best choice for retrieving data from a stored procedure. Depending on the provider and on other settings, this Recordset can even be updatable. You should create client-side static cursors only when the client workstation has enough memory. The MaxRecords property sets a limit to the number of records that will be returned in the Recordset when you're working with a cursor. The default value is 0, which means that all records will be returned. This property can be written to when the Recordset is closed and is read-only when the Recordset is open. The CacheSize property sets and returns the number of records that ADO will cache locally when working with cursors. You can adjust the value of this property to fine-tune your application, trading memory for performance. You can assign a new value to this property at any moment, but if the Recordset is already open, the new setting will be used only when ADO needs to fill the local cache—that is, when you move the pointer to the current record to point to a record that isn't in the cache. NOTE -------------------------------------------------------------------------------- Most programmers like cursors—especially dynamic and keyset cursors—because they're so powerful and versatile. Unfortunately, cursors are often the worst choice in terms of performance, resources, and scalability. You should use cursors only when you're working with small Recordsets or when you're using bound controls. (Bound controls need cursors to support backward and forward navigation.) When you employ cursors, remember to build the Source property so that you reduce the number of rows fetched and to use a WHERE clause that exploits one or more indexes. Another effective technique to improve the performance of your application is to execute a MoveLast method to quickly populate the Recordset and release any lock on the data source as soon as possible.

You can see he says that MSJet don't really create dynamic cursors, but only keyset: we should keep this in mind. He says also that cursors really exist on server-side recordsets. I can't make any comment on it, but for sure server-side recordsets generate less network traffic.

Hope it helps

[Edited by yronium on 18-10-2005 at 12:28 PM GMT]

____________________________
Real Programmer can count up to 1024 on his fingers

thank u for ur help.

____________________________
Please help out.

Hi yronium,
I didn't mean to imply that your method of obtaining the RecordCount was not the best way - merely pointing out that the Keyset Cursor that Jet provides in lieu of the Dynamic one requested will provide the RecordCount functionality when used with the Client-Side cursor. In terms of best programming practice I agree that your method is best - I use it myself infact, as to get a record count with a client side cursor you need to pull the full recordset across a network, whereas a single figure returned from a query in a server-side cursor is bound to be faster.

However, I'm intrigued as to why you have TWO copies of Balena's "Programming Visual Basic 6",  is it because, in true programming style, one is your Master and the other is a "Backup"    



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

quote:

---

However, I'm intrigued as to why you have TWO copies of Balena's "Programming Visual Basic 6",  is it because, in true programming style, one is your Master and the other is a "Backup"    

---

ehehehe, nope, it was simply an economic edition and a full edition. Economic has no cd-rom (from which I toke the english text I pasted above) and some chapters are trimmed (the price was 1/8 cheaper, anyway...)

However, I have a double copy of many relevant books I read, as I'm always ready to give one to a friend who I suggest to read it. Years ago, mr. Giorgio Armani made a tv commercial in which he said "Never lend a book: donate it". I consider it a good principle.



____________________________
Real Programmer can count up to 1024 on his fingers