Solved

adOpenDymanic / adLockPessimistic Recordset Fails

Posted on 2004-04-22
5
372 Views
Last Modified: 2012-06-21
I have a multi user application in development and therefore have need to use dynamic recordsets within certain
areas of the system, however despite my best efforts I can't seem to get them to function correctly with MySQL.

I have flagged 'Enable Dynamic Cursor' within the ODBC Driver settings, but still nothing. The same code works
great when used with a MS Access backend !

I am of course aware that MySQL Server does NOT support Server side cursors, but my understanding is that the
ODBC driver does all of the work for you, client side (granted at a substantial sacrifice in speed).

Anyway here is some code:

____________________________________________________________________________________________

'+--------------------+
'|  Connection info. |
'+--------------------+

Global Const s_Conn = "Driver={MySQL ODBC 3.51 Driver};" & _
                      "Server=Chiron;" & _
                      "Port=3306;" & _
                      "Option=32;" & _
                      "Stmt=;" & _
                      "Database=live;" & _
                      "Uid=root;" & _
                      "Pwd="


'+---------------------+
'| Recordset snippet |
'+---------------------+

With o_Recordset

        .Open s_SELECT, o_Conn, adOpenDynamic, adLockOptimistic

End With

____________________________________________________________________________________________


If data is altered @ a different location in the company I am required to perform a 'Requery' in order for it to be
visible within my recordset, could it some how be converted to a static 'CursorType' with out my knowing ?

I'm begining to loose the plot so if anyone has any ideas I would appriciate some feedback. Any additional code or
general/specific information required please let me know!

This is a fairly important question so I hope the points offered with spark some interest !!

Cheers

Chris
0
Comment
Question by:DangerM0use
  • 3
  • 2
5 Comments
 
LVL 1

Author Comment

by:DangerM0use
ID: 10888162
Sorry I forgot to mention versions:

ODBC: 3.51
MySQL: 4.0.14-nt

Regards
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 10888573
Rather than explain it all here I will point you at an article I wrote specifically on the subject of VB cursors and locks in relation to MySQL:

http://www.vbmysql.com/articles/cursorsandlocks.html

If you have any questions after reading it please let me know, but I believe that it will answer your questions.
0
 
LVL 1

Author Comment

by:DangerM0use
ID: 10889352
Thanks for the swift feedback, I have read your article in the past, along with several other, and have found them to be
extremely informative and helpful (Thanks from the VB brotherhood!).

I am however struggling to grasp 'Dynamic' cursor types, using your analogy of selling tickets, I would assume that when
a client updates a seating record to have been sold, all other connected client machines would update themselves, to
reflect that sale.

The system I am trying to apply this principle to involves lab samples which are awaiting analysis, lab staff all have their
own systems and select samples to analyze as they appear on their work list (which should deminish, as other lab staff
complete their analysis).

I expect that as lab staff update the records of a particular sample all other machines remove it from the list. It is this
function that I am failing to produce.

I have followed (to the best of my knowledge) your article, both the connection & recordset objects have the adUseServer
cursor location specified , I have enabled the 'Enable Dynamic Cursor' & passed option 32 in my connection string. finally
the table I am querying has a primary key, but still I need to requery my recordset before I see any changes.

Is there some fundamental aspect that I am missing ?

Cheers
0
 
LVL 17

Accepted Solution

by:
Squeebee earned 500 total points
ID: 10901378
This is not an automated process under mySQL. To do what you want use client-side cursors and a static recordset. Issue periodic refreshes of your recordset with atimer control. Dynamic cursor simply means that when you move forwards and backwards in the recordset it requeries, it does not cause changes to be pushed to the client.
0
 
LVL 1

Author Comment

by:DangerM0use
ID: 10923323
Ah... that would explain matters, when I had read that the dynamic recordset constantly requeries I assumed (wrong!) that the WillChange event would be fired, should
the data have changed outside of the application.

Well your question does answer my question, though it is not the answer I had hoped for that is not your fault, and I appriciate you putting me right. I guess I will end up
using some sort of timer to refresh the recordset.

Thanks again for your articles.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now