rs.AddNew ODBC Fail Query timeout expired (1 Viewer)

[jsdba]

-Frontend Access 2010
-Backend tables are in SQL Server 2017

Set rs = db.OpenRecordset("SELECT * FROM tbltable1", dbOpenSnapshot, dbSeeChanges)
Set rs1 = db.OpenRecordset("SELECT * FROM tbltable2 ", dbOpenDynaset, dbSeeChanges)

BeginTrans
rs.MoveFirst
Do Until rs.EOF
    rs1.AddNew
        rs1.Fields("LUID") = rs.Fields("LUID")
    rs1.Update
    varBook = rs1.LastModified
    rs1.Bookmark = varBook   
   
    '\\set variables IDs
    FeeID = (rs1.Fields("FeeID").Value)
    FeeLUID = rs1.Fields("FeeLUID").Value
    PopulateDataStage FeeLUID, FeeID 'go to sub

Sub PopulateDataStage(i, x)
Dim db As Database
Dim rs3 As Recordset
Dim rs4 As Recordset

Set db = CurrentDb
Set rs3 = db.OpenRecordset("SELECT * FROM tbltable WHERE ID=" & i, dbOpenSnapshot, dbSeeChanges)
Set rs4 = db.OpenRecordset("SELECT * FROM tbltable2", dbOpenDynaset, dbSeeChanges)
BeginTrans
Do Until rs3.EOF
    [B][COLOR="Red"]rs4.AddNew[/COLOR][/B]
        
        rs4.Fields("") = rs3.Fields("")
        rs4.Fields("") = rs3.Fields("")

    rs4.Update
rs3.MoveNext
Loop
CommitTrans

rs3.Close
rs4.Close
Set rs3 = Nothing
Set rs4 = Nothing

I get the error at rs4.AddNew. The first part of the code loops through the parent records then jumps to another sub to add child records. the first parent record is added with child record with no problem but at the second record of the parent table the error occurs. theres nothing particularly special about the second record. This use to work flawlessly when both backend and frontend was Access

Any ideas?

 

[Pat Hartman]

Does the table have a primary key?
Do you have permission to add rows?
Can you open the table in DS view from the Access GUI and just type in data to add a row?
Can you open a recordset in SSMS and Add a row by typing?

 

[Mark_]

Additionally, are there more than one unique indexes for this table?

Also, are you repeating the line

        rs4.Fields("") = rs3.Fields("")

twice as shown in your code?

 

[jsdba]

Does the table have a primary key?
Do you have permission to add rows?
Can you open the table in DS view from the Access GUI and just type in data to add a row?
Can you open a recordset in SSMS and Add a row by typing?

Yes to all of the above

 

[jsdba]

Additionally, are there more than one unique indexes for this table?

Also, are you repeating the line

        rs4.Fields("") = rs3.Fields("")

twice as shown in your code?

No just 1. Also, i repeated that line just to indicate that i'm adding multiple fields. I should indicate that table has a FK (from parent table) which is also inserted

rs4.Fields("FKID") = x

I've done this a gazillion times and it has been working until i upsized backend to sql server.

 

[PeterF]

You Dim RS3 and RS4 as recordset, you should explicitly Dim the type of recordset.

If it is a linkend table you can use:
Dim rs3 As DAO.Recordset
Dim rs4 As DAO.Recordset

If its a connection in vba to the SQL server it should be:
Dim rs3 As ADODB.Recordset
Dim rs4 As ADODB.Recordset

 

[Mark_]

Since it is the second parent that is causing problems, have you checked the fields that are required to be unique to make sure you are actually entering unique values?

As an example, if you have "AddDateTime" as a unique Date/Time field, are you making sure your adds are not close enough together to have the value duplicated? This may be something happening on the back end at the time you add the records PRIOR to filling and updating the record, so it could be a default value.

 

[sonic8]

I guess this is a locking issue.

You add one record in a transaction and before that transactions is committed you try to add another one that will most likely end up in the same data page of the table. If they run in different ODBC connections to SQL-Server the first will block the second.


Using Profiler, sp_who and sp_lock to analyse the situation on SQL-Server while waiting for the timeout should reveal the cause.



BTW: Use the dbAppendOnly-Option for your recordsets if you just want to add new records.