[RESOLVED] Problems updating a record

[jalexm]

Hello.
I'm using Access and VB6 in my app.
In FormA there is an ADODC and some controls showing record fields.
In FormB user changes another record, saves the record and a class event is fired after that.
FormA receives that event and tries to update its screen immediately in order to show the changes made in FormB. I'm using ADODC.Refresh in event receiver to do that.
The problem is FormA can't show the changes. Curiously, if user makes another change then the first one appears in FormA.
I'm not sure, but it seems the class event is firing before the changes are really saved to database.
Please, any ideas? Thanks.

[DataMiser]

You'll need to show some code.

[jalexm]

You'll need to show some code.

The code is simple.

'In MyClass:
Public Event RecUpdated()

Public Sub UpdateRec()
   SQLCmd = "UPDATE ...;"
   With AdoCmd
      .ActiveConnection = ...
      .CommandType = adCmdText
      .CommandText = SQLCmd
      .Execute
   End With
   RaiseEvent RecUpdated
End Sub
-------------------------------------
'Somewhere in FormB:
   MyClass.UpdateRec
-------------------------------------
'In FormA:
Private WithEvents evtMyClass As MyClass

Private Sub evtMyClass_RecUpdated()
   ADODC.Refresh
End Sub

[DataMiser]

Well personally I never use the ADODC so I am not sure what kind of quirks it may have. I know it was buggy when VB6 was first released and I haven't used it since then.

If I am not mistaken though a refresh does not requery the database so newly added records may not show up. If you requery then they should be accessiable.

[jcis]

Don't open new threads for the same topic. Give feedback when somebody tries to help you, did you try using Adodc1.Requery?

Personally, I don't recommend using Adodc control, you're correctly using Commands for your insert/update operations, that's a very good practice, adodc is the opposite.

[Hack]

Duplicate thread deleted.....one topic, one thread, one forum section please.

[jalexm]

Give feedback when somebody tries to help you, did you try using Adodc1.Requery?

If you meant Adodc1.Recordset.Requery, yes, I tried it but it didn't work.

Personally, I don't recommend using Adodc control, you're correctly using Commands for your insert/update operations, that's a very good practice, adodc is the opposite.

I use Adodc only to try refresh data. There is a grid and some textboxes linked to database. I could refresh data manually in textboxes, but I don't know how to do it with the grid.

Also, I have created a Pause function. Calling it right before Adodc.Refresh solved the problem, but I think it's not a good solution, since the time delay could change with number of records, computer speed, etc.

The best thing it would be my app receiving a kind of event when database REALLY saved/updated the data. Is there anyway to do that?

[gibra]

Simply, refresh cache using MSJRO

After INSERT or UPDATE need to refresh the cache:
http://www.xtremevbtalk.com/showthread.php?t=102739#5

[jcis]

Why don't you just get rid of that grid and all bound controls and manage all data just by sending queries to the db and looping to get data from the Recordset returned? You could use a Flexgrid or a Listview to replace that grid.

[jalexm]

Simply, refresh cache using MSJRO

After INSERT or UPDATE need to refresh the cache:
http://www.xtremevbtalk.com/showthread.php?t=102739#5

Interesting. I tried it but it didn't work.

[jalexm]

Why don't you just get rid of that grid and all bound controls and manage all data just by sending queries to the db and looping to get data from the Recordset returned? You could use a Flexgrid or a Listview to replace that grid.

I'm using TrueDBGrid and really don't know how to populate it with data.

[DataMiser]

Unbind the datasource, requery the datasource then rebind

[jalexm]

Unbind the datasource, requery the datasource then rebind

If you meant:

Set DBGrid.DataSource = Nothing
ADODC.Refresh
Set DBGrid.DataSource = ADODC

It didn't work too
The only thing I have tested OK is this:

Pause 550 '550ms or more.
ADODC.Refresh

But I know this is not a good solution.

[gibra]

Interesting. I tried it but it didn't work.

Impossible!

[jalexm]

Impossible!

Have you tried it? I did.
It didn't work. Event fires BEFORE everything.
It seems Access need some miliseconds to get ready no matter what you do.
If I'm doing something wrong, please tell me.

[DataMiser]

Are formA and FormB both using the same ADODC?

[gibra]

Have you tried it? I did.
It didn't work. Event fires BEFORE everything.
It seems Access need some miliseconds to get ready no matter what you do.
If I'm doing something wrong, please tell me.

- I use it in every program which handle database Access.
- I DON'T use ADODC (blah....) I use only ADODB objects.
- I use TrueDBGrid 8 also

To connect your database directly to TrueDBgrid is simple, (as any other data grid), i.e.:

'Assume that CN is your connection already open (a ADODB.Connection object)
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM TABLE", CN,
Set TrueDBGrid1.DataSource = rs

Also, You can download a complete VB6 project-template (working) from my web site here (italian language only):
http://nuke.vbcorner.net/Progetti/VB...T/Default.aspx

Login Test Project 3 (ProgettoProvaLogin 3) is the third version of a small sample application that demonstrates how to manage a database MDB (Access 2002-2003) in a multi-user (LAN) using ADO 2.5.
The purpose of this project is to provide support to less experienced developers in designing database applications. (*)
In practice it is a kind of project templates that can be used as a starting point for developing their own applications.
The project can be downloaded and modified freely without any restrictions. The author disclaims any and all liability

Optimistic Concurrency
To test the optimistic concurrency (multi-user) to perform the following steps:

Compile the project
Start 2 sessions of the compiled program, taking care to connect:
- In the first session with user1 and password1
- In the second session with user2 and password2
From Customers lists (Anagrafica Clienti) open the same record (customer) in both sessions
Edit and save the data in the last session opened with user2 (the second)
Now edit and save data in the first session opened with user1.
A message will indicate that the modification is not allowed because the data have already been modified from User2.
To modify the data you need to reload the original data from the database (use Refresh button).



New in Version 3.0
In addition to the many features implemented in previous versions (1 and 2) version 3 adds important new features:

- Options (frmOptions.frm)
new form for the program configuration.
- ClsImageControls
class to load the 32bit PNG and ICO, ability to set the margin (see SetMargin)
- modGDIPlusResize
to load images with 32bit PNG and ICO, can resize the image size (width and height)
- clsPDFCreator
class for generating PDF documents (tiny example)
- modHighlight
to highlight the active control with a colored border, now configurable (see Options)
- modOS
to obtain information about the operating system.
- various images
in the \images folder there are images that will loaded at run-time
N.B. Some images are visible only in the compiled program (EXE).
- frmMain
The new routine EsportaPDF () allows to create a customers list in PDF file format (without external OCX/DLL ).
frmLogin
- Appearance nicer
- Displays information about the operating system (OS Info button)
- Show the window in the foreground
- Show button on the taskbar
- Ability to see the password in clear
frmCliente (Customers)
in the routine GetDataFromClass () has been added to the call to AggiornaGrigliaAnagrafica (), so as to recharge the data even in the grid (if open)
- CIniManag
In routine FinestraLeggiPosizione () restores the window to the center, in case of negative values ​​for Left and Top.
- modDatabase
In routine ExportExcel () was added the optional parameter 'sNomeFoglio' to allow you to specify the sheet name to be created.

enjoy.

[jalexm]

Are formA and FormB both using the same ADODC?

No. ADODC is used only in FormA.

[jalexm]

- I use it in every program which handle database Access.
- I DON'T use ADODC (blah....) I use only ADODB objects.
- I use TrueDBGrid 8 also

Thank you, I will read your project carefully and learn more about those things.
As to ADODC, I'm not sure but I think the problem is not related to it.
When ADODC.Refresh is executed Access DB is not ready. That's the problem.

[DataMiser]

If your adodc is on form a then it has a dataset that it got from the database.
You say you are updating the database from FromB. If you are not using the datcontrol then how are you updating the data?

If you are updating using a different object then your datacontrol must obtain that data from the database and there may need to be a slight delay between the time you issue the update statement and the time you try to do a refresh.

I wrote a project a while back that received data from a remote source, wrote it to a database and displayed the data on screen in a flex grid. Each transaction would trigger a refresh after the update statement. When using Access as a backend the grid was always showing 1 record behind. In orther words the refresh was happening before the record could be retrieved from the db. The next record would trigger a refresh that would display the previous record and this happened consistantly. When using SQL server as a backend this did not happen and the records appeared on screen as soon as they were received.

So you may just be dealing with a case where you need to do a slight delay.

[jalexm]

If your adodc is on form a then it has a dataset that it got from the database.
You say you are updating the database from FromB. If you are not using the datcontrol then how are you updating the data?

If you are updating using a different object then your datacontrol must obtain that data from the database and there may need to be a slight delay between the time you issue the update statement and the time you try to do a refresh.

I wrote a project a while back that received data from a remote source, wrote it to a database and displayed the data on screen in a flex grid. Each transaction would trigger a refresh after the update statement. When using Access as a backend the grid was always showing 1 record behind. In orther words the refresh was happening before the record could be retrieved from the db. The next record would trigger a refresh that would display the previous record and this happened consistantly. When using SQL server as a backend this did not happen and the records appeared on screen as soon as they were received.

So you may just be dealing with a case where you need to do a slight delay.

In fact, I solved the problem doing what you, Gibra and other friends said.
Firstly, I tried to unbind/refresh/rebind ADODC but it didn't work.
Later I removed ADODC, unbound all the controls, and did the things manually. This worked fine. No more pauses and delays.
For some reason, ADODC.Refresh can't get new data fastly enough and when event fires it gets old data.
Now it's working fine. No ADODC anymore.
Thank you all.