Problem adding records to SQL tables from Access

Question

Problem adding records to SQL tables from Access

Daniel Joosten

SSC Enthusiast

Points: 149
More actions
March 15, 2005 at 9:49 am

#163826

I have an Access database with a SQL Server back-end. I am new to SQL, so the problem I have been having is a real puzzle to me.

I have created an Access query called ‘qryIRCasesUnrouted’, which finds records in a table called ‘IR’ that do not have a matching key in another table called ‘tblRoutingLog’. The query is used to find new Incident Reports (Cases) that haven’t been routed so that I can add them to the Routing List for processing and disposition.

I then run the following subroutine in Access to add those records to the ‘tblRoutingLog’ table:

Public Sub ImportIR()
On Error GoTo ErrHandler

Dim DB As DATABASE, rstSrc As Recordset, rstTgt As Recordset
Dim i As Integer, strSrcSQL As String

Set DB = CurrentDb
strSrcSQL = "SELECT * FROM qryIRCasesUnrouted WHERE ORI = '" & Forms!Login!ORI & "'"
Set rstSrc = DB.OpenRecordset(strSrcSQL, dbOpenDynaset, dbSeeChanges)
Set rstTgt = DB.OpenRecordset("tblRoutingLog", dbOpenDynaset, dbSeeChanges)

With rstSrc
    Do Until .EOF
        rstTgt.AddNew
        rstTgt!RouteUID = NewPK
        rstTgt!KeyID = !Case_ID
        rstTgt!RouteRecipient = !LoginName
        rstTgt!RecvdDate = Now
        rstTgt!Pending = True
        rstTgt!ReportType = "Incident Report"
        rstTgt!Descrip = !Case_Number
        rstTgt!Status = "A"
        rstTgt.Update
        i = i + 1
        .MoveNext
    Loop
    .Close
    rstTgt.Close
End With

ImportExit:
Set rstSrc = Nothing
Set rstTgt = Nothing
Exit Sub

ErrHandler:

MsgBox "Import failed due to Error # " & err.Number & " - " & err.Description, vbCritical + vbOKOnly, "Import failed"
Resume ImportExit
End Sub

The subroutine simply moves through the ‘qryIRCasesUnrouted’ result set and adds the appropriate data to the ‘tblRoutingLog’ SQL table.

In most cases, everything works fine. However, I recently had a customer who had over 5000 records to add and I received the following error:

Error # 3155 – ODBC –insert on a linked table ‘tblRoutingLog’ failed

This doesn’t occur until it has already added 3601 records to the ‘tblRoutingLog’ table. Trying to re-run it the second time to add the rest of the records doesn’t work either.

I noticed that if I checked the “Create as Clustered” option for the Primary Key of the tblRoutingLog table in Enterprise Manager, I do not get this error and all the records are added.

Can anyone explain to me what the problem is? I’m not confident that I won’t have problems with other clients in the future? What does changing the ‘Primary Key to clustered do?

PS
This error doesn’t occur if I import to an Access table either

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 1

The clustering option ensures that the data in the table is part of the index, which enables quick search and retrieval.

Also a clustered key is best used to support queries that return more than one row.

More info can be found at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_5b1v.asp