Solved

adOpenDymanic / adLockPessimistic Recordset Fails

Posted on 2004-04-22
5
371 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
Comment Utility
Sorry I forgot to mention versions:

ODBC: 3.51
MySQL: 4.0.14-nt

Regards
0
 
LVL 17

Expert Comment

by:Squeebee
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

11 Experts available now in Live!

Get 1:1 Help Now