ADODB Recordset Update with SQL Update on the same table causes error??

Thanks for the reply Daniel .... but I'm not trying to update the same record at the same time from two different directions ....

The SQL queries run first in another funtion.  Then the recordset is created later on in the process.  The more I test it I'm starting to believe that's not what's generating that error.

Even if I open a recordset in a function with no SQL query in it I still get that error when I try to update using the rst.Update command as shown below.

Dim rstaddit As ADODB.Recordset

Set rstaddit = New ADODB.Recordset
rstaddit.Open "SELECT * FROM CUSTSUMM", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rstaddit.RecordCount <> 0 Then
    rstaddit.MoveFirst
    Do Until rstaddit.EOF
        If rstaddit!PD91_OVER < 10 Then
            rstaddit!PD61_90 = rstaddit!PD61_90 + rstaddit!PD91_OVER
            rstaddit!PD91_OVER = 0
            rstaddit.Update  <----------This generates the error
        End If
etc.
etc.



ET

Thanks for the reply LPurvis ...  It's been a while!!!

I think you are on to something with the comments in your last paragraph.... it jogged my memory.  I was just about to go back through my notes but I think you may have hit it.  About 2 years ago I converted a large Access db to MySQL ... very good learning experience.  I kind of remember now the issues with bit fields and the TimeStamp field needing to be added to the tables in order to make updates.

I will give it a try in the morning and let you know.

Thanks a million!!!

ET

   

Ok, I adjusted the MySQL tables to include the CREATED_DATE Timestamp field with a default value of Current_Timestamp and also set a Default value to the TinyBit fields plus removed the Not Null attribute.  This is what I had to do in the other application that was converted from Access to MySQL but the problem still exist.

I think I've isolated the problem but I'm not sure how to adjust for it since I thought I had made adjustments for it.  I will explain below.

The previous application that was converted to MySQL as well as this one with the recordset update problem both used a DSN to connect to the MySQL tables.  Therefore, in the linking function when the application starts up, the code simply used the DSN to connect as shown below.

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=" & strMySQLDSN, acTable, "COMPANY", "COMPANY"

Now, this application is also being modified to NOT use a DSN, therefore the DSN parameters are being included in the new linking code as shown below.

'Build the MySQL Server Connection String
'add the values of the "option =  property" together to include multiple options
    'Access/VB = 3
    'Return Matching Rows = 2
    'Do Not Prompt Upon Connect = 16

strMySQLConn = "ODBC;Driver={MySQL ODBC 3.51 Driver};Server=" & strMySQLServerHost & ";Database=" & strMySQLDatabase & ";User=" & strMySQLLogin & ";Password=" & strMySQLPswd & ";Option=21;"

I think the problem is the Option=21 parameter in the connection string because if I manually connect the table to the front end using the DSN which has the Return Matching Rows and Do Not Prompt Upon Connect checked then the code works like it should.  When connected using the connection string then it throws the error as described above.... "You and another user are trying to update the same data, etc."

Somewhere I found on the net where you have to add the values of the Option= paramater as shown above but apparently that's not working.

Any ideas will be appreciated.

ET

Yes, that's the link I am working from.  

I'm a bit confused by your options example above as the comments said to include multiple options you must add them together.  How do you get to 35???

ET

Ignore the previous question ....

You were referring to the recommended option settings table on that page.

I will try 35 and see what happens.

Et

Thanks LPurvis and sorry for not getting back to this question earlier.

You actually answered the question in both of your replies.

1.) Once the Access server tables were migrated to MySQL, I needed to have the Timestamp field in each of the tables plus the TinyBit fields needed to be set to Not Null.

2.) On the Connection String Parameters for MySQL and Access  Option = 35 works.

Again,

Thanks for all your help.

ET