Dao Adding New Records - Timeout Issue Or Hang

[accessNator]

I am looping through a TABLE1 and in that recordset, I am inserting those values in another table called TABLE2. But it seems when I attempt to add the new record in TABLE2, it seems to hang and I cant figure it out. I have narrowed it down that if I COMMENT out the .update, my code will loop through the rst1 for TABLE1.

For some reason on the .UPDATE it just hangs. I eventually get an ODBC--Call Failed. [Microsoft][ODBC SQL Server Driver]Query timeout expirted(#0) Please contact your administrator.

Both tables are link tables to a SQL Server tables.

Here is my code:

Private Sub InsertRecord()

Dim db As DAO.Database
Set db = CurrentDb

Dim rst1 As DAO.Recordset
Dim Table1 As String
Table1 = "dbo_tmpLifeLine"

Dim rst2 As DAO.Recordset
Dim Table2 As String
Table2 = "dbo_WorksheetHeader"


Set rst1 = db.OpenRecordset(Table1, dbOpenDynaset)

    If Not (rst1.EOF And rst1.BOF) Then
        rst1.MoveFirst
    End If
    
    Do Until rst1.EOF = True


            Set rst2 = db.OpenRecordset(Table2, dbOpenDynaset, dbSeeChanges)
            With rst2
                .AddNew
                ![FiscalYearID] = rst1![FiscalYearID]
                ![companyid] = Me.txtCompanyID
                ![RevenueDataMonth] = rst1![DataMonth]
                ![RevenueDataYear] = rst1![DataYear]
                ![NumberOfMonths] = 1
                ![WorksheetTypeID] = 2
                .Update
            End With
            rst2.Close
            
    rst1.MoveNext
    Loop
    
    rst1.Close
    Set rst1 = Nothing
    Set rst2 = Nothing
    
    Set db = Nothing

End Sub

 

[CJ_London]

you are mixing DAO and ADODB methods - openrecordset is a dao method whilst your .append is ADODB.

Change your looping to

Do Until rst1.EOF = True
    currentdb.execute("INSERT INTO Table2 Set (FiscalYearID, companyid, RevenueDataMonth, RevenueDataYear, NumberOfMonths, WorksheetTypeID) Values(" & rst1![FiscalYearID] & ", " & Me.txtCompanyID & ", " & rst1![DataMonth] & ", " & rst1![DataYear] & ", 1, 2)") 
    rst1.MoveNext
Loop

 

[accessNator]

you are mixing DAO and ADODB methods - openrecordset is a dao method whilst your .append is ADODB.

Change your looping to

Do Until rst1.EOF = True
    currentdb.execute("INSERT INTO Table2 Set (FiscalYearID, companyid, RevenueDataMonth, RevenueDataYear, NumberOfMonths, WorksheetTypeID) Values(" & rst1![FiscalYearID] & ", " & Me.txtCompanyID & ", " & rst1![DataMonth] & ", " & rst1![DataYear] & ", 1, 2)") 
    rst1.MoveNext
Loop

Thank you for the reply. For the sakes of it, I modified the code per your example to see if it made a difference. Sigh, no differences as it gives me the same error message. I only had 2 records reading, to be inserted into the 2nd table.

I did a manual insert into the SQL Server using SSMS interface and it took the records. I cant seem to figure out why from the Access side, I am getting this issue.

Thoughts on tracing my problem?

 

[CJ_London]

I would investigate the error message to see what is causing it.

One thing to check, when you made your odbc link, did you provide an index for table2?

Perhaps take out the dbseechanges for rst2

and if you are only copying data from table1 to table2 you can do that in one query rather than individual inserts

 

[JHB]

I did a manual insert into the SQL Server using SSMS interface and it took the records. I cant seem to figure out why from the Access side, I am getting this issue.

Thoughts on tracing my problem?

Are you able to manual add a record in the table from MS-Access?
If not, then you have to add a autonumber field or index to the table on the SQL Server side, so that a record can be uniquely identified.

 

[accessNator]

I would investigate the error message to see what is causing it.

It looks like by putting a STOP Break in my code and walking through my steps, I was able to isolate where my problem was occurring. In my original post, I didnt think to show the process where when I was calling for the INSERTRECORD procedure using a Transaction process and I didn't add a piece of code within the RST1 where I was calling a function to retrieve a record count. For some reason that was where my problem was. I know it might not make much sense in my explanation to you, but I wanted to give you an update on my situation. I will post a new question on my dilemma on how should I handle this as it may be a totally different post now that I know where my problem arose.

Thanks again.

 

[accessNator]

Are you able to manual add a record in the table from MS-Access?
If not, then you have to add a autonumber field or index to the table on the SQL Server side, so that a record can be uniquely identified.

Thanks for your reply. That wasn't the case.