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

 

[ClimoC]

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!

I think this is why I had it Opening with the parameters I did - by editing the value in the userform controls, you were updating both your recordset, and sending the value to the appropriate field in Access. Access' locking would keep a handle on the multiple users and the hierarchy of changes - and whenever the Frontend prompted a requery, you'd get the latest info.

What's annoyed me the most through all of this, is that through all testing and playing around, everything has worked as was originally posted, except the one table that wasn't picking up the Added record

However, I say that prematurely, because when we start getting a few hundred/one or two thousand records in this, it might all fall apart due to one of the pitfalls you're both kindly illuminating for me

This is my problem, I know a lot/enough, just not enough

 

[xenou]

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

What's the story on that field. Is it an autonumber field?


BTW, why not build this in Access instead of using Excel Userforms? I would also as a general rule prefer DAO when working with Access but it may be too late for that.

 

[ClimoC]

What's the story on that field. Is it an autonumber field?


BTW, why not build this in Access instead of using Excel Userforms? I would also as a general rule prefer DAO when working with Access but it may be too late for that.

hehe, yeah waaaayyy too late! Meh. I'm more comfortable with ADO now. I know they're not hugely different but remembering all the little behaviours and intricacies of an overall style/method takes enough time as it is.

As it would happen, removing the requeries seems to have done the trick (leaving code as originally posted) of getting the record into the FinRS r-set.

 

[ClimoC]

What's the story on that field. Is it an autonumber field?


BTW, why not build this in Access instead of using Excel Userforms? I would also as a general rule prefer DAO when working with Access but it may be too late for that.

And no sorry it's not an autonumber field. Nor is it required.

Again though, working now, so all happy

 

[xenou]

As it would happen, removing the requeries seems to have done the trick (leaving code as originally posted) of getting the record into the FinRS r-set.

Good catch.