Odd Requery/Updatebatch behaviour

[ClimoC]

Howdy

I have an Excel Workbook with userforms, pivottables and the like. The backend is an Access db (accdb), with connection through ADODB

Everything seems to work beautifully, except when I'm adding a new record.

Here's the startup connection:

Public Function TestBatchUpdate()


Dim strConn As String, strFileSpec As String


Set ConnLink = New ADODB.Connection


strFileSpec = ThisWorkbook.Path & "\Io.accdb"
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileSpec & ";Persist Security Info=False;"
With ConnLink
            .CursorLocation = adUseClient
            .CommandTimeout = 0
            .Open strConn
End With


Set FinRS = New ADODB.Recordset
Set SchRS = New ADODB.Recordset
Set ProRS = New ADODB.Recordset
Set FisQuLU = New ADODB.Recordset
Set FisYeLU = New ADODB.Recordset
Set FisPeLU = New ADODB.Recordset
Set Users = New ADODB.Recordset




ProRS.Open "Programmes", ConnLink, adOpenStatic, adLockBatchOptimistic
SchRS.Open "Scheduling", ConnLink, adOpenStatic, adLockBatchOptimistic
FinRS.Open "Finance", ConnLink, adOpenStatic, adLockBatchOptimistic
FisQuLU.Open "Fiscal_Quarter_Lookup", ConnLink, adOpenStatic, adLockBatchOptimistic
FisYeLU.Open "Fiscal_Year_Lookup", ConnLink, adOpenStatic, adLockBatchOptimistic
FisPeLU.Open "Fiscal_Period_Lookup", ConnLink, adOpenStatic, adLockBatchOptimistic
Users.Open "Users", ConnLink, adOpenStatic, adLockBatchOptimistic






End Function

