ADODC out of sync? 2

[jgoodman00]

I seem to have run into a very annoying problem using the adodc control. If I populate one of the underlying tables for a query dynamically, at runtime, & just before the form containing the adodc is loaded, the adodc will load blank. If the form is loaded again, or I step-through the code (slowing down execution), it loads fine. It would appear the adodc control is not reflecting the changes recently made to the db, even though the table updates are finished before as the form is loaded.

I am running some INSERT code in the form load event. I have tried refreshing the recordset, requerying the recordset, & also opening & closing the adodc recordset. None of these make any difference...



Any suggestions?

James Goodman MCP

http://www.james-goodman.co.uk

 

[chadt]

Form_Initialize

That may be the key to your success.

 

[jgoodman00]

The event chosen makes no difference unfortunately. It seems the time between the INSERT statement, & the form opening (& thus the recordset opening) is insufficient for the recordset to recognise the updates to the underlying table...

James Goodman MCP

http://www.james-goodman.co.uk

 

[CCLINT]

I am assuming you are using an MDB? Then this is because of the lasy write cache.

If you are using ADO and JET then use JRO to refresh the cache.

 

[MangroBongacello]

I had the same problem once. I found out the problem was in using two seperate connections (one for adodc and one for execute statement). Each connection doesn't see the changes other connections make immediatelly. I found a solution that works although it may seem a bit weird:

1. Set up the connection object:
dim cnn as new adodb.connection
cnn.connectionstring=...
cnn.open

2. run your insert statement(it may help using transaction):
cnn.begintrans
cnn.execute "INSERT..."
cnn.committrans

3. with adodc
.connectionstring=cnn.connectionstring
.recordsource = "your sql statement"
.refresh
set .recordset.activeconnection=cnn
.refresh
end with


I hope this will be of some help. Although this is working for me, I guess there should be a better way. I'm going to check this JRO thing CCLINT suggested (maybe an example, CCLINT?)

Mangro

 

[jgoodman00]

This looks like exactly what I need.

I have a global cnn object which is used for almost all of my connections to the DB. The only time this is not used, is for the ADODC controls. Is there a way to use this global connection object on them?

I am guessing my problem is that the inserted rows are contained in the cnn cache, & subsequently not showing in the ADODC.

I then got the following code from microsoft:
Public Sub RefreshCache()
Dim pJe As New JRO.JetEngine
pJe.RefreshCache cnn
Set pJe = Nothing
End Sub[/color blue]

When this executes, it fails on the refreshcache method, stating 'The connection cannot be used to perform this operation. It is either closed or invalid in this context...'

As far as I can see, everything is ok, so I am at a bit of a loss.

1. Is there a way of binding my adodc controls to my global connection object? If so, how?
2. Any ideas why the RefreshCache method fails? To the best of my knowledge, the global cnn object is a standard connection object.



Cheers,


James Goodman MCP

http://www.james-goodman.co.uk

 

[MangroBongacello]

James, the text in bold in my previous post does that (connects adodc to global connection object). The adodc commands before bold are to set up the adodc recordset object, to which you can then set the activeconnection property (and thus to adodc) the global connection.

Mangro

 

[CCLINT]

MangroBongacello, using one connection: that is what I usually recommend as well - and it IS the recommended method, (Had some discussions here and good examples...but it seems the exact thread with the real discussions and examples is gone and the search cannot find other references - just some small mentions like thread709-531361, thread222-539134), but sometimes this is not possible especially if the data comes from two different databases (importing data from one db to another), but yes, it is the first step to take.

When working with an Adodc, it may be easier, in the long run at least, to use a recordset object and set the Adodc's recordset to this:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM SomeTable", conn,adOpenStatic, adLockOptimistic, adCmdText

Set Adodc.Recordset = rs

But using your example works just fine as well.

jgoodman00: The syntax you are using is correct, but I think either the connection is not open, or not a JET MDB, or, more likely, your are using the MS ACCESS driver and not the JET provider.

Post your connection string...

 

[jgoodman00]

I reckon I am using the Access driver rather than the Jet one. I am using a DSN.


When I setup the DSN, I do not get an option to use the Jet provider, only the MSAccess driver.



James Goodman MCP

http://www.james-goodman.co.uk

 

[MangroBongacello]

Yeah... CCLINT, sometimes it's really hard to think simple... your method is much finer, adodc doesn't have to be set up and refreshed twice like in my example. Have a star from me.

James, I think this is what you need.

Regards
Mangro

 

[jgoodman00]

So is it possible to use a DSN through Jet, rather than MSAccess?

James Goodman MCP

http://www.james-goodman.co.uk

 

[CCLINT]

Thank you MangroBongacello, I know exactly what you mean ("think simple": Mostly my problem and not someone elses )

The next question would be: Then why use the ADODC at all (except maybe to just have the navigating abilities for the user to play with - which you can create your self.

jgoodman00: This is correct what you say. You will need to use the JET Provider instead.
There are some properties you can set to reduce flush time, etc, but try using the OLEDB JET Provider instead....there are other limitations with the ODBC driver, which you will find out later.
This probably means that you will have to set up a file search dialog so the user can search for and point to the database, and save the results in the registry to be used in the next session, if you do not keep the db in a fixed foilder, such as the application folder or a sub folder thereof.

Still, short term solution not having to change drivers etc. is to go with one connection, as, IMHO, this is what you should be doing anyways.

 

[jgoodman00]

Ok, I have tried modifying my global connection object so that it is DSN less (as the source db will always be in the same folder as the application).

My connection string is:
Provider=Microsoft.Jet.OLEDB.4.0;File Name=\\Folder Name\Astra\Astra.mdb;

However, it does not make a connection. It always fails with 'Connection denied. New connection you requested has different characteristics than the one already in use.'

My global connection object is the first thing opened by the splash screen for the app, so there are no other connections.

???

James Goodman MCP

http://www.james-goodman.co.uk

 

[CCLINT]

Not
File Name =

, but

Data Source =

Also, if you have any further problems the post the complete connection string. You also need to close the connection before re-opening it with a different connection, so check if the conn.State = 0 before connecting.