• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

adOpenDymanic / adLockPessimistic Recordset Fails

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
DangerM0use
Asked:
DangerM0use
  • 3
  • 2
1 Solution
 
DangerM0useAuthor Commented:
Sorry I forgot to mention versions:

ODBC: 3.51
MySQL: 4.0.14-nt

Regards
0
 
SqueebeeCommented:
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
 
DangerM0useAuthor Commented:
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
 
SqueebeeCommented:
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
 
DangerM0useAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now