Re: Updating Access data using SQL / refresh time question

From: Brad Pears (bradp_at_truenorthloghomes.com)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 16:22:26 -0500

Thanks for the suggestions CHris. I will look into this...

As a test today, for one poarticular recordset, I changed from DAO to ADO to
see if there woul dbe a difference. I used the refresh advice offered by
Victor in this same thread, and still wound up with the exact same result.

In fact, the only way to have it show the updated rows are to either give
the user a message they have to respond to ...i.e. "All updates have been
completed". This seems to give the DB enough time to make the changes

or I used a loop as follows..

       For i = 1 To 100000000
           Next i

which when completed, was enough time for the DB to be changed.

It almost seems like a client/server thing where the changes to the DB are
done in the background and the user is simply presented with the original
data until theDB is updated...

Your encapsulated idea is really new to me so I will likely have further
questions... OO stuff is new too...

"Chris Barber" <chris@blue-canoe.co.uk.NOSPAM> wrote in message
news:umfRaotBEHA.2380@TK2MSFTNGP10.phx.gbl...
> I forgot to mention that the expense of repeated connection open and close
> is mitigated by connection pooling ensuring that your performance doesn't
> degrade - existing connections are not destroyed, just released and reused
> for the next connection request that uses an *identical* connection string
> (hence the connection strings are created by the component, not the
> developer to ensure they are identical each time).
>
> Chris.
>
> "Chris Barber" <chris@blue-canoe.co.uk.NOSPAM> wrote in message
> news:uDBpZjtBEHA.2628@TK2MSFTNGP11.phx.gbl...
> Change your code to use a stand-alone library that encapsulates
connections
> and ADO request and updates.
> Your code looks surprisingly like the code that I used to have problems
> with.
>
> Ever since moving to using the ADODB class object that I gave you a link
to
> I have *never* had issues with data 'lag'. I suspect that your issues are
> mostly to do with overlapping connections.
> Particularly it should be understood that a connection lifetime
encapsulates
> the data changes and only releases them to other users when that
connection
> lifetime completes. Queries within that same connection are OK but
separate
> connections to the DB might not see the changes immediately - hence the
> importance of when and how the recordsets are queried and connections are
> used. In your case there are at least two connections (one for the DB
object
> and one for the explicitly created connection) that appear to be
> overlapping.
>
> It might help to change your code to this:
>
> sub Test
> dim db as database
> dim oconn as new adodb.connection
> dim strSQL as string
> dim strConn as string
>
> ' Set up the ADODB connection object
> strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & "c:\data\testDB.mdb" & ";"
> Set oConn = New ADODB.Connection
> oConn.Open strConn
>
> ...some code
>
> ' Massupdate some data in table1
> strSQL = "update Table1 set fld1 = 'somevalue'"
> oconn.execute strSQL
>
> 'Explicitly close this connection - should allow updates to propagate to
> other user requests.
> Set oconn = Nothing
>
> ' Open the DB and set dao recordset object
> Set DB = OpenDatabase("c:\data\TestDB.mdb")
>
> 'Now load the listview by looping through each RS row
> call LoadListView(db)
>
> Set DB = Nothing
>
> end sub
>
> Alternatively if you use the ADODB Class that I use then it would become:
>
> Sub Test()
> Dim pobjConnection As ADODB.Connection
> Dim pstrSQL As String
> Dim RSF As RSFunctionsADODB
> Dim pstrDatabaseFilePathName As String
>
> pstrDatabaseFilePathName = "c:\data\testDB.mdb"
>
> 'Create the DB access component
> Set RSF = New RSFunctionsADODB
>
> 'Set up the ADODB connection object
> Set pobjConnection = RSF.GetAccessConnection(pstrDatabaseFilePathName)
>
> ' ...some code
>
> ' Massupdate some data in table1
> pstrSQL = "UPDATE Table1 SET fld1 = 'somevalue'"
> pobjConnection.Execute pstrSQL
>
> 'Explicitly close this connection - should allow updates to propagate
to
> other user requests.
> Set pobjConnection = Nothing
>
> 'Now load the listview by looping through each RS row
> LoadListView
>
> Set RSF = Nothing
>
> End Sub
>
> Sub LoadListView()
>
> Dim RSF As RSFunctionsADODB
> Dim pstrDatabaseFilePathName As String
> Dim X As Integer
> Dim pobjRS As ADODB.Recordset
> Dim pstrSQL As String
>
> Set RSF = New RSFunctionsADODB
> pstrDatabaseFilePathName = "c:\data\testDB.mdb"
> pstrSQL = "SELECT * FROM Table1"
>
> ' Set the recordset object each time we load the listview
> Set pobjRS = RSF.GetAccessRS(pstrSQL, pstrDatabaseFilePathName)
>
> 'Populate the listview
> listview1.listitems.Clear
> X = 1
> With pobjRS
> If Not (.RecordCount = 0) Then .MoveFirst
> Do Until .EOF
> ' load listview
> listview1.listitems(X).Add , , .Fields("fld1")
> X = X + 1
> .MoveNext
> Loop
> End With
>
> 'Release resources
> Set pobjRS = Nothing
> Set RSF = Nothing
>
> End Sub
>
>
> In the code above it becomes easier to understand and debug what is going
on
> when you *know* that no connections whatsoever persist with the RSF object
> or connected to the recordsets - they are all disconnected.
> Most of all you should refrain from passing database objects into
functions
> and subs because this imposes an overhead and increases the likelihood
that
> the object might be changed or 'lost' since these objects are of course
> passed ByRef.
>
> Hope this helps.
>
> Chris.
>
>
> "Brad Pears" <bradp@truenorthloghomes.com> wrote in message
> news:OUZPmJtBEHA.628@TK2MSFTNGP10.phx.gbl...
> OK... this may be a little hard to explain but here goes....
>
> When this project was started, I was simply using DAO recordsets etc...
>
> But as time progressed and I started learning different things, the
project
> evolved. In time, for mass update purposes, I decided to start using SQl
> statements to update, delete etc... from tables within the db as I know
SQL
> and it's easier to mass update.
>
> However, in order to execute a standalone SQL statement (as far as I am
> aware anyway), you have to create a connection and use that connection
> object as follows...
> ----------------------------------------------
> dim oconn as ADODB.connection
> dim strSQL as string
>
> ... a bunch of code...
>
> 'Now update a table
> strSQL = "update table1 set fld1 = 'somevalue'"
>
> ' Now execute the SQL statement
> oconn.execute strSQL
> ----------------------------------------------
> So, what is now happening is that I am using an ADODB connection object to
> update data in the DB tables via SQL statements. In some cases,
immediately
> after updating a table in the above fashion, I am loading a listview in
> which I am reading in records from a DAO recordset - updated rows from the
> table I just updated using the oconn connection. This is where the data
lag
> seems to be.
>
> example...
> sub Test
> dim db as database
> dim oconn as new adodb.connection
> dim strSQL as string
> dim strConn as string
>
> ' Open the DB and set dao recordset object
> Set DB = OpenDatabase("c:\data\TestDB.mdb")
>
> ' Set up the ADODB connection object
> strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & "c:\data\testDB.mdb" & ";"
> Set oConn = New ADODB.Connection
> oConn.Open strConn
>
> ...some code
>
> ' Massupdate some data in table1
> strSQL = "update Table1 set fld1 = 'somevalue'"
> oconn.execute strSQL
>
> 'Now load the listview by looping through each RS row
> call LoadListView(db)
>
> end sub
>
>
> sub LoadListView(db as database)
> dim X as integer
> dim RS as dao.recordset
> ' Set the recordset object each time we load the listview
> set RS = db.openrecordset("select * from Table1")
> listview1.listitems.clear
> X = 1
> rs.movefirst
> do until rs.eof
> ' load listview
> listview1.listitems(X).add,, rs.fields("fld1")
> X = X+1
> rs.movenext
> loop
> end sub
>
> After this code is run, the listview does not show the updated rows as I
> would have assumed it should - especially since IO am resetting the
> recordset just before the load process! However, if I wait a second, exit
> the listview and come back in, the listview then shows the updated
> values....
>
>
> This code isn't exact but you get the idea of what I am trying to do to
> ensuire I am getting the most current data...
>
> Any suggestions?
>
> Thanks,
>
> Brad
>
>
> "Chris Barber" <chris@blue-canoe.co.uk.NOSPAM> wrote in message
> news:%23aQX9EgBEHA.1700@TK2MSFTNGP12.phx.gbl...
> > It can't be caching [the listview] - it only shows what you give it.
> > The data retrieved form the database must be 'out of date' at this
point.
> >
> > Are you maintaining a single connection to the DB and holding it open or
> are
> > you creating and destroying the connection for every call to the
database
> > (recommended when using OLEDB but not for ODBC)?
> >
> > I used to get this until I moved to accessing the DB using disconnected
> > recordsets. I have a class that may be of use that encapsulates this
> > functionality (obtaining recordsets and marshalling change back to the
> DB -
> > it also returned connection objects if required to run execute commands
> > etc.):
> > http://ftp.belper.blue-canoe.net/rsaccess/rsaccess.zip
> >
> > Can you show the code that you are using to make the change and
> re-populate
> > the listview paying particular attention to where the connection objects
> are
> > coming from?
> >
> > Chris.
> >
> > "Brad Pears" <bradp@truenorthloghomes.com> wrote in message
> > news:OZOLidfBEHA.212@TK2MSFTNGP12.phx.gbl...
> > I have something happening in my VB 6.0 app that is driving me crazy....
> (DB
> > is Access 2000 )
> >
> > I have a listview on the screen containing records from a master table.
> The
> > user is able to double click a listview item, which then gives them a
> screen
> > where they can modify data in the table.
> >
> > To update data, I use an SQL clause and simply execute it i.e.
> >
> > strSQl = "update TABLE set field = " & fldValue
> > oconn.execute strSQL
> >
> > When the update is complete, I refresh the items in the listview. i.e.
> > clear all listview items and reload them from the same table I just
> > updated... ( I know I can simply redisplay the actual listview item I
was
> on
> > but this isn't what I am doing right now)
> >
> > However, sometimes, upon return to the listview from teh update screen,
> the
> > newly updated data does not show up - instead, the old data is still
> there.
> > Then if I exit the screen and come back in, voila, there are the
changes!
> > It appears the the changes are not being made to the table immediately
> when
> > updating. I have this problem with just about every listview I have
where
> > the user can update data.
> >
> > It does not happen all the time, just often enough for me to get calls
> from
> > the users occasionally asking "How come my changes aren't made?" Then I
> tell
> > them they have been saved, just exit that particular screen, come back
in
> > and you'll see them...
> >
> > I have tried using a wait loop i.e. for i = 1 to 100000, next i etc.. to
> let
> > the db updates complete first before redisplaying the listview but that
> > doesn't seem to work either.
> >
> > Maybe the problem is more with the listview caching data ebehind the
> > scenes??? I do not know how this control works internally...
> >
> > Any help would be much appreciated...
> >
> > Brad
> >
> >
> >
>
>
>
>

Relevant Pages

  • Re: Updating Access data using SQL / refresh time question
    ... you have to create a connection and use that connection ... dim oconn as ADODB.connection ... ' Open the DB and set dao recordset object ... 'Now load the listview by looping through each RS row ...
    (microsoft.public.vb.database)
  • Re: Updating Access data using SQL / refresh time question
    ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ... > ' Set the recordset object each time we load the listview ...
    (microsoft.public.vb.database)
  • Re: Updating Access data using SQL / refresh time question
    ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ... > ' Set the recordset object each time we load the listview ...
    (microsoft.public.vb.database)
  • Re: Updating Access data using SQL / refresh time question
    ... the screen updates for every addition to the listview. ... > really want a server-side cursor), connection and RS retrieval timeouts, ... >> sub Test ... >> dim oconn as new adodb.connection ...
    (microsoft.public.vb.database)
  • Re: Updating Access data using SQL / refresh time question
    ... listview is causing a delay of 5-10 seconds and sometimes longer. ... > really want a server-side cursor), connection and RS retrieval timeouts, ... >> sub Test ... >> dim oconn as new adodb.connection ...
    (microsoft.public.vb.database)
Loading