This is a relatively new approach for me (I didn't realise I could use connected recordsets, previously in other programmes I kept everything separate as disconnected recordsets, then relied on worksheet_change events to fire SQL queries to update the database, and refresh the tableview every x seconds.)

When I try and add a new record to multiple tables, the first updatebatch method works fine as it is. The others don't work about 60-70% of the time (record is not inserted to the other tables)

With ProRS
        .Filter = adFilterNone
        .MoveFirst
        .AddNew Array("Series_Name", "Series_Number", "Run_Number"), Array(Trim(ADDSeriesName.Value), CLng(ADDSeriesNo.Value), CLng(ADDRunNo.Value))
        .Fields("Series_ID").Value = 0
        .UpdateBatch
        S_ID = .Fields("PK").Value
        .Fields("Series_ID").Value = S_ID
        .UpdateBatch
End With
With FinRS
        .AddNew Array("Series_ID", "Schedule_ID", "Amort_Policy"), Array(S_ID, 0, 0)
        .UpdateBatch
        .Requery
End With




ProRS.Requery
FinRS.Requery

There are a couple of requeries in there for the FinRS connection, because it seems to bring the success rate up from 0% to about 30%.

I've noticed it's also more likely to work if the database is open in Access whilst interaction occurs with the Excel-housed userforms.

Are there settings in Access/The connection parameters that can make Access execute all changes in the cache instantly? Nowhere will people be updating multiple records at any one time - they select the record they want to edit, type in the fields, and move on.

Are my problems even cache based? Is it the order I do things in? Settings in the Finance table (FinRS)?

Could really do with some help, because it hinders the project if adding a new record doesn't always work

Thanks
C

 

[ChrisM]

I've never had luck with UpdateBatch, always just used Update by itself. Is there a reason you need the batch part of it? It looks like you are updating one record at a time anyways, no need for Batch.

 

[ClimoC]

I've never had luck with UpdateBatch, always just used Update by itself. Is there a reason you need the batch part of it? It looks like you are updating one record at a time anyways, no need for Batch.

hey Chris

Don't you need to use UpdateBatch to get it to correctly synchronise what's in Access vs what's in the Recordset? I thought Update only applied the change to the Recordset

 

[xenou]

BatchUpdate might be useful for disconnected recordsets (I have never wanted to really go there though). An update command on the recordset will update the database.

 

[ChrisM]

No, the recordset is updated the moment you do the .AddNew and put some data in it. The recordset is always "real time", but it does not maintain a real time connection back to the source database. If you were to delete a record in Access while your recordset was open, you would continue to see it until you ran .Update, then it would disappear.

So .Update sends data back to the database. Or you could do multiple .AddNews, then do a single UpdateBatch to send them all at once back to the database. That's the whole point of the batch part, store up all the changes and make them in one nice efficient transmission to the database.

But again, if you do some reading on ADO and UpdateBatch you will find there are some technical issues going on in the background that you really need to understand.

 

[ClimoC]

BatchUpdate might be useful for disconnected recordsets (I have never wanted to really go there though). An update command on the recordset will update the database.

Thanks xenou - but I just did a find/replace and swapped all UpdateBatch's for Update's - the result being that Access hasn't yet included the record (though it may be in the recordset). This will be a multi-user system, so changes need to be as up-to-date as possible.

It breaks at this part, bugging 'Invalid use of Null' (Record doesn't exist yet, so the PrimaryKey hasn't been generated)

With ProRS
        .Filter = adFilterNone
        .MoveFirst
        .AddNew Array("Series_Name", "Series_Number", "Run_Number"), Array(Trim(ADDSeriesName.Value), CLng(ADDSeriesNo.Value), CLng(ADDRunNo.Value))
        .Fields("Series_ID").Value = 0
        .Update
        S_ID = .Fields("PK").Value    'BUGS HERE
        .Fields("Series_ID").Value = S_ID
        .Update
End With

Now, assuming some other solution was found to get around the use of the Primary Key as a Foreign Key in the other tables (done manually by the code as above, as I took off restrictions and links because they caused too strong a set of conditions that affected other methods/reports) - the problem still exists that the record I just added to the recordset hasn't filtered through (and no amount of 'Refresh All' in Access has changed that) to Access - so any new/requery by other users won't show new records/changes

Is there a parameter or something I'm missing in my connection (see 1st post) that would make Update actually update in the way Update Batch seems to be doing? Or a setting in Access that is stopping something? If Access isn't showing the new record after pressing 'Refresh All', it would seem to me that the changes aren't even in the cache waiting to be written?

 

[ClimoC]

No, the recordset is updated the moment you do the .AddNew and put some data in it. The recordset is always "real time", but it does not maintain a real time connection back to the source database. If you were to delete a record in Access while your recordset was open, you would continue to see it until you ran .Update, then it would disappear.

So .Update sends data back to the database. Or you could do multiple .AddNews, then do a single UpdateBatch to send them all at once back to the database. That's the whole point of the batch part, store up all the changes and make them in one nice efficient transmission to the database.

But again, if you do some reading on ADO and UpdateBatch you will find there are some technical issues going on in the background that you really need to understand.

Thanks Chris - So it seemed to be working quite nicely despite my below post whinging about Access not seeing it. But I still need multiple users to see the change every time they make one - are you saying that with just .Update, any subsequent connections made at other terminals would see the new record in their recordsets too? Do I need to stick to .Update but include a bunch of .Requery's at the start of Functions/Form module codes?

Also - the unique ID was being borrowed from the PK in Access. Using Updatebatch, I can see the PK of the added record. With Update, it returns Null.

 

[xenou]

You have to be careful about record locking. Not all changes are visible to all users (even if they are real changes).

I would change:
adLockBatchOptimistic to adLockOptimistic

Also use a dynamic cursor rather than a static cursor.

 

[ChrisM]

You're getting in to the tricky areas of ADO that I've always been able to conveniently ignore because I don't have to deal with multiple users, so I can't help you any further, other than to answer one of your questions:

Recordsets are not live links, so if one user makes a change to his recordset, then runs Update, Access will be updated then, but the other users' recordsets will NOT be updated automatically.

And now you get in to the row/table locking issues xenou is bringing up, so you probably don't want two users with the same recordset open at the same time anyways (unless one is in read-only mode). And now you have to start writing code to handle whether portions of the database are locked, and pop a message to the user that they are in read only mode, etc. Again, I avoid this complexity like the plague, I'm not that good to manage all that!

 

[ClimoC]

You have to be careful about record locking. Not all changes are visible to all users (even if they are real changes).

I would change:
adLockBatchOptimistic to adLockOptimistic

Also use a dynamic cursor rather than a static cursor.

Tried that, AddNew now fails. "You must enter a value in the 'Programmes.Series_ID' field"

Well I did that...

.AddNew Array("Series_ID", "Series_Name", "Series_Number", "Run_Number"), Array(0, Trim(ADDSeriesName.Value), CLng(ADDSeriesNo.Value), CLng(ADDRunNo.Value))

and it says the same 'You must enter a value...' error - doesn't matter if I put it in single quotes, double quotes, as a number, as a CLng integer (nor if it's 0, 1, or 145675 - it says the same error...